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
About

hi there, I am software engineer, working in e-commerce company and passionate about all thing digital. On this blog, I share my experiments with different technology.

Posted in SQL Server

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

What people say about post?
seo hizmeti on Awesome wordpress ecommerce…

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 38 other followers

%d bloggers like this: