Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Script to create audit table

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-23 : 10:09:03
The following is a script that generates an audit table for a provided table name.

It will:
- Add 5 audit columns to an existing table (DataVersion, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy)
- Create 2 triggers on the production table, one for updates and one for deletes (inserts do not need audit info)
- Create an audit table with similar design as the production table
- Create a primary key index on the audit table matching that of the production table but with the DataVersion also added as a part of it

Prerequisites:
- The stored procedure "sp_ScriptTableForAudit" must be present in the database (provided below)
- The script must be run in the database that the production table resides.
- The script does not handle table names with other schemas than dbo

It has not been very thoroughly tested so please add feedback/bug reports etc to this thread.
/*
Script created by Henning Frettem, aka Lumbago @ SQLTeam, 2010-03-23
BE CAREFUL, THIS SCRIPT WILL MAKE CHANGES TO YOUR TABLES!!
*/

SET NOCOUNT ON

DECLARE
@TableName varchar(100) = 'SampleOrg',
@AuditTableName varchar(200)

SET @AuditTableName = @TableName + '_audit'

DECLARE @SQL nvarchar(max) = ''

--> Add audit columns to the production table if they don't exist already
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'DataVersion')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD DataVersion int NOT NULL DEFAULT 1' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedDate')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedBy')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedDate')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedBy')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)

IF LEN(@SQL) > 0
BEGIN
PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END

--> Create the audit table with the same definition as the production table, but add the IsDeleted column
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @AuditTableName)
BEGIN
DECLARE @AuditTableDefinition table (
RowNumber int IDENTITY(1, 1),
DefinitionRow varchar(max)
)

INSERT INTO @AuditTableDefinition
EXEC sp_ScriptTableForAudit @Tablename = @TableName, @NewTablename = @AuditTableName

--> Need IsDeleted column in audit table
UPDATE @AuditTableDefinition
SET DefinitionRow = ', IsDeleted bit NOT NULL DEFAULT(0) )'
WHERE RowNumber = (SELECT MAX(RowNumber) FROM @AuditTableDefinition)

SELECT @SQL = @SQL + DefinitionRow FROM @AuditTableDefinition

--> Run the create table script in the database
PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END

--> Create Update trigger
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Update')
BEGIN
SET @SQL = @SQL + '
CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Update] ON ' + @TableName + '
FOR UPDATE
AS

SET NOCOUNT ON

UPDATE a SET
a.DataVersion = b.DataVersion + 1,
a.ModifiedDate = GETDATE(),
a.ModifiedBy = SYSTEM_USER
FROM ' + @TableName + ' a
INNER JOIN inserted b
ON '

SELECT @SQL = @SQL + 'a.' + c.name + ' = b.' + c.name + ' AND '
FROM sys.index_columns sc
JOIN sys.columns c
ON sc.object_id = c.object_id
AND sc.column_id = c.column_id
JOIN sys.indexes i
ON sc.object_id = i.object_id
AND sc.index_id = i.index_id
WHERE OBJECT_NAME(sc.object_id) = @TableName
AND i.is_primary_key = 1
ORDER BY index_column_id ASC

SET @SQL = LEFT(@SQL, LEN(@SQL) - 4) + CHAR(13) + CHAR(13)

SET @SQL = @SQL + ' INSERT INTO ' + @AuditTableName + '
('

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ')
SELECT '

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ' = 0
FROM deleted

GO
'

PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END

--> Create delete trigger
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Delete')
BEGIN
SET @SQL = @SQL + '
CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Delete] ON ' + @TableName + '
FOR DELETE
AS

SET NOCOUNT ON

