Create Audit Tables with Triggers

Some times it is require to create audit tables and triggers for more than one tables.
After searching on net could not find solution which I am finding for so i have created one.
This might suites to your requirement as well. enjoy!! 🙂

USE MASTER
GO
SET ansi_nulls ON GO
SET quoted_identifier OFF GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name ='sp_create_audit')
DROP PROCEDURE sp_create_audit
GO
CREATE PROCEDURE SP_Create_Audit @tableName SYSNAME = NULL AS
BEGIN
DECLARE @sqlCreate_audit VARCHAR(4000), @sqlCreate_audit_trigger VARCHAR(4000), @audit_table SYSNAME , @audit_Trigger_name SYSNAME
SET @audit_table = @tableName + "_Audit" SET @audit_Trigger_name = "TG_"+@tableName+"_Audit"
SET @sqlCreate_audit = "if exists (select * from sys.Tables where name = '"+ @audit_table+"‘)
drop table "+@audit_table
EXEC(@sqlCreate_audit)
SET @sqlCreate_audit = " Select * Into " + @audit_table + " from " + @tableName + " where 1=0 " EXEC(@sqlcreate_audit)
SET @sqlCreate_audit = " Alter table "+ @audit_table + " add operation varchar(50), [user] sysname, date datetime "
EXEC(@sqlcreate_audit)
SET @sqlCreate_audit_trigger = " if exists (select * from sys.triggers where name='" + @audit_Trigger_name+ "‘) drop trigger " + @audit_Trigger_name
EXEC(@sqlCreate_audit_trigger)
SET @sqlCreate_audit_trigger = " create trigger " + @audit_Trigger_name + " on " + @tableName + " After Update, Delete AS Begin declare @user sysname, @datetime datetime select @user = user, @datetime=getdate() if exists (select * from updated) begin insert into "+ @audit_table + " select *,'Before',@user,@datetime from inserted insert into "+@audit_table + " select *,'After', @user,@datetime from deleted end else insert into "+ @audit_table+" select *,'Deleted',@user, @datetime from deleted END"
EXEC(@sqlCreate_audit_trigger)
END
GO
SET quoted_identifier ON

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s