Printable View
Hi all,
im trying to figure out how to do the following.
I have a table orders, tbl_Orders, and a backup orders table tbl_Orders_bak.
Each time a record is updated from the tbl_Orders i want to take a copy of the entire row before the update occurs and put it into the tbl_Orders_bak
what the best to do this. I would have thought a BEFORE UPDATE trigger but it doesnt exist in SQL Server
I used the following:
FOR UPDATE
AS
BEGIN
INSERT INTO tbl_Orders_bak
SELECT * FROM DELECTED
END
dunno if this is a good way or not... but it works.....
FOR without AFTER or INSTEAD of defaults to AFTER - which is good - this trigger only fires AFTER all cascades and ref-integrity is checked.
Then basically your trigger is quite simple - it's taking the contents of the DELETED temp table and inserting into another table.
Seems fine to me!
the only problem is that it doesnt work when using image or ntext fields....
not an issue for my current project... but may be of use again
From MS BOL
Quote:
SQL Server 2000 does not allow text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers
so would this work for an after trigger?
SET @iId = SELECT iId FROM INSERTED
INSERT INTO tbl_Orders_bak
SELECT * FROM tbl_Orders WHERE iId = @iId
DELETE FROM tbl_Orders WHERE iId = @iId
INSERT INTO tbl_Orders
SELECT * FROM INSERTED
I don't use triggers enough (at all actually!) - I would have to scour books online to get a handle on the difference between AFTER and INSTEAD OF...
INSTEAD OF still has the DELETED and INSERTED tables - it's just when the trigger fires that matters - and the fact you can adjust what ACTION occurs in an INSTEAD OF trigger.
ah ok... cheers for the pointers...