SQL Server Tigger Update

[Strider]

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

[Strider]

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.....

[szlamany]

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!

[Strider]

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

[szlamany]

From MS BOL

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

[Strider]

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

[szlamany]

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.

[Strider]

ah ok... cheers for the pointers...