SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Script to create audit table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 03/23/2010 :  10:09:03  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 03/23/2010 :  10:12:24  Show Profile  Reply with Quote
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

India
55 Posts

Posted - 03/15/2011 :  09:15:55  Show Profile  Reply with Quote
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

India
182 Posts

Posted - 03/16/2011 :  05:03:56  Show Profile  Reply with Quote
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

India
182 Posts

Posted - 03/16/2011 :  05:31:08  Show Profile  Reply with Quote
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

India
55 Posts

Posted - 03/16/2011 :  06:46:40  Show Profile  Reply with Quote
Thanks a lot it is working fine.
Go to Top of Page

kiranmurali
Yak Posting Veteran

India
55 Posts

Posted - 01/17/2012 :  07:08:19  Show Profile  Reply with Quote
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

USA
15676 Posts

Posted - 01/17/2012 :  09:30:16  Show Profile  Visit robvolk's Homepage  Reply with Quote
Kiran-

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

ianthm
Starting Member

United Kingdom
1 Posts

Posted - 11/24/2012 :  04:00:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000