INSERT INTO ' + @AuditTableName + '
('

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ')
SELECT '

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ' = 1
FROM deleted

GO'

PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END


- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-23 : 10:12:24
The following procedure is a slightly modified version of Tim Chapmans sp_ScriptTable from Techrepublic. You can download the original version here: http://blogs.techrepublic.com.com/datacenter/?p=431.

CREATE PROCEDURE sp_ScriptTableForAudit
--> Slightly altered version of http://blogs.techrepublic.com.com/datacenter/?p=431
(
@TableName SYSNAME,
@IncludeFKConstraints BIT = 0,
@IncludePKConstraints BIT = 1,
@IncludeConstraints BIT = 0,
@IncludeIndexes BIT = 0,
@NewTableName SYSNAME,
@UseSystemDataTypes BIT = 0
)
AS
BEGIN


DECLARE @MainDefinition TABLE
(
FieldValue VARCHAR(200)
)

DECLARE @DBName SYSNAME
DECLARE @ClusteredPK BIT
DECLARE @TableSchema NVARCHAR(255)
SET @DBName = DB_NAME(DB_ID())

SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)

DECLARE @ShowFields TABLE
(
FieldID INT IDENTITY(1,1),
DatabaseName VARCHAR(100),
TableOwner VARCHAR(100),
TableName VARCHAR(100),
FieldName VARCHAR(100),
ColumnPosition INT,
ColumnDefaultValue VARCHAR(100),
ColumnDefaultName VARCHAR(100),
IsNullable BIT,
DataType VARCHAR(100),
MaxLength INT,
NumericPrecision INT,
NumericScale INT,
DomainName VARCHAR(100),
FieldListingName VARCHAR(110),
FieldDefinition CHAR(1),
IdentityColumn BIT,
IdentitySeed INT,
IdentityIncrement INT,
IsCharColumn BIT
)

DECLARE @HoldingArea TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)

DECLARE @PKObjectID TABLE
(
ObjectID INT
)

DECLARE @Uniques TABLE
(
ObjectID INT
)

DECLARE @HoldingAreaValues TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)

DECLARE @Definition TABLE
(
DefinitionID SMALLINT IDENTITY(1,1),
FieldValue VARCHAR(200)
)


INSERT INTO @ShowFields
(
DatabaseName,
TableOwner,
TableName,
FieldName,
ColumnPosition,
ColumnDefaultValue,
ColumnDefaultName,
IsNullable,
DataType,
MaxLength,
NumericPrecision,
NumericScale,
DomainName,
FieldListingName,
FieldDefinition,
IdentityColumn,
IdentitySeed,
IdentityIncrement,
IsCharColumn
)

SELECT
DB_NAME(),
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
CAST(ORDINAL_POSITION AS INT),
COLUMN_DEFAULT,
dobj.name AS ColumnDefaultName,
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
DATA_TYPE,
CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
CAST(NUMERIC_PRECISION AS INT),
CAST(NUMERIC_SCALE AS INT),
DOMAIN_NAME,
COLUMN_NAME + ',',
'' AS FieldDefinition,
CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,
CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,
CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,
CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
FROM
INFORMATION_SCHEMA.COLUMNS c
JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'


WHERE c.TABLE_NAME = @TableName
ORDER BY
c.TABLE_NAME, c.ORDINAL_POSITION

SELECT TOP 1 @TableSchema = TableOwner
FROM @ShowFields

INSERT INTO @HoldingArea (Flds) VALUES('(')

INSERT INTO @Definition(FieldValue)
VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)

INSERT INTO @Definition(FieldValue)
VALUES('(')

INSERT INTO @Definition(FieldValue)
SELECT
CHAR(10) + FieldName + ' ' +
CASE
WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
ELSE UPPER(DataType) +
CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +
CASE WHEN IdentityColumn = 1 THEN '' ELSE '' END + -->' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
END +

CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END
FROM @ShowFields

