SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create an audit trigger on a table


Create an audit trigger on a table

Author
Message
well0549
well0549
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 2
Comments posted to this topic are about the item Create an audit trigger on a table



Kathy A. Head
Kathy A. Head
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.



evanrich
evanrich
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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?
SQL-DBA
SQL-DBA
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 462
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
quetzco
quetzco
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 36
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
Glen Harland
Glen Harland
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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)?
mdv3441
mdv3441
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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.
Glen Harland
Glen Harland
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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.
Karl Klingler
Karl Klingler
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 2100
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search