SQL Trigger

[rotten69]

Hey,

CREATE OR REPLACE TRIGGER "TRI_AMOUNT_REDUCTION"
	      AFTER INSERT ON "PURCHASE"
	      FOR EACH ROW
	    BEGIN
		IF (:NEW.CLIENTNO = 122336) THEN
			:NEW.AMOUNT := AMOUNT * 0.1;
		END IF;
	    END;
	    /

I have a query that selects my top client then want to pass the clientNo to the trigger. Is it a good idea to put my select statement in the trigger instead ?

[hfx642]

Yes, you can.
Is it a good idea? Yes.
BUT...
What your trigger is doing now, is not a good idea.
How do you know that your NEW client, is a TOP client? (They are NEW!)
At the beginning, ALL your NEW clients have a 50/50 chance of being a TOP client.
Technically... That's a lot of TOP clients.
What happens to your PAST TOP clients, when you get a NEW TOP client?
Does their "amount" still stay at a 10% premium, or do they get reduced by the previous 10%?

[rotten69]

True.. I may have new clients coming into the system in the future. As specified in my current trigger, the top client is a guy with ClientNo = 122336. However, he is NOT always a top client. I might have a new guy with ,let's say, ClientNo = 122338 and his purchases are higher than the previous guy's.

To get around this, I need to add my select statement to see who's my top client and if top client exists then give him/her a 10% reduction in price..

Does their "amount" still stay at a 10% premium, or do they get reduced by the previous 10%?

I ain't sure what happens if a new client replaces the old one.
Cheers,

cheers,