Trigger for insert, update, delete

sql-server triggers audit
Tech Xie picture Tech Xie · Nov 6, 2010 · Viewed 24.2k times · Source

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!

Answer

bobs picture bobs · Nov 6, 2010

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 questions

Creating audit triggers in SQL Server

I need to implement change tracking on two tables in my SQL Server 2005 database. I need to audit additions, deletions, updates (with detail on what was updated). I was planning on using a trigger to do this, but after poking …

Read More
When do triggers fire and when don't they

Pretty general question regarding triggers in SQL server 2005. In what situations are table triggers fired and what situations aren't they? Any code examples to demonstrate would be great. I'm writing a audit based databases and just want to be aware …

Read More
How To Create Generic SQL Server Stored Procedure To Perform Inserts Into Audit Table Based on Inserted and Deleted In Trigger

I have implemented an audit trail framework based on the information provided by the first answer to the following post: SQL Server history table - populate through SP or Trigger? Ultimately, the framework that I have implemented uses three triggers …

Read More