SQL Server trigger - copy row before upgrade

Question

I want to copy a table row before updating, and I am trying to do this as follows:

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

Sorry, I am getting an error

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 guess this is due to the id column; can I do something like an "except" id? I do not want to list all the columns in the trigger, as this should be as dynamic as possible ...

Answer 1

You cannot, in principle. You will need to specify columns or use a separate table:

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

( Triggertest_audit - , Triggertest, //etc - ; , , )

Answer 2

- , Triggertest. ?

• INSERTED Triggertest, Triggertest IDENTITY

• Triggertest IDENTITY, :

  Triggertest (col1, col2, col3) col1, col2, col3 Inserted

:

, SQL .

, , Triggertest, .

, , XML .

:

nullability, TriggerTest... ...

Answer 3

, , , , . , , , - .

, , . , - . , ?

Create trigger, , . , .

, , , -, ( , ) SQL , , - .