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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use INSTEAD OF triggers to update the data directly....
ASKER
Thanks - it worked ok.
rgds
rgds
ASKER
rgds CC