October 1, 2007 at 3:14 am
Comments posted to this topic are about the item Create an audit trigger on a table
April 16, 2009 at 9:10 am
This script is fantastic! But I have one question. If I wanted a Primary Key on the Audit table, how would I need to amend your script to include this? I would like it called AUDIT_##YOUR_TABLE##ID. I'm pretty new at all this and would appreciate your assistance.
August 27, 2009 at 12:24 pm
just so I'm clear on this, will this record old value and new value? I need to setup an audit table to record when a row is updated, and need it to record what column or columns were changed, and what the old and new values were, and who updated it. does this trigger accomplish that?
September 1, 2009 at 1:31 pm
Really helpful. THANKS for posting this.
I would add just two other variables that can be quite helpful for auditing - user name and machine name. I updated the SQL to do this:
-- ADD THE AUDIT FIELDS
SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE,CURRENT_USER AS TRG_USER, HOST_NAME() AS TRG_PC'
-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_PAYMENT] FROM [DBO].[PAYMENT]'
EXEC(@SQLSTR)
END
IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_PAYMENT] SELECT *,'INSERT' ,GETDATE(),CURRENT_USER,HOST_NAME() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_PAYMENT] SELECT *,'DELETE' ,GETDATE(),CURRENT_USER,HOST_NAME() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_PAYMENT] SELECT *,'UPDATE' ,GETDATE(),CURRENT_USER,HOST_NAME() FROM INSERTED
November 22, 2009 at 8:57 am
Very, Very useful.
How can I avoid the problem of 'ntext', 'text', 'image' fields when executing:
IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_BTPRJ_COSTIIMP] SELECT *,'INSERT' ,GETDATE() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_BTPRJ_COSTIIMP] SELECT *,'DELETE' ,GETDATE() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_BTPRJ_COSTIIMP] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED
?
Antonio
November 23, 2010 at 4:52 pm
quetzco (11/22/2009)
Very, Very useful.How can I avoid the problem of 'ntext', 'text', 'image' fields when executing:
IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_BTPRJ_COSTIIMP] SELECT *,'INSERT' ,GETDATE() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_BTPRJ_COSTIIMP] SELECT *,'DELETE' ,GETDATE() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_BTPRJ_COSTIIMP] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED
?
Antonio
Hi, yes this is a real conundrum. There are several limitations in SQL Server that make this impossible:
1. You can't refer in any way to text, ntext or image fields in the inserted or deleted tables.
2. You can't refer to the inserted or deleted tables in dynamic sql which means you can't build a select statement excluding the forbidden columns.
Can anyone see a way around this without having to explicitly name columns (which would render the solution non-generic)?
December 8, 2010 at 9:34 am
I attempted to use this trigger but receive 2 errors and not sure why it is complaining (SQL Server 2005).
Msg 2715, Level 16, State 3, Procedure TRG_Profile, Line 1
Column, parameter, or variable #8: Cannot find data type SYSNAME.
Parameter or variable '@CURCOL' has an invalid data type.
December 8, 2010 at 3:52 pm
mdv3441 (12/8/2010)
I attempted to use this trigger but receive 2 errors and not sure why it is complaining (SQL Server 2005).Msg 2715, Level 16, State 3, Procedure TRG_Profile, Line 1
Column, parameter, or variable #8: Cannot find data type SYSNAME.
Parameter or variable '@CURCOL' has an invalid data type.
Just a thought: Does your database have a case sensitive collation? If so, the datatype may not be recognised if you refer to it using the wrong case.
May 18, 2011 at 3:48 am
Hello,
to circumvent the text-, ntext-, image- and timestamp-problem i converted this into a template, so with ctrl-shift-m i can insert the proper table name:
-- 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].[<Table_Name, sysname, Table>]')
ORDER BY COLID
DECLARE @CUR INTEGER
DECLARE @max-2 INTEGER
DECLARE @SQLSTR AS VARCHAR(MAX)
DECLARE @FIELDS AS VARCHAR(MAX)
DECLARE @CURCOL SYSNAME
DECLARE @COLTYPE AS VARCHAR(10)
-- SETUP VARIABLES
SELECT @SQLSTR = '', @FIELDS = '', @CUR=1
SELECT @max-2 = MAX(ID) FROM @MEMTABLE
-- LOOP EVERY FIELD
WHILE @CUR <= @max-2
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' BEGIN
-- 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 +'] '
SET @FIELDS = @FIELDS + ' [' + @CURCOL +']'
IF @CUR <= @max-2 - 1 SELECT @SQLSTR=@SQLSTR + ',', @FIELDS = @FIELDS + ','
END
ELSE IF @COLTYPE <> 'TEXT' AND @COLTYPE <> 'NTEXT' AND @COLTYPE <> 'IMAGE' AND @COLTYPE <> 'TIMESTAMP' BEGIN
-- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS
SET @SQLSTR = @SQLSTR + ' ['+@CURCOL + ']'
SET @FIELDS = @FIELDS + ' [' + @CURCOL +']'
IF @CUR <= @max-2 - 1 SELECT @SQLSTR=@SQLSTR + ',', @FIELDS = @FIELDS + ','
END
SET @CUR = @CUR + 1
END
-- ADD THE AUDIT FIELDS
-- SET @FIELDS = @FIELDS + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_PC'
SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE,CURRENT_USER AS TRG_USER, SYSTEM_USER AS TRG_SYSUSER, HOST_NAME() AS TRG_PC'
-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_<Table_Name, sysname, Table>] FROM [DBO].[<Table_Name, sysname, Table>]'
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_<Table_Name, sysname, Table>]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
PRINT @SQLSTR
EXEC(@SQLSTR)
SET @SQLSTR = 'ALTER TABLE [DBO].[AUDIT_<Table_Name, sysname, Table>] ADD RID BIGINT IDENTITY(1,1)'
PRINT @SQLSTR
EXEC(@SQLSTR)
SET @SQLSTR = 'ALTER TABLE [DBO].[AUDIT_<Table_Name, sysname, Table>] ADD CONSTRAINT PK_AUDIT_<Table_Name, sysname, Table> PRIMARY KEY ( RID )'
EXEC(@SQLSTR)
END
SET @SQLSTR = '
CREATE TRIGGER TRG_<Table_Name, sysname, Table>
ON [DBO].[<Table_Name, sysname, Table>]
FOR DELETE,INSERT,UPDATE
AS
-- JUST CHANGE <Table_Name, sysname, 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 @ACT = ''INSERT'' INSERT [DBO].[AUDIT_<Table_Name, sysname, Table>] (' + ISNULL(@Fields,'-') + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_SYSUSER, TRG_PC) SELECT ' + ISNULL(@Fields,'-') + ',''INSERT'' ,GETDATE(),CURRENT_USER,SYSTEM_USER,HOST_NAME() FROM INSERTED
IF @ACT = ''DELETE'' INSERT [DBO].[AUDIT_<Table_Name, sysname, Table>] (' + ISNULL(@Fields,'-') + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_SYSUSER, TRG_PC) SELECT ' + ISNULL(@Fields,'-') + ',''DELETE'' ,GETDATE(),CURRENT_USER,SYSTEM_USER,HOST_NAME() FROM DELETED
IF @ACT = ''UPDATE'' BEGIN
INSERT [DBO].[AUDIT_<Table_Name, sysname, Table>] (' + ISNULL(@Fields,'-') + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_SYSUSER, TRG_PC) SELECT ' + ISNULL(@Fields,'-') + ',''UPDATE'' ,GETDATE(),CURRENT_USER,SYSTEM_USER,HOST_NAME() FROM DELETED
INSERT [DBO].[AUDIT_<Table_Name, sysname, Table>] (' + ISNULL(@Fields,'-') + ', TRG_ACTION, TRG_DATE, TRG_USER, TRG_SYSUSER, TRG_PC) SELECT ' + ISNULL(@Fields,'-') + ',''UPDATE'' ,GETDATE(),CURRENT_USER,SYSTEM_USER,HOST_NAME() FROM INSERTED
END
'
PRINT @SQLSTR
EXEC(@SQLSTR)
Note: since i am using this trigger for debugging purposes i added a second insert for the update to get the before-values...
Best regards
karl
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply