Forum Stats

  • 3,852,376 Users
  • 2,264,100 Discussions
  • 7,905,055 Comments

Discussions

Categories

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

2

Answers

  • 3128080
    3128080 Member Posts: 37
    edited Dec 17, 2015 5:46AM

    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

    · Share on TwitterShare on Facebook
  • vijayrsehgal-Oracle
    vijayrsehgal-Oracle Member Posts: 767 Red Ribbon
    edited Dec 17, 2015 5:49AM

    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;

    · Share on TwitterShare on Facebook
  • 3128080
    3128080 Member Posts: 37
    edited Dec 17, 2015 5:50AM

    Hi

    I am also getting this error

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

    Sorry to be so annoying

    Thanks

    · Share on TwitterShare on Facebook
  • PamNL
    PamNL Member Posts: 637 Gold Badge
    edited Dec 17, 2015 5:50AM

    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 ...

    · Share on TwitterShare on Facebook
  • PamNL
    PamNL Member Posts: 637 Gold Badge
    edited Dec 17, 2015 5:51AM

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

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,316 Red Diamond
    edited Dec 17, 2015 5:57AM 
    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.

    · Share on TwitterShare on Facebook
  • 3128080
    3128080 Member Posts: 37
    edited Dec 17, 2015 6:01AM

    Hi

    I am also getting this error

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

    Sorry to be so annoying

    Thanks

    · Share on TwitterShare on Facebook
  • PamNL
    PamNL Member Posts: 637 Gold Badge
    edited Dec 17, 2015 6:01AM

    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 ..

    · Share on TwitterShare on Facebook
  • 3128080
    3128080 Member Posts: 37
    edited Dec 17, 2015 6:06AM 
    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

    · Share on TwitterShare on Facebook
  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited Dec 17, 2015 6:08AM

    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?

    · Share on TwitterShare on Facebook
This discussion has been closed.