SQL Server trigger - copy row before updating

Question

Hi all,

I'd like to copy a table's row before updating and I'm trying to do it like this:

CREATE TRIGGER first_trigger_test
on Triggertest
FOR UPDATE
AS
insert into Triggertest select * from Inserted

Unfortunately, I get the error message

Msg 8101, Level 16, State 1, Procedure first_trigger_test, Line 6
An explicit value for the identity column in table 'Triggertest' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I assume it's because of the id-column; can't I do something like 'except' id? I do not want to list all the columns in the trigger as it should be as dynamic as possible...

Answer 1

You can't, basically. You'll either have to specify the columns, or use a separate table:

CREATE TRIGGER first_trigger_test
on Triggertest
FOR UPDATE
AS
insert into Triggertest_audit select * from deleted

(where Triggertest_audit is a second table that looks like Triggertest, but without the primary key/identity/etc - commonly multiple rows per logical source row; not I assumed you actually wanted to copy the old values, not the new ones)

Answer 2

The problem happens because you are trying to set an identity column in Triggertest. Is that your plan?

1. If you want to copy the new identity columns from INSERTED into Triggertest, then define the column in Triggertest without IDENTITY

2. If Triggertest has it's own IDENTITY columns, use this:

   insert into Triggertest (col1, col2, col3) select col1, col2, col3 from Inserted

After comment:

No, you can't without dynamic SQL to detect what table and find all non-identity colums.

However, if you add or remove columns you'll then have a mis-match between trigger table and Triggertest and you'll get a different error.

If you really want it that dynamic, you'd have to concat all columns into one or use XML to ignore schema.

Finally:

Do all your tables have exactly the same number of columns and datatypes and nullability as TriggerTest... because this is the assumption here...

Answer 3

IF you want the table to be built each time the trigger runs then you have no choice but to use the the system tables to find the columns and create a table with those column definitions. Of course your first step will have to be to drop the existing table or the trigger won't work the second time someone updates a record.

However, I think you need to rethink this process. Dropping a table then creating a new one every time you change a record is a seriously bad idea. How is this table in anyway useful when it may get wiped out and rebuilt every second or so?

What you might consider doing instead is create a dynamic process to create the Create trigger scripts that have the correct information for that table but which are not dynamic. Then your configuration people need to run this process every time table changes are made.

Remember it is critical for triggers to do two things, run as fast as humanly possible and account for proccesing all the records inthe batch (triggers should never have row-by-row proccessing or other slow processses or assume only one row will be in inserted or deleted tables) Dynamic SQL in a trigger is porbably also a bad idea as you can't test out all the possibilites beforehand and can bring your whole production server to a screaming halt when some unexpected thing happens.