Sql-server – Trigger for insert, update, delete

auditsql servertriggers

I want to insert rows into the audit table whenever an insert, update or delete takes place in the master table "Table1" – doesn't matter which column is changed/inserted. I also want to add I, U or D on insert, update or delete. For insert and delete I am checking if rows exist in the inserted and deleted table. What is the best way to approach update.

My code for insert and delete is :

CREATE TRIGGER [dbo].[tr_Table1_InsertUpdate_Table1History_Insert]
ON [dbo].[Table1]
FOR INSERT, DELETE, UPDATE

AS
BEGIN
 IF EXISTS(SELECT * FROM Inserted)
 BEGIN
  INSERT INTO Table1History(...., ModificationType)
  SELECT ..., 'I'
  FROM Inserted
 END


 IF EXISTS(SELECT * FROM Deleted)
 BEGIN
  INSERT INTO Table1History(..., ModificationType)
  SELECT ..., 'D'
  FROM Deleted
 END

END
GO

Kindly help!

Best Answer

For updates, the original values for the row will be added to the deleted table, and the new values for the row will be added to the inserted table. So, to identify inserts, deletes and updates you would do the following

  • Inserts - get the rows from inserted that are not in deleted
  • Deletes - get the rows from deleted that are not in inserted.
  • Updates - get the rows that are in both inserted and deleted
Related Topic