A trigger to give a 10 per cent discount to a regular customer

3128080

Hi,

This is just for an assignment. I am really really new PL/SQL haha. I agree with you guys, but I had to write a trigger and the database itself is fairly simple.

Thank you so much for you help. Can you just tlel on when the tirgger will execute and explain what it is doing?

Thanks,

Fareedh

vijayrsehgal-Oracle

As Blu has already suggested you should not be using Trigger, but the line of code giving you error is due to "=" instead of ":="

the code should have line as below

:New.TotalCost := 0.90 * :New.TotalCost;

3128080

Hi

I am also getting this error

Error(7,26): PLS-00049: bad bind variable 'NEW.PassengerNumber'

Sorry to be so annoying

Thanks

PamNL

It will fire when you update anything in the bookings table.

I think your trigger should actually be on insert, because if you create a new booking it wll get the full cost and ONLY when you update the booking the passenger gets the 10% discount.

So you can test by entering a passenger in the table, then insert something in the booking table (nothing happens). Now update the booking (ANY field) and your total cost will be reduced.

Now for the fun part, update again ;-).

And again

And again ...

PamNL

And to be sure you get the point .. check TotalCost after each update ;-)

BluShadow

PamNL wrote:

It will fire when you update anything in the bookings table.
I think your trigger should actually be on insert, because if you create a new booking it wll get the full cost and ONLY when you update the booking the passenger gets the 10% discount.

Even better it should be on INSERT and UPDATE, so it can fire whether it's a new data or updated data, providing the UPDATE is coded to give a reduction on the original cost/price and not to keep applying discounts again and again on the current value.

3128080

Hi

I am also getting this error

Error(7,26): PLS-00049: bad bind variable 'NEW.PassengerNumber'

Sorry to be so annoying

Thanks

PamNL

That's fine .. what does your passenger and booking table look like? I had to guess on the foreign key in bookings to go the passenger table ..

3128080

SELECT PASSENGERNUMBER,
  TITLE,
  STATUSNAME,
  FIRSTNAME,
  SURNAME,
  FIRSTLINEADDRESS,
  SECONDLINEADDRESS,
  CITY,
  POSTCODE,
  CONTACTTELEPHONENUMBER,
  EMAILADDRESS,
  JOURNEY_JOURNEYNUMBER
FROM PASSENGERS ;

SELECT BOOKINGID,
  DATEOFBOOKING,
  TRAVELDATE,
  STOPS,
  TOTALADULTS,
  TOTALCHILDREN,
  CARDTYPE,
  CARDNUMBER,
  EXPIRYDATE,
  TOTALCOST,
  PASSENGERS_PASSENGERNUMBER,
  SERVICES_SERVICENUMBER
FROM BOOKINGS ;

ABove is booking and passengers

Thanks

Chris Hunt

The problem with this approach is that the trigger fires every time the booking record is updated for any reason, and each time it does the cost is reduced by 10%. Customer changes delivery date? knock off 10%. Customer changes order amount? knock off another 10% etc...

Possibly better to have a new column: discount_amount, that has a default value of 0, but you set it to 10% for regular customers.

How will you deal with cases where the passenger status is changed to regular for a passenger that has existing booking records?