SQL Server 2000: Audit Records

Auto Gen Insert trigger etc

http://www.mssqltips.com/tip.asp?tip=1770

 

/*
This script generates a update trigger where you have to insert the updated row in another table like an audit table only if any one of the column was changed. 
The reason I had to have multiple varchar(max) variables is that the results window will not print more than 8192 characters.
*/

DECLARE @tablename VARCHAR(100)
DECLARE @audittable VARCHAR(100)
DECLARE @sqlInsert VARCHAR(MAX)
DECLARE @sqlColumns VARCHAR(MAX)
DECLARE @sqlJoin VARCHAR(MAX)
DECLARE @sqlWhere VARCHAR(MAX)
DECLARE @sqlWhereFinal VARCHAR(MAX)
DECLARE @sqlHeader VARCHAR(MAX)
DECLARE @quote CHAR(1)
SET @quote = CHAR(39)

SET @tablename 'Trigger Table Name'        --Replace this with the table name for which you want to write the update trigger
SET @audittable 'Audit Table Name'        --Replace this with the audit table you want to insert the changed data

--this is just the header info  for the trigger
SET @sqlHeader 'IF OBJECT_ID('+@quote+''+@tablename+'_U'+@quote+') IS NOT NULL
       DROP TRIGGER dbo.'
+@tablename+'_U
GO

CREATE TRIGGER dbo.'
+@tablename+'_U
ON dbo.'
+@tablename+' FOR update
/**************************************************************
* Update trigger for '
+@tablename+'
*
* MODIFICATIONS
* 01/01/2000 xxx New
**************************************************************/
AS '
PRINT @sqlHeader

--select insert into
SELECT @sqlInsert COALESCE(@sqlInsert+' ,' '') + name CHAR(13)+ CHAR(9)  FROM sys.syscolumns WHERE OBJECT_NAME(id@tablename ORDER BY colid
SET @sqlInsert 'insert into dbo.'+@audittable+'('+CHAR(13) +CHAR(9)+@sqlInsert +')'
PRINT @sqlInsert

-- select col list
SELECT @sqlColumns COALESCE(@sqlColumns+' ,' '') +'d.'name CHAR(13) + CHAR(9FROM sys.syscolumns WHERE OBJECT_NAME(id@tablename ORDER BY colid
SET @sqlColumns 'select '+CHAR(13) +CHAR(9)+ @sqlColumns 

--strip the last linebreak
SET @sqlColumns LEFT(@sqlColumns, (LEN(@sqlColumns)-2))
PRINT @sqlColumns

--generate the join condition between Inserted and Deleted tables if the table has Primary key
IF EXISTS(SELECT FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE   WHERE table_name  @tablename AND constraint_name LIKE '%PK%')
BEGIN
               SET 
@sqlJoin ''
               
SELECT @sqlJoin COALESCE(@sqlJoin '') + 'd.'column_name ' = i.'column_name CHAR(13)+CHAR(9) +' and ' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name @tablename AND constraint_name LIKE '%PK%'
               
SET @sqlJoin 'from ' CHAR(13) + CHAR(9) + ' deleted d join inserted i on ' @sqlJoin 
               
--strip off the last 'and'
               
SET @sqlJoin LEFT(@sqlJoin, (LEN(@sqlJoin)-6))
END
ELSE
       SET 
@sqlJoin 'from deleted d, inserted i'

PRINT @sqlJoin

--generate the != clause where you check if atleast one column is changed...
DECLARE @coltype VARCHAR(100)
DECLARE @colname VARCHAR(100)
SET @sqlWhereFinal 'where'  

DECLARE colcursor CURSOR LOCAL FORWARD_ONLY  READ_ONLY FOR SELECT st.namesc.name
FROM sys.syscolumns sc JOIN sys.systypes st ON sc.xtype st.xtype 
WHERE OBJECT_NAME(sc.id@tablename AND sc.name NOT IN
(SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE   WHERE table_name  @tablename AND constraint_name LIKE '%PK%')

OPEN colcursor
FETCH next FROM colcursor INTO @coltype @colname
WHILE @@fetch_status 0
BEGIN
       SET 
@sqlWhere ''
       
PRINT @sqlWhereFinal
       
SET @sqlWhereFinal         ''
       
SET @sqlWhere CASE WHEN @coltype IN('smalldatetime','datetime','sql_variant','ntext','varbinary','varchar','binary','char','timestamp','nvarchar','nchar','xml','sysname')
                                               
THEN  @sqlWhere CHAR(9) + 'isnull(d.'@colname +','''') != isnull(i.'@colname ','''') or'  
                                               
ELSE
                                                          
@sqlWhere CHAR(9) + 'isnull(d.'@colname +',-1) != isnull(i.'@colname ',-1) or'
                                               
END
       SET 
@sqlWhereFinal @sqlWhereFinal @sqlWhere 
       
FETCH next FROM colcursor INTO @coltype @colname
END
CLOSE 
colcursor
DEALLOCATE colcursor

--remove the last 'or'
SET @sqlWhereFinal LEFT(@sqlWhereFinal, (LEN(@sqlWhereFinal)-3))
PRINT @sqlWhereFinal

 


 

Template:

Creaye audit table:

CREATE TABLE [dbo].[za_tbSSP] (
    [TmeStmp] [timestamp] NOT NULL ,
    [DteTmeCreated] [datetime] NOT NULL ,
    [ChngeType] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
    [ChngedBy] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
    [Program] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [WrkStation] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
    [ID] [int] NULL ,
    [suID] [int] NULL ,
    [dyDocumentYear] [int] NULL ,
    [SSP] [int] NULL


) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE  TRIGGER [TG_XXXXX_UPDATE] ON [dbo].[XXXXX]  FOR UPDATE
AS
BEGIN
    INSERT za_XXXXX (DteTmeCreated,ChngeType,ChngedBy,Program,WrkStation,
    **table fields here ** )

   SELECT GETDATE(),
    'U',
    (SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,
    LEN(SYSTEM_USER))),
    LEFT(app_name(),50),
    HOST_NAME(),
    * FROM  DELETED   END

GO


CREATE  TRIGGER [TG_XXXXX_INSERT] ON [dbo].[XXXXX]  FOR INSERT
AS
BEGIN
    INSERT za_XXXXX (DteTmeCreated,ChngeType,ChngedBy,Program,WrkStation,
     **table fields here ** )

   SELECT GETDATE(),
    'I',
    (SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,
    LEN(SYSTEM_USER))),
    LEFT(app_name(),50),
    HOST_NAME(),
    * FROM  INSERTED   END

GO

CREATE  TRIGGER [TG_XXXXX_DELETE] ON [dbo].[XXXXX]  FOR DELETE
AS
BEGIN
    INSERT za_XXXXX (DteTmeCreated,ChngeType,ChngedBy,Program,WrkStation,
     **table fields here ** )

   SELECT GETDATE(),
    'D',
    (SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,
    LEN(SYSTEM_USER))),
    LEFT(app_name(),50),
    HOST_NAME(),
    * FROM  DELETED   END

GO