Link to home
Start Free TrialLog in
Avatar of cc258
cc258

asked on

creating trigger to change value of inserted

I am trying to create a trigger that fires when a record is inserted in table SUB. It should check if the value of SUB.itype is either 1, 2 or 3. If yes, the go to table MAIN and check value of iname. If MAIN.iname LIKE "Toyota%" then change value of Inserted SUB.itype to 0. (SUB.rec_id = MAIN.rec_id).

This is what I have:

CREATE TRIGGER TypeTrigger ON SUB FOR INSERT AS
DECLARE @my_itype VARCHAR(1)

SELECT @my_itype = (SELECT iname FROM MAIN WHERE(rec_id IN (SELECT rec_id FROM Inserted WHERE (itype > 0))) AND (MAIN.iname LIKE "Toyota%"))

IF @my_itype > 0

UPDATE Inserted
SET itype = 0

This is where Im stuck - what do I do?

Thks - CC

ASKER CERTIFIED SOLUTION
Avatar of Hilaire
Hilaire
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cc258
cc258

ASKER

Looks fine - I will give it a spin.

rgds CC
You can use INSTEAD OF triggers to update the data directly....
Avatar of cc258

ASKER

Thanks - it worked ok.

rgds