Automate Audit Trigger Generation
I have been tasked with a daunting job of scripting I/U/D triggers for our new database. The job of the triggers is to Audit the activity on the tables. What column was changed to what value on what table and by whom. With over 60 tables, this is a daunting task. So off I went scouring this forum for functions and ideas. Finally, I got it together and it works. We have 1 Audit table for reporting and the rest is in the soup of stored procedures and functions.
The only caveat to this is that each table that you are going to audit has to have a ModifiedById (unique identifier) and that the triggers will not work for text or ntext columns.
The single point of entry is to execute the vp_GenerateTriggers.
Parameters are Table Name and Action. Table name can be any table or 'ALL' for all tables. Action can be I, U, D - you guessed it and A for all.
So execute vp_GenerateTriggers 'ALL', 'A' will generate insert update and delete triggers for all tables in the database.
Gut the cript, modify it to your needs, or change the templates in the stored procedures to meet your needs.
-Milos
milosk@vehix.com
Vehix.com
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Audit]
GO
CREATE TABLE [dbo].[Audit] (
[AuditID] [uniqueidentifier] NOT NULL ,
[TypeID] [uniqueidentifier] NOT NULL ,
[TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TablePKID] [uniqueidentifier] NOT NULL ,
[ColumnName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateModified] [datetime] NOT NULL ,
[ModifiedByID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnCleanDefaultValue]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnCleanDefaultValue]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnColumnDefault]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnColumnDefault]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnIsColumnPrimaryKey]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnIsColumnPrimaryKey]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnTableColumnInfo]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnTableColumnInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnTableHasPrimaryKey]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnTableHasPrimaryKey]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnColumnDefault(@sTableName varchar(128), @sColumnName varchar(128))
RETURNS varchar(4000)
AS
BEGIN
DECLARE @sDefaultValue varchar(4000)
SELECT @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @sTableName
AND COLUMN_NAME = @sColumnName
RETURN @sDefaultValue
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnIsColumnPrimaryKey(@sTableName varchar(128), @nColumnName varchar(128))
RETURNS bit
AS
BEGIN
DECLARE @nTableID int,
@nIndexID int,
@i int
SET @nTableID = OBJECT_ID(@sTableName)
SELECT @nIndexID = indid
FROM sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048) = 2048
IF @nIndexID Is Null
RETURN 0
IF @nColumnName IN
(SELECT sc.[name]
FROM sysindexkeys sik
INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
WHERE sik.id = @nTableID
AND sik.indid = @nIndexID)
BEGIN
RETURN 1
END
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnTableColumnInfo(@sTableName varchar(128))
RETURNS TABLE
AS
RETURN
SELECT c.name AS sColumnName,
c.colid AS nColumnID,
dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn,
CASE WHEN t.name IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar') THEN 1
WHEN t.name IN ('decimal', 'numeric') THEN 2
ELSE 0
END AS nAlternateType,
c.length AS nColumnLength,
c.prec AS nColumnPrecision,
c.scale AS nColumnScale,
c.IsNullable,
SIGN(c.status & 128) AS IsIdentity,
t.name as sTypeName,
dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue
FROM syscolumns c
INNER JOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype
WHERE c.id = OBJECT_ID(@sTableName)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnTableHasPrimaryKey(@sTableName varchar(128))
RETURNS bit
AS
BEGIN
DECLARE @nTableID int,
@nIndexID int
SET @nTableID = OBJECT_ID(@sTableName)
SELECT @nIndexID = indid
FROM sysindexes
WHERE id = @nTableID
AND indid BETWEEN 1 And 254
AND (status & 2048) = 2048
IF @nIndexID IS NOT Null
RETURN 1
RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vp_DeleteTrigger]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vp_DeleteTrigger]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vp_GenerateTriggers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vp_GenerateTriggers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vp_InsertTrigger]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vp_InsertTrigger]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vp_UpdateTrigger]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vp_UpdateTrigger]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC vp_DeleteTrigger
@sTableName varchar(128),
@bExecute bit = 0
AS
BEGIN
set nocount on
DECLARE @sProcText varchar(8000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@sTAB char(1),
@sPrimaryKey varchar(128)
DECLARE @ModifiedByID UNIQUEIDENTIFIER
DECLARE @CrmActionID UNIQUEIDENTIFIER
DECLARE @TableName VARCHAR(50)
DECLARE @TablePKID UNIQUEIDENTIFIER
DECLARE @ColumnName VARCHAR(50)
DECLARE @OldValue VARCHAR(50)
DECLARE @NewValue VARCHAR(50)
-- initialize variables
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @ModifiedByID = '00000000-0000-0000-0000-000000000000'
SET @sPrimaryKey = 'unknown'
SET @NewValue = 'DELETED'
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''tr_' + @sTableName + '_D'')' + @sCRLF
SET @sProcText = @sProcText + @sTAB + 'DROP trigger tr_' + @sTableName + '_D' + @sCRLF
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + 'CREATE trigger tr_' + @sTableName + '_D ON ' + @sTableName + ' FOR DELETE' + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @ModifiedByID UNIQUEIDENTIFIER' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @CrmActionID UNIQUEIDENTIFIER' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @TableName VARCHAR(50)' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @TablePKID UNIQUEIDENTIFIER' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @ColumnName VARCHAR(50)' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @OldValue VARCHAR(50)' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @NewValue VARCHAR(50)' + @sCRLF + @sCRLF
SET @sProcText = @sProcText + 'SELECT @CrmActionID = TypeID FROM Type WHERE Typename = ' + '''' + 'Action' + '''' + ' AND Type = ' + '''' + 'Delete' + '''' + '' + @sCRLF + @sCRLF
Print @sProcText
set @sProcText = ''
-- get the columns for this table
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo(@sTableName)
ORDER BY 3 DESC
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
-- Main loop for columns
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- get the primary column for this table
IF (@bPrimaryKeyColumn = 1)
BEGIN
SET @sPrimaryKey = @sColumnName
END
SET @sProcText = @sProcText + ' SELECT @OldValue = CONVERT(VARCHAR(50),d.' + @sColumnName + '),' + @sCRLF
SET @sProcText = @sProcText + ' @TablePKID = d.' + @sPrimaryKey + ', ' + @sCRLF
SET @sProcText = @sProcText + ' @ModifiedByID = d.ModifiedByID' + @sCRLF
SET @sProcText = @sProcText + ' FROM DELETED d ' + @sCRLF
SET @sProcText = @sProcText + ' INSERT INTO Audit (ModifiedByID, TypeID, TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified)' + @sCRLF
SET @sProcText = @sProcText + ' VALUES (@ModifiedByID, @CrmActionID, ' + '''' + @sTableName + '''' + ', @TablePKID,' + '''' + @sColumnName + '''' + ', @OldValue, @NewValue, GETDATE())' + @sCRLF + @sCRLF
Print @sProcText
set @sProcText = ''
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sCRLF + ' GO' + @sCRLF + @sCRLF
Print @sProcText
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE Procedure vp_GenerateTriggers
--'***************************************************************************
--'Procedure: vp_GenerateTriggers
--'
--'Description: Run I/U/D/S stored procedures for a specific table or all tables
--'
--' Created on: 02/12/2004
--' Created by: Milos Krivka
--'
--' Modification Notes
--' By On Notes
--' -------- -------------------- --------------------------------
--'
--'***************************************************************************
--**********************************
--
-- P A R A M E T E R S
--
--**********************************
(
@Table varchar(20),
@Action char(1),
@Execute bit = 0
)
AS
--- first, clean up the SQL table
set nocount on
Declare @TableName as varchar(100)
BEGIN
IF upper(@Table) <> 'ALL'
BEGIN
IF upper(@Action) = 'I' execute vp_InsertTrigger @Table, @Execute
IF upper(@Action) = 'U' execute vp_UpdateTrigger @Table, @Execute
IF upper(@Action) = 'D' execute vp_DeleteTrigger @Table, @Execute
IF upper(@Action) = 'A'
BEGIN
execute vp_InsertTrigger @Table, @Execute
execute vp_UpdateTrigger @Table, @Execute
execute vp_DeleteTrigger @Table, @Execute
END
END
ELSE
BEGIN
declare table_cursor cursor for
select name
from sysobjects
where xtype = 'U'
and name <> 'Audit'
and name <> 'HTMLText'
and name <> 'History'
open table_cursor
-- get the first row
FETCH NEXT FROM table_cursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
IF upper(@Action) = 'I'
BEGIN
execute vp_InsertTrigger @TableName, @Execute
END
IF upper(@Action) = 'U'
BEGIN
execute vp_UpdateTrigger @TableName, @Execute
END
IF upper(@Action) = 'D'
BEGIN
execute vp_DeleteTrigger @TableName, @Execute
END
IF upper(@Action) = 'A'
BEGIN
execute vp_InsertTrigger @TableName, @Execute
execute vp_UpdateTrigger @TableName, @Execute
execute vp_DeleteTrigger @TableName, @Execute
END
-- get the next table
FETCH NEXT FROM table_cursor
INTO @TableName
END
-- cleanup
CLOSE table_cursor
DEALLOCATE table_cursor
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC vp_InsertTrigger
@sTableName varchar(128),
@bExecute bit = 0
AS
BEGIN
set nocount on
DECLARE @sProcText varchar(8000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@sTAB char(1),
@sPrimaryKey varchar(128)
DECLARE @ModifiedByID UNIQUEIDENTIFIER
DECLARE @CrmActionID UNIQUEIDENTIFIER
DECLARE @TableName VARCHAR(50)
DECLARE @TablePKID UNIQUEIDENTIFIER
DECLARE @ColumnName VARCHAR(50)
DECLARE @OldValue VARCHAR(50)
DECLARE @NewValue VARCHAR(50)
-- initialize variables
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @ModifiedByID = '00000000-0000-0000-0000-000000000000'
SET @sPrimaryKey = 'unknown'
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''tr_' + @sTableName + '_I'')' + @sCRLF
SET @sProcText = @sProcText + @sTAB + 'DROP trigger tr_' + @sTableName + '_I' + @sCRLF
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + 'CREATE trigger tr_' + @sTableName + '_I ON ' + @sTableName + ' FOR INSERT' + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @ModifiedByID UNIQUEIDENTIFIER' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @CrmActionID UNIQUEIDENTIFIER' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @TableName VARCHAR(50)' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @TablePKID UNIQUEIDENTIFIER' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @ColumnName VARCHAR(50)' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @OldValue VARCHAR(50)' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @NewValue VARCHAR(50)' + @sCRLF + @sCRLF
SET @sProcText = @sProcText + 'SELECT @CrmActionID = TypeID FROM Type WHERE Typename = ' + '''' + 'Action' + '''' + ' AND Type = ' + '''' + 'Insert' + '''' + '' + @sCRLF + @sCRLF
Print @sProcText
set @sProcText = ''
-- get the columns for this table
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo(@sTableName)
ORDER BY 3 DESC
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
-- Main loop for columns
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- get the primary column for this table
IF (@bPrimaryKeyColumn = 1)
BEGIN
SET @sPrimaryKey = @sColumnName
END
SET @sProcText = @sProcText + ' IF UPDATE('+ @sColumnName +')' + @sCRLF
SET @sProcText = @sProcText + ' BEGIN' + @sCRLF
SET @sProcText = @sProcText + ' SELECT @TablePKID = i.' + @sPrimaryKey + ',' + @sCRLF
SET @sProcText = @sProcText + ' @OldValue = CONVERT(VARCHAR(50),d.' + @sColumnName + '),' + @sCRLF
SET @sProcText = @sProcText + ' @NewValue = CONVERT(VARCHAR(50),i.'+ @sColumnName +'), ' + @sCRLF
SET @sProcText = @sProcText + ' @ModifiedByID = i.ModifiedByID' + @sCRLF
SET @sProcText = @sProcText + ' FROM INSERTED i LEFT OUTER JOIN DELETED d on i.' + @sPrimaryKey + ' = d.' + @sPrimaryKey + ' ' + @sCRLF
SET @sProcText = @sProcText + ' IF @OldValue <> @NewValue or (@OldValue is null and (@NewValue is not null or @NewValue <> ' + '''' + '''' +')) or (@OldValue is not null and (@NewValue is null or @NewValue = ' + '''' + '''' +' ))' + @sCRLF
SET @sProcText = @sProcText + ' BEGIN' + @sCRLF
SET @sProcText = @sProcText + ' INSERT INTO Audit (ModifiedByID, TypeID, TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified)' + @sCRLF
SET @sProcText = @sProcText + ' VALUES (@ModifiedByID, @CrmActionID, ' + '''' + @sTableName + '''' + ', @TablePKID, ' + '''' + @sColumnName + '''' + ', @OldValue, @NewValue, GETDATE())' + @sCRLF
SET @sProcText = @sProcText + ' END' + @sCRLF
SET @sProcText = @sProcText + ' END' + @sCRLF + @sCRLF
Print @sProcText
set @sProcText = ''
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sCRLF + ' GO' + @sCRLF + @sCRLF
Print @sProcText
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC vp_UpdateTrigger
@sTableName varchar(128),
@bExecute bit = 0
AS
BEGIN
set nocount on
DECLARE @sProcText varchar(8000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@sTAB char(1),
@sPrimaryKey varchar(128)
DECLARE @ModifiedByID UNIQUEIDENTIFIER
DECLARE @CrmActionID UNIQUEIDENTIFIER
DECLARE @TableName VARCHAR(50)
DECLARE @TablePKID UNIQUEIDENTIFIER
DECLARE @ColumnName VARCHAR(50)
DECLARE @OldValue VARCHAR(50)
DECLARE @NewValue VARCHAR(50)
-- initialize variables
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @ModifiedByID = '00000000-0000-0000-0000-000000000000'
SET @sPrimaryKey = 'unknown'
SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''tr_' + @sTableName + '_U'')' + @sCRLF
SET @sProcText = @sProcText + @sTAB + 'DROP trigger tr_' + @sTableName + '_U' + @sCRLF
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + 'CREATE trigger tr_' + @sTableName + '_U ON ' + @sTableName + ' FOR UPDATE' + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @ModifiedByID UNIQUEIDENTIFIER' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @CrmActionID UNIQUEIDENTIFIER' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @TableName VARCHAR(50)' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @TablePKID UNIQUEIDENTIFIER' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @ColumnName VARCHAR(50)' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @OldValue VARCHAR(50)' + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @NewValue VARCHAR(50)' + @sCRLF + @sCRLF
SET @sProcText = @sProcText + 'SELECT @CrmActionID = TypeID FROM Type WHERE Typename = ' + '''' + 'Action' + '''' + ' AND Type = ' + '''' + 'Update' + '''' + '' + @sCRLF + @sCRLF
Print @sProcText
set @sProcText = ''
-- get the columns for this table
DECLARE crKeyFields cursor for
SELECT *
FROM dbo.fnTableColumnInfo(@sTableName)
ORDER BY 3 DESC
OPEN crKeyFields
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
-- Main loop for columns
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- get the primary column for this table
IF (@bPrimaryKeyColumn = 1)
BEGIN
SET @sPrimaryKey = @sColumnName
END
ELSE
BEGIN
SET @sProcText = @sProcText + ' IF UPDATE('+ @sColumnName +')' + @sCRLF
SET @sProcText = @sProcText + ' BEGIN' + @sCRLF
SET @sProcText = @sProcText + ' SELECT @TablePKID = i.' + @sPrimaryKey + ',' + @sCRLF
SET @sProcText = @sProcText + ' @OldValue = CONVERT(VARCHAR(50),d.' + @sColumnName + '),' + @sCRLF
SET @sProcText = @sProcText + ' @NewValue = CONVERT(VARCHAR(50),i.'+ @sColumnName +'), ' + @sCRLF
SET @sProcText = @sProcText + ' @ModifiedByID = i.ModifiedByID' + @sCRLF
SET @sProcText = @sProcText + ' FROM INSERTED i LEFT OUTER JOIN DELETED d on i.' + @sPrimaryKey + ' = d.' + @sPrimaryKey + ' ' + @sCRLF
SET @sProcText = @sProcText + ' IF @OldValue <> @NewValue ' + @sCRLF
SET @sProcText = @sProcText + ' BEGIN' + @sCRLF
SET @sProcText = @sProcText + ' INSERT INTO Audit (ModifiedByID, TypeID, TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified)' + @sCRLF
SET @sProcText = @sProcText + ' VALUES (@ModifiedByID, @CrmActionID, ' + '''' + @sTableName + '''' + ', @TablePKID, ' + '''' + @sColumnName + '''' + ', @OldValue , @NewValue, GETDATE())' + @sCRLF
SET @sProcText = @sProcText + ' END' + @sCRLF
SET @sProcText = @sProcText + ' END' + @sCRLF + @sCRLF
Print @sProcText
set @sProcText = ''
END
FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
END
CLOSE crKeyFields
DEALLOCATE crKeyFields
SET @sProcText = @sCRLF + ' GO' + @sCRLF + @sCRLF
Print @sProcText
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Type]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Type]
GO
CREATE TABLE [dbo].[Type] (
[TypeID] [uniqueidentifier] NOT NULL ,
[TypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SortOrder] [int] NOT NULL ,
[isSystemData] [bit] NOT NULL ,
[ModifiedByID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Type ( TypeID, TypeName, Type, Description, SortOrder, isSystemData, ModifiedByID )
VALUES ( 'B4A3C05E-EB76-449B-9095-F2DE60069C09', 'Action', 'Insert', 'INS', 1, 0, '00000000-0000-0000-0000-000000000000' )
go
INSERT INTO Type ( TypeID, TypeName, Type, Description, SortOrder, isSystemData, ModifiedByID )
VALUES ( 'B8C25EC1-B195-416E-84D4-F3629E07B92B', 'Action', 'Delete', 'DEL', 1, 0, '00000000-0000-0000-0000-000000000000' )
Go
INSERT INTO Type ( TypeID, TypeName, Type, Description, SortOrder, isSystemData, ModifiedByID )
VALUES ( '16EE356E-C53F-455E-82E8-79F9D2DFE0EA', 'Action', 'Update', 'UPD', 1, 0, '00000000-0000-0000-0000-000000000000' )
Go
