Forum Stats

  • 3,839,073 Users
  • 2,262,444 Discussions
  • 7,900,845 Comments

Discussions

Categories

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

3128080
3128080 Member Posts: 37
edited Dec 17, 2015 6:13AM in SQL & PL/SQL

Hi,

I am trying to create a trigger to give a discount of 10% of the field 'totalCost' in the bookings table, if the 'statusname' is regular in the Passengers table. Below is what I have come up with so far. But I know its wrong. Can you please help? 

create or replace TRIGGER Ten_Percent_Discount 
BEFORE UPDATE ON Bookings
FOR EACH ROW
BEGIN
UPDATE Bookings b
SET b.TotalCost = 0.10 * TotalCost
WHERE Passengers.StatusName = 'Regular';
END;

Thanks,

Fareedh

Tagged:
Pleiadian
· Share on TwitterShare on Facebook

Best Answer

  • PamNL
    PamNL Member Posts: 637 Gold Badge
    edited Dec 17, 2015 5:27AM Answer ✓

    Assuming you have a passenger_id in your bookings table you probably can use something like this

    eate or replace TRIGGER Ten_Percent_Discount  
    BEFORE UPDATE ON Bookings 
    FOR EACH ROW 

    CURSOR C_Passengers
    IS
    SELECT Status_Name
    FROM   Passengers
    WHERE  Passenger_Id = :NEW.Passenger_Id
    ;

    l_status_name Passengers.Status_Name%TYPE;

    BEGIN 

      OPEN  C_Passengers;
      FETCH C_Passengers INTO l_status_name;
      CLOSE C_Passengers;
     
      IF l_status_name = 'Regular'
      THEN
        :New.total_cost = 0.90 * :New.Total_Cost;
      END IF;

    END; 

    · Share on TwitterShare on Facebook
«13

Answers

  • PamNL
    PamNL Member Posts: 637 Gold Badge
    edited Dec 17, 2015 5:24AM

    You are trying to update the table that is updating .. simple do this

    1. create or replace TRIGGER Ten_Percent_Discount  
    2. BEFORE UPDATE ON Bookings 
    3. FOR EACH ROW 
    4. BEGIN 
    6.   :New.total_cost = 0.90 * :New.Total_Cost;
    8. END; 

    Assuming you want to give 10% discount instead of 90% ;-).

    And where does your Passengers.Statusname come from :-)?

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

    Hi,

    Thanks for the response

    Its from the Passengers table.

    Thanks,

    Fareedh

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

    Assuming you have a passenger_id in your bookings table you probably can use something like this

    eate or replace TRIGGER Ten_Percent_Discount  
    BEFORE UPDATE ON Bookings 
    FOR EACH ROW 

    CURSOR C_Passengers
    IS
    SELECT Status_Name
    FROM   Passengers
    WHERE  Passenger_Id = :NEW.Passenger_Id
    ;

    l_status_name Passengers.Status_Name%TYPE;

    BEGIN 

      OPEN  C_Passengers;
      FETCH C_Passengers INTO l_status_name;
      CLOSE C_Passengers;
     
      IF l_status_name = 'Regular'
      THEN
        :New.total_cost = 0.90 * :New.Total_Cost;
      END IF;

    END; 

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

    Little type, use StatusName rather than Status_Name of course ..

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

    Hi,

    Thanks for your help. But can you please explain on what the trigger is doing as I am really new to triggers and does it execute the trigger when I update the statusname?

    Thanks,

    Fareedh

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

    And TotalCost rather than Total_Cost :-)

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

    I am getting a syntax error on this line called something expected

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

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,138 Red Diamond
    edited Dec 17, 2015 5:38AM 
    3128080 wrote:

Hi,

I am trying to create a trigger to give a discount of 10% of the field 'totalCost' in the bookings table, if the 'statusname' is regular in the Passengers table. Below is what I have come up with so far. But I know its wrong. Can you please help?
    1. create or replace TRIGGER Ten_Percent_Discount  
    2. BEFORE UPDATE ON Bookings 
    3. FOR EACH ROW 
    4. BEGIN 
    5. UPDATE Bookings b 
    6. SET b.TotalCost = 0.10 * TotalCost 
    7. WHERE Passengers.StatusName = 'Regular'; 
    8. END;  
    create or replace TRIGGER Ten_Percent_Discount 
BEFORE UPDATE ON Bookings
FOR EACH ROW
BEGIN
UPDATE Bookings b
SET b.TotalCost = 0.10 * TotalCost
WHERE Passengers.StatusName = 'Regular';
END;

    
Thanks,
Fareedh

    This is business logic and really shouldn't be something you put in a trigger.  It should be at the application/business layer of your coding, not the database layer.

    Triggers should be used to adjust or default values on the table to which the trigger is associated, and should ideally be the minimal code to allow for the trigger to execute quickly (consider what happens if you have to populate the table with a large quantity of data - the trigger has to fire for each row inserted).

    Move this logic to your business layer where you can query the other table to determine the status and adjust the cost accordingly as the booking is inserted/updated.

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

    Hi,

    Thanks for the reply. I totally understand that however this is for my assignment and it requires a business rule trigger and I could not think of anything else.

    Can you also please tell me on why I am getting a syntax error on this line  :New.TotalCost = 0.90 * :New.TotalCost;

    Thanks

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

    I agree that this logic should not be in a trigger (nothing should be in a trigger as far as I'm concerned ;-)).

    However, just to get this working ... what is your syntax error? Maybe submit your code as you have it now?

    I think you missed :New.TotalCost := 0.90 * :New.TotalCost;

    So the : should be before the =.

    You seem fairly new to PL/SQL, this is not a production system thing is it?? Looks more like an excercise to learn PL/SQL?

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