3

I want to do after I an INSERT in table X to copy that record into another History table immediately.

Now the table has the primary key as an Identity column, so the record won't have an primary key id until it is actually inserted.

My question is if I do a trigger on this record will I get the identity id for that record or will it still be blank?

gotqn
  • 42,737
  • 46
  • 157
  • 243
dotnetN00b
  • 5,021
  • 13
  • 62
  • 95

3 Answers3

5

Yes the identity is available in the trigger but make sure you get that id correctly.

@@identity, SCOPE_IDENTITY etc are NOT what you want to do in a trigger! 

SELECT @id = id FROM inserted

Is also a bad idea.

Always write your triggers to expect multiple changes being made simultaneously. The above approaches will all cause subtle but important errors when you insert more than one record into the table at a time.

The correct approach is to insert into your audit table FROM the inserted table

i.e. 

INSERT INTO myAuditTable(Id, Datetime, user)
SELECT id, GETDATE(), USER_NAME())
FROM inserted
Code Magician
  • 23,217
  • 7
  • 60
  • 77
3

if you do the 'after insert' trigger, the record is already there with a value for the identity column.

Rodolfo
  • 4,155
  • 23
  • 38
2

Just make sure you declare the trigger as "AFTER" insert, not "FOR" or "INSTEAD OF" (guess you wouldn't use the last one... ;)

http://msdn.microsoft.com/en-us/library/ms189799.aspx

Erik A. Brandstadmoen
  • 10,430
  • 2
  • 37
  • 55