Create an audit trigger on a table

,

Sometimes you need an audit trigger on a table. I have created a generic trigger that will work for (almost)every table. if the audit table is not there yet, It will copy the table structure (but strip identity attributes) and put all the data in an AUDIT_ table.

I believe this will work for almost every table.

Just replace ##YOUR_TABLE## with the tablename you need to audit.

If you like it, vote for it

CREATE TRIGGER TRG_##YOUR_TABLE##
ON [DBO].[##YOUR_TABLE##]
FOR DELETE,INSERT,UPDATE
AS
-- JUST CHANGE ##YOUR_TABLE## INTO YOUR OWN TABLENAME TO MAKE IT WORK 
DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT 
DECLARE @SQLSTRING VARCHAR(2000)

SET @DEL = 0
SET @INS = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1 

IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'

IF @INS = 0 AND @DEL = 0 RETURN

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_##YOUR_TABLE##]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
	-- CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE
	DECLARE @MEMTABLE TABLE
	( 
		ID INT IDENTITY
		,COLUMNAME SYSNAME
		,TYPENAME VARCHAR(20)
	 )
	-- INSERT THE COLUMNAMES AND THE DATATYPES
	INSERT @MEMTABLE 
		(COLUMNAME,TYPENAME) 
		SELECT NAME,TYPE_NAME(XTYPE) 
		FROM SYSCOLUMNS 
		WHERE ID = OBJECT_ID('[DBO].[##YOUR_TABLE##]') 
		ORDER BY COLID

	DECLARE @CUR INTEGER
	DECLARE @MAX INTEGER
	DECLARE @SQLSTR AS VARCHAR(8000)
	DECLARE @CURCOL SYSNAME
	DECLARE @COLTYPE AS VARCHAR(10)

	-- SETUP VARIABLES
	SET @SQLSTR = ''
	SET @CUR=1
	SELECT @MAX = MAX(ID) FROM @MEMTABLE

	-- LOOP EVEY FIELD
	WHILE @CUR <= @MAX
	BEGIN
		-- GET VALUES FROM THE MEMTABLE	
		SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR
		IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER'
			-- WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN 
			-- IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THAT ATTRIBUTES 
			SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS '+@COLTYPE+') AS [' + @CURCOL +'] '
		ELSE
			-- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS
			SET @SQLSTR = @SQLSTR + ' '+@CURCOL + ' AS [' + @CURCOL +'] '
		IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + ','
		SET @CUR = @CUR + 1
	END
	-- ADD THE AUDIT FIELDS
	SET @SQLSTR = @SQLSTR +',CAST(''      '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE'
	-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
	SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_##YOUR_TABLE##] FROM [DBO].[##YOUR_TABLE##]'
	EXEC(@SQLSTR)
END

IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'INSERT' ,GETDATE() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'DELETE' ,GETDATE() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED

Rate

5 (5)

Share

Share

Rate

5 (5)