Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create an audit trigger on a table Expand / Collapse
Author
Message
Posted Monday, October 1, 2007 3:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2008 12:56 PM
Points: 281, Visits: 2
Comments posted to this topic are about the item Create an audit trigger on a table


Post #404908
Posted Thursday, April 16, 2009 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 6:45 AM
Points: 1, Visits: 35
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.


Post #698569
Posted Thursday, August 27, 2009 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 4, 2010 2:47 PM
Points: 4, Visits: 24
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?
Post #778585
Posted Tuesday, September 1, 2009 1:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 3:18 AM
Points: 80, Visits: 338
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
Post #781015
Posted Sunday, November 22, 2009 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:07 AM
Points: 9, Visits: 30
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
Post #822977
Posted Tuesday, November 23, 2010 4:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 9, 2012 10:33 PM
Points: 31, Visits: 146
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)?
Post #1025596
Posted Wednesday, December 8, 2010 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 5, 2013 7:48 AM
Points: 1, Visits: 60
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.
Post #1031984
Posted Wednesday, December 8, 2010 3:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 9, 2012 10:33 PM
Points: 31, Visits: 146
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.
Post #1032210
Posted Wednesday, May 18, 2011 3:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 3:34 AM
Points: 477, Visits: 1,908
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 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 = MAX(ID) FROM @MEMTABLE

-- LOOP EVERY 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' 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 - 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 - 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
Post #1110794
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse