Update Trigger query...

[j252ewv]

Trigger query...
i want to be able to insert a new record into another table when a particulare field is updated on the source table, my trigger code is below but will not create in execution, can some spot the school boy error?

CREATE TRIGGER Test ON tbl_ManualItem
FOR UPDATE
AS
IF UPDATE(fk_ItemID)
BEGIN
INSERT INTO tbl_JobJoinItem
(fk_jobID,fk_Itemid,fk_Floorid,fk_Roomid)
VALUES
SELECT fk_jobID,fk_Itemid,fk_Floorid,fk_Roomid FROM Updated
END

 

[jrbarnett]

Remove the word "Values" - you don't need it if you are selecting data from tables for insert or update.

John

 

[j252ewv]

That works simple when you know how!

One last thing can you tell me how to reference the original value to compare against the new value?

 

[jrbarnett]

You can read the value from the deleted table and compare them to those in the inserted table to work out what has changed.

eg

[/code]
CREATE TRIGGER Test ON tbl_ManualItem
FOR UPDATE
AS
IF UPDATE(fk_ItemID)
BEGIN
INSERT INTO tbl_JobJoinItem
(fk_jobID,fk_Itemid,fk_Floorid,fk_Roomid)
SELECT fk_jobID,fk_Itemid,fk_Floorid,fk_Roomid
FROM Updated u JOIN inserted i ON u.fk_jobid = i.job_id and u.fk_itemid <> i.fk_itemid
END
[/code]

John

 

[jbenson001]

Use the Inserted and Deleted "conceptual" tables that are accessable inside of a trigger. If you have an Insert trigger, the Inserted table will hold the values of the inserted rows. If you have a Delete trigger, then the Deleted table holds the values of the deleted rows. If you have an Update trigger, the Deleted table holds the original values, and the Inserted table holds the new values.

 

[j252ewv]

You have both helped no end

Many thanks.