What Audit trigger
here will do:
Audit trigger – to audit DML operations on all tables in a
database.
Audit trigger is created by using stored procedure “GenerateAudittrail” & function “Upd_trg_col_populate”.
What
stored procedure “GenerateAudittrail” here will do :
Create exact copy of all tables in a database with extra
audit columns and name suffixed as ‘_audit’.
Create Insert/Update/Delete trigger for all original tables .
Triggers are created in such a way that whenever DML operations
take place on all original tables ,audit
tables will be populated accordingly.
What function “Upd_trg_col_populate”
here will do:
In case of update
operation,only updated column value of that table along with audit columns will
get populated in audit tables
“Upd_trg_col_populate” function is called inside “GenerateAudittrail” procedure.
Step 1: --create test data
CREATE DATABASE Audit_Test
GO
CREATE TABLE Student(
ID INT,
NAME VARCHAR(20),
Department VARCHAR(20))
CREATE TABLE Employee(
ID INT,
NAME VARCHAR(20),
Department VARCHAR(20))
------------------------------------------------------------------
Step 2 :--create stored procedure
CREATE PROCEDURE [dbo].[GenerateAudittrail]
@TableName varchar(128),
@Owner varchar(128),
@AuditNameExtention varchar(128) = '_Audit',
@DropAuditTable bit = 0,
@Audit_Database_Name varchar(128) = 'Audit_Test'
AS
BEGIN
---- Check if table exists
IF not
exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table does not exist'
RETURN
END
---- Check @AuditNameExtention
IF @AuditNameExtention is null
BEGIN
PRINT 'ERROR: @AuditNameExtention cannot be null'
RETURN
END
---- Drop audit table if it exists
and drop should be forced
IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'['+@Audit_Database_Name+'].[' + @Owner + '].[' + @TableName +
@AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable
= 1)
BEGIN
PRINT 'Dropping audit table ['+@Audit_Database_Name+'].[' + @Owner + '].[' + @TableName + @AuditNameExtention +
']'
EXEC ('drop table ' +@Audit_Database_Name+'.'+ @TableName + @AuditNameExtention)
END
---- Declare cursor to loop over
columns
DECLARE TableColumns CURSOR Read_Only
FOR SELECT
C.name, TY.name as TypeName, C.max_length, C.is_nullable, C.collation_name, C.precision, C.scale
FROM SYS.columns C
INNER JOIN SYS.tables T ON T.object_id = C.object_id
INNER JOIN SYS.types TY ON TY.user_type_id = C.user_type_id AND TY.system_type_id
= C.system_type_id
WHERE SCHEMA_NAME(T.SCHEMA_ID) = @Owner AND OBJECT_NAME(T.OBJECT_ID) = @TableName AND T.type = 'U'
ORDER BY C.column_id
OPEN TableColumns
---- Declare temp variable to fetch
records into
DECLARE @ColumnName varchar(128)
DECLARE @ColumnType varchar(128)
DECLARE @ColumnLength varchar(max)
DECLARE @ColumnNullable int
DECLARE @ColumnCollation sysname
DECLARE @ColumnPrecision tinyint
DECLARE @ColumnScale tinyint
---- Declare variable to build
statements
DECLARE @CreateStatement varchar(max)
DECLARE @ListOfFields varchar(max)
SET @ListOfFields = ''
---- Check if audit table exists
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention +
']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
---- AuditTable exists, update
needed
PRINT 'Table already exists. Only triggers will be updated.'
FETCH Next FROM
TableColumns
INTO @ColumnName, @ColumnType,
@ColumnLength, @ColumnNullable, @ColumnCollation,
@ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <>
'text' and
@ColumnType <> 'ntext'
and @ColumnType <>
'image' and
@ColumnType <> 'timestamp')
BEGIN
SET
@ListOfFields = @ListOfFields + @ColumnName + ','
END
FETCH Next FROM
TableColumns
INTO @ColumnName, @ColumnType,
@ColumnLength, @ColumnNullable, @ColumnCollation,
@ColumnPrecision, @ColumnScale
END
END
ELSE
BEGIN
---- AuditTable does not exist,
create new
---- Start of create table
SET @CreateStatement = 'CREATE TABLE ['+@Audit_Database_Name+'].[' + @Owner + '].[' + @TableName +
@AuditNameExtention + ']
('
SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'
FETCH Next FROM
TableColumns
INTO @ColumnName, @ColumnType,
@ColumnLength, @ColumnNullable, @ColumnCollation,
@ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <>
'text' and
@ColumnType <> 'ntext'
and @ColumnType <>
'image' and
@ColumnType <> 'timestamp')
BEGIN
SET
@ListOfFields = @ListOfFields + @ColumnName + ','
SET
@CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
IF
@ColumnType in ('binary','nvarchar', 'varbinary', 'varchar')
BEGIN
--IF
(@ColumnLength = -1)
Set
@CreateStatement = @CreateStatement + '(max) '
--ELSE
-- SET @CreateStatement = @CreateStatement +
'(' + cast(@ColumnLength as varchar(max)) + ') '
END
IF
@ColumnType in ('decimal', 'numeric')
SET
@CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '
IF
@ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET
@CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
IF
@ColumnNullable = 0
SET
@CreateStatement = @CreateStatement + 'NOT '
SET
@CreateStatement = @CreateStatement + 'NULL, '
END
FETCH Next FROM
TableColumns
INTO @ColumnName, @ColumnType,
@ColumnLength, @ColumnNullable, @ColumnCollation,
@ColumnPrecision, @ColumnScale
END
---- Add audit trail columns
SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,'
SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL)'
---- Create audit table
PRINT 'Creating audit table ['+@Audit_Database_Name+'].[' + @Owner + '].[' + @TableName + @AuditNameExtention +
']'
EXEC (@CreateStatement)
---- Set primary key and
default values
SET @CreateStatement = 'ALTER TABLE ['+@Audit_Database_Name+'].[' + @Owner + '].[' + @TableName +
@AuditNameExtention + ']
ADD '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' +
@TableName + @AuditNameExtention + '_AuditDate] DEFAULT
(getdate()) FOR [AuditDate],'
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' +
@TableName + @AuditNameExtention + '_AuditUser] DEFAULT
(suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtention
+ '] PRIMARY KEY CLUSTERED '
SET @CreateStatement = @CreateStatement + '([AuditId]) ON
[PRIMARY], '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' +
@TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' +
rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]'
EXEC (@CreateStatement)
END
CLOSE TableColumns
DEALLOCATE TableColumns
/* Drop Triggers, if they exist */
PRINT 'Dropping
triggers'
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]')
DECLARE
@fn_out_var VARCHAR(MAX)
SELECT
@fn_out_var = (SELECT dbo.Upd_trg_col_populate(@ListOfFields))
/* Create triggers */
PRINT 'Creating
triggers'
EXEC ('CREATE TRIGGER tr_' +
@TableName + '_Insert
ON ' + @Owner +
'.' + @TableName
+ ' FOR INSERT AS
INSERT INTO ' +@Audit_Database_Name+'.'+@Owner +'.'+@TableName+@AuditNameExtention +
'(' + @ListOfFields +
'AuditAction) SELECT ' +
@ListOfFields + '''INSERT''
FROM Inserted')
EXEC ('CREATE TRIGGER tr_' +
@TableName + '_Update
ON ' + @Owner +
'.' + @TableName
+ ' FOR UPDATE AS
INSERT INTO ' +@Audit_Database_Name+'.'+@Owner+'.'+@TableName+@AuditNameExtention +
'(' + @ListOfFields +
'AuditAction) '
+ @fn_out_var)
EXEC ('CREATE TRIGGER tr_' +
@TableName + '_Delete
ON ' + @Owner +
'.' + @TableName
+ ' FOR DELETE AS
INSERT INTO ' +@Audit_Database_Name+'.'+@Owner+'.'+@TableName+@AuditNameExtention +
'(' + @ListOfFields +
'AuditAction) SELECT ' +
@ListOfFields + '''DELETE''
FROM Deleted')
END
GO
Step 3: -- create function
CREATE FUNCTION [dbo].[Upd_trg_col_populate]
(@input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Return VARCHAR(MAX)
DECLARE @Split TABLE(
Columnslist VARCHAR(MAX),
ROWNUM INT
IDENTITY(1,1),
ACTION VARCHAR(100))
DECLARE
@l_len INT,
@i INT=1,
@N INT=1,
@col VARCHAR(100),
@cols VARCHAR(MAX)= @input;
DECLARE
@nullcolumn VARCHAR(MAX),
@query VARCHAR(MAX),
@count BIGINT,
@j INT = 1,
@columns VARCHAR(100),
@first_col VARCHAR(100),
@final_query VARCHAR(MAX)
SET @l_len = LEN(@cols)
WHILE
@i<=@l_len
BEGIN
IF SUBSTRING(@cols,@i,1)=','
BEGIN
SET
@col = SUBSTRING(@cols,@N,@i-@N)
INSERT
INTO @Split(Columnslist)
SELECT
@col
SET
@N = @i+1;
END
SET
@i = @i+1;
END
SELECT
@first_col = Columnslist FROM @Split WHERE
ROWNUM = 1;
SELECT
@count = COUNT(Columnslist) FROM @Split
SET
@query = 'SELECT '
SET
@nullcolumn = ''
SET
@final_query = ''
--SELECT
* FROM @Split
WHILE
@j <= @count
BEGIN
SELECT
@columns = Columnslist FROM
@Split WHERE ROWNUM =
@j;
SET @nullcolumn
= @nullcolumn+'NULLIF(d.'
SET
@nullcolumn = @nullcolumn+@columns+','+'i.'+@columns+'),'
SET @j = @j + 1
END
SET
@query = @query +
@nullcolumn
--SELECT
@query = LEFT(@query,LEN(@query)-1)
SET
@final_query = '''UPDATE'' FROM INSERTED i INNER JOIN DELETED d ON d.'+@first_col+' = i.'+@first_col
SET
@final_query = @query +@final_query
SET
@return = @final_query
RETURN @return
END
GO
--step 4: execute
stored procedure
DECLARE
@table_count int
DECLARE @tablename
Varchar(100)
DECLARE @i int = 1
DECLARE
@MyTableVar table(
Number INT IDENTITY(1,1),
Table_list varchar(200));
INSERT INTO @MyTableVar
SELECT name
FROM sys.tables WHERE TYPE = 'U' AND SCHEMA_NAME(SCHEMA_ID) = 'dbo'
SELECT @table_count =
max(Number) from @MyTableVar
WHILE @i <= @table_count
BEGIN
Select @tablename =
Table_list from @MyTableVar WHERE Number = @i
PRINT
@tablename
EXEC [dbo].[GenerateAudittrail] @tablename,'dbo' ---- To execute
trigger
SET @i = @i + 1
END
--step 5 : check audit table population
by inserting,updating & deleting main tables
INSERT INTO Student SELECT 1,'sathya','ECE'
SELECT * FROM Student
SELECT * FROM Student_Audit
UPDATE Student SET NAME = 'Deepak' WHERE ID = 1
SELECT * FROM Student
SELECT * FROM Student_Audit
DELETE FROM Student
SELECT * FROM Student
SELECT * FROM Student_Audit
DROP DATABASE Audit_Test
Audit trigger in this post
can be used for database with less number of tables.Because creating copy of audit
tables for many tables will not be good.
Also in SQL Server 2008 ,change
tracking concept was introduced to track changes made to tables.I will write
about that in my blog in the future.
I have referred the below link for source
code,but I have made few modifications to existing stored procedure & newly
created function for populating only updated column value in audit tables.
No comments:
Post a Comment