IF @IncludeFKConstraints = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'
FROM
(
SELECT
ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,
REVERSE(SUBSTRING(REVERSE((
SELECT cp.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ParentColumns,
REVERSE(SUBSTRING(REVERSE((
SELECT cr.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ReferencedColumns
FROM sys.foreign_keys fk
) a
WHERE ParentObject = @TableName
END
IF @IncludeConstraints = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = @TableName
END

IF @IncludePKConstraints = 1
BEGIN
INSERT INTO @PKObjectID(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 1 AND
is_primary_key = 1

/*
INSERT INTO @Uniques(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 2 AND
is_primary_key = 0 AND
is_unique_constraint = 1

*/
SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

INSERT INTO @Definition(FieldValue)
SELECT ',CONSTRAINT ' + CASE type
WHEN 'PK'
THEN 'PK_' + @NewTableName + ' PRIMARY KEY ' +
CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END
WHEN 'UQ' THEN name + ' UNIQUE '
END
+
CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +
REVERSE(SUBSTRING(REVERSE((
SELECT
c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.key_constraints ccok
LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id


LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
i.object_id = ccok.parent_object_id AND
ccok.object_id = cco.object_id
FOR XML PATH('')
)), 2, 8000)) + ', DataVersion ASC)'
FROM
sys.key_constraints cco
LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID
LEFT JOIN @Uniques u ON cco.object_id = u.objectID
WHERE
OBJECT_NAME(cco.parent_object_id) = @TableName
END

INSERT INTO @Definition(FieldValue)
VALUES(')')

IF @IncludeIndexes = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' +
REVERSE(SUBSTRING(REVERSE((
SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE
OBJECT_NAME(sc.object_id) = @TableName AND
sc.object_id = i.object_id AND
sc.index_id = i.index_id
ORDER BY index_column_id ASC
FOR XML PATH('')
)), 2, 8000)) + ')'
FROM sys.indexes i
WHERE
OBJECT_NAME(object_id) = @TableName


AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1
AND is_unique_constraint = 0
AND is_primary_key = 0
END
INSERT INTO @MainDefinition(FieldValue)
SELECT FieldValue FROM @Definition
ORDER BY DefinitionID ASC

SELECT * FROM @MainDefinition
END
GO


- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein
Go to Top of Page

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-03-15 : 09:15:55
when ever i tried to create audit tables for master table it is giving error as below:

Msg 2714, Level 16, State 4, Line 1
There is already an object named 'UQ__PNET_LEA__AABE986B1837EBAF' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.


so how to handle this contraint error.

Thanks in advance
Kiranmayee
Go to Top of Page

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-16 : 05:03:56
Hi,

it is giving error as below:

Msg 2714, Level 16, State 4, Line 1
There is already an object named 'UQ_AMGUISECTIONS_SECTION_DESCRIPTION_PROJECCT_ITM_ID' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Please help me how to handle this error


--Ranjit
Go to Top of Page

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-16 : 05:31:08
I handle above error by adding extraname for NameofConstraint as below
WHEN 'UQ' THEN name +'_DataVersion'+ ' UNIQUE '

--Ranjit
Go to Top of Page

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-03-16 : 06:46:40
Thanks a lot it is working fine.
Go to Top of Page

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2012-01-17 : 07:08:19
HI ALL

I have a problem while inserting the records into my audit table.

i have created my audit table same as my source table but with out any primary key and constarints.
i will work fine if there is no a forttachment i.e., filestream
as this filestream rowguid is unique. it will not to track the changes for a record more than once.

can any one help me in solving this problem.

regards,
kiran murali
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-17 : 09:30:16
Kiran-

Please start a new topic for your question.
Go to Top of Page

ianthm
Starting Member

1 Post

Posted - 2012-11-24 : 04:00:19
Hi, I am having some problems running this script and get an 'expects parameter' error. This is a complex script for me to debug given my experience level so I would very much appreciate some expert advice.

This is the script....

USE [Segmantics_Dev]
GO
/****** Object: StoredProcedure [dbo].[sp_ScriptTableForAudit] Script Date: 24/11/2012 08:47:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_ScriptTableForAudit]
-- Add the parameters for the stored procedure here

@name varchar(100),
@audit varchar(200)

AS
BEGIN

/*
Script created by Henning Frettem, aka Lumbago @ SQLTeam, 2010-03-23
BE CAREFUL, THIS SCRIPT WILL MAKE CHANGES TO YOUR TABLES!!
*/

SET NOCOUNT ON
declare
@TableName nvarchar(100),
@AuditTableName nvarchar(200)

SET @TableName = @name
SET @AuditTableName = @audit

DECLARE @SQL nvarchar(max) = ''

--> Add audit columns to the production table if they don't exist already
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'DataVersion')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD DataVersion int NOT NULL DEFAULT 1' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedDate')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedBy')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedDate')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedBy')
SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)

IF LEN(@SQL) > 0
BEGIN
PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END

--> Create the audit table with the same definition as the production table, but add the IsDeleted column
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @AuditTableName)
BEGIN
DECLARE @AuditTableDefinition table (
RowNumber int IDENTITY(1, 1),
DefinitionRow varchar(max)
)

INSERT INTO @AuditTableDefinition
EXEC sp_ScriptTableForAudit @Tablename = @TableName, @NewTablename = @AuditTableName

--> Need IsDeleted column in audit table
UPDATE @AuditTableDefinition
SET DefinitionRow = ', IsDeleted bit NOT NULL DEFAULT(0) )'
WHERE RowNumber = (SELECT MAX(RowNumber) FROM @AuditTableDefinition)

SELECT @SQL = @SQL + DefinitionRow FROM @AuditTableDefinition

--> Run the create table script in the database
PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END

--> Create Update trigger
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Update')
BEGIN
SET @SQL = @SQL + '
CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Update] ON ' + @TableName + '
FOR UPDATE
AS

SET NOCOUNT ON

UPDATE a SET
a.DataVersion = b.DataVersion + 1,
a.ModifiedDate = GETDATE(),
a.ModifiedBy = SYSTEM_USER
FROM ' + @TableName + ' a
INNER JOIN inserted b
ON '

SELECT @SQL = @SQL + 'a.' + c.name + ' = b.' + c.name + ' AND '
FROM sys.index_columns sc
JOIN sys.columns c
ON sc.object_id = c.object_id
AND sc.column_id = c.column_id
JOIN sys.indexes i
ON sc.object_id = i.object_id
AND sc.index_id = i.index_id
WHERE OBJECT_NAME(sc.object_id) = @TableName
AND i.is_primary_key = 1
ORDER BY index_column_id ASC

SET @SQL = LEFT(@SQL, LEN(@SQL) - 4) + CHAR(13) + CHAR(13)

SET @SQL = @SQL + ' INSERT INTO ' + @AuditTableName + '
('

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ')
SELECT '

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ' = 0
FROM deleted

GO
'

PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END

--> Create delete trigger
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Delete')
BEGIN
SET @SQL = @SQL + '
CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Delete] ON ' + @TableName + '
FOR DELETE
AS

SET NOCOUNT ON

INSERT INTO ' + @AuditTableName + '
('

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ')
SELECT '

SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @AuditTableName

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
SET @SQL = @SQL + ' = 1
FROM deleted

GO'

PRINT @SQL
EXEC sp_executesql @SQL
SET @SQL = ''
END




END



and this is the error

ALTER TABLE Test1 ADD DataVersion int NOT NULL DEFAULT 1
ALTER TABLE Test1 ADD CreatedDate datetime NOT NULL DEFAULT GETDATE()
ALTER TABLE Test1 ADD CreatedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()
ALTER TABLE Test1 ADD ModifiedDate datetime NOT NULL DEFAULT GETDATE()
ALTER TABLE Test1 ADD ModifiedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()

Msg 201, Level 16, State 4, Procedure sp_ScriptTableForAudit, Line 0
Procedure or function 'sp_ScriptTableForAudit' expects parameter '@name', which was not supplied.


CREATE TRIGGER [trgTest1Update] ON Test1
FOR UPDATE
AS

SET NOCOUNT ON

UPDATE a SET
a.DataVersion = b.DataVersion + 1,
a.ModifiedDate = GETDATE(),
a.ModifiedBy = SYSTEM_USER
FROM Test1 a
INNER JOIN inserted b
ON a.id = b.id

INSERT INTO Test1Test
)
SELEC = 0
FROM deleted

GO

Msg 102, Level 15, State 1, Procedure trgTest1Update, Line 15
Incorrect syntax near ')'.

CREATE TRIGGER [trgTest1Delete] ON Test1
FOR DELETE
AS

SET NOCOUNT ON

INSERT INTO Test1Test
)
SELEC = 1
FROM deleted

GO
Msg 102, Level 15, State 1, Procedure trgTest1Delete, Line 9
Incorrect syntax near ')'.

(1 row(s) affected)

Go to Top of Page
   

- Advertisement -