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

cc258Asked:
Who is Participating?
 
HilaireCommented:
You can't update the "inserted" virtual table. It's read-only.
Instead, you can update the SUB table directly. But you need to have a primary key/unique identifier in this table to update only the relevant records

CREATE TRIGGER TypeTrigger ON SUB FOR INSERT AS
UPDATE S
Set itype = 0
FROM SUB S
INNER JOIN INSERTED I ON S.primarykey = I.primarykey  -- is rec_id the primary key ??
INNER JOIN MAIN M on M.rec_id = I.rec_id
where S.itype > 0
and M.iname like 'Toyota%'

HTH
Hilaire
0
 
cc258Author Commented:
Looks fine - I will give it a spin.

rgds CC
0
 
arbertCommented:
You can use INSTEAD OF triggers to update the data directly....
0
 
cc258Author Commented:
Thanks - it worked ok.

rgds
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.