OraFAQ Forum: SQL & PL/SQL » A trigger to give a 10 per cent discount to a regular customer

Home » SQL & PL/SQL » SQL & PL/SQL » A trigger to give a 10 per cent discount to a regular customer

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

A trigger to give a 10 per cent discount to a regular customer [message #645974]

Thu, 17 December 2015 04:18

ora1992
Messages: 1
Registered: December 2015

Junior Member

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

Report message to a moderator

Re: A trigger to give a 10 per cent discount to a regular customer [message #645979 is a reply to message #645974]

Thu, 17 December 2015 05:16

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Please read and follow How to use [code] tags and make your code easier to read?

If you want to update a value in the current row from a before update trigger then you don't use an update statment. You assign the value directly:

:new.totalcost := 0.10 * :new.TotalCost;

That's a 90% discount by the way.

To check the statusname you'd need a select against the passengers table, what that select should look like we have no idea because we know nothing about your tables.

Report message to a moderator

Re: A trigger to give a 10 per cent discount to a regular customer [message #645989 is a reply to message #645979]

Thu, 17 December 2015 05:54

JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar

Member

And put your business rules inside packages and call them from the triggers.
So you can also use and test them without the need to update data.

Report message to a moderator

Re: A trigger to give a 10 per cent discount to a regular customer [message #645991 is a reply to message #645989]

Thu, 17 December 2015 06:03

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

And put your business rules inside packages and call them from the triggers.

I have mixed feelings about this "rule" and the given reason "So you can also use and test them without the need to update data" is not a good one, you can always rollback an update so you do not really update the data but, more important, if you cannot modify the data when testing your code I am some doubts on your tests and code.

Report message to a moderator

Re: A trigger to give a 10 per cent discount to a regular customer [message #645995 is a reply to message #645991]

Thu, 17 December 2015 06:37

JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar

Member

What I'm trying to address here, is that you shouldn't put this kind of "hard coding" inside triggers.
In the business rule "regular passengers get a discount" there is some obvious "hard coding", which I would like to encapsulate:
1) the amount it self
2) who is a regular passenger (which is a static rule, not dependent on a data change)

Report message to a moderator

Previous Topic:	Date Conersion From UTC to EST
Next Topic:	Staff Lft Company and display his pervisos recoreds

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 30 21:23:15 CDT 2026