auditing trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

auditing trigger

Author

Topic

mfdoom
Starting Member

10 Posts

Posted - 2006-03-20 : 20:14:36

hey all, i found this auditing trigger, currently it just kicks out what was changed and when, id like to add who cause the trigger to fire as well (currently its just set to the "inventory" table). check under the "add the audit fields" comment for info:

CREATE TRIGGER TRG_inventory
ON [DBO].[inventory]
FOR DELETE,INSERT,UPDATE
AS

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_inventory]') 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].[inventory]')
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 THOSE 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 ' - tryin to add who made the update here, figure out what GETUSER translates to

-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_inventory] FROM [DBO].[inventory]'
EXEC(@SQLSTR)
END

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-20 : 20:26:21

WHO :

select system_user

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

mfdoom
Starting Member

10 Posts

Posted - 2006-03-20 : 20:56:18

alrighty, so if i add the following, will we have a winner?:

-- ADD THE AUDIT FIELDS
SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE,CAST(GETUSER() AS system_user) as TRG_USER' -- added: CAST(GETUSER() AS system_user) as TRG_USER

-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_inventory] FROM [DBO].[inventory]'
EXEC(@SQLSTR)
END

IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_inventory] SELECT *,'INSERT' ,GETDATE() GETUSER() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_inventory] SELECT *,'DELETE' ,GETDATE() GETUSER() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_inventory] SELECT *,'UPDATE' ,GETDATE() GETUSER() FROM INSERTED
-- added GETUSER() after GETDATE() for each

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-20 : 21:00:41

change

CAST(GETUSER() AS system_user) as TRG_USER

system_user as TRG_USER

GETUSER() is not a valid function. To get the current user, use system_user

mfdoom
Starting Member

10 Posts

Posted - 2006-03-20 : 21:16:50

so i have added:

-- ADD THE AUDIT FIELDS
SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS
TRG_DATE,system_user as TRG_USER'

-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_inventory] FROM [DBO].[inventory]'
EXEC(@SQLSTR)
END

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

but now im getting error: Msg 213, Level 16, State 1, Procedure TRG_inventory, Line 86
Insert Error: Column name or number of supplied values does not match table definition.

any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-20 : 21:23:05

Just curious ? Why are you creating the audit table AUDIT_inventory inside a trigger ? Why not do it once outside ?

mfdoom
Starting Member

10 Posts

Posted - 2006-03-20 : 21:26:53

you bring up a good point that i hadnt thought about, why waste the overhead?

i can move it outside the loop. i should probably do that.

-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_inventory] FROM [DBO].[inventory]'
EXEC(@SQLSTR)
END

seems to be holding me up, i guess

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-20 : 21:32:24

My point is, can't you pre-create the audit tables ?

Actually you can also do this

select top 0 *, cast('' as char(6)) as TRG_ACTION, cast(getdate() as datetime) as TRG_DATE, system_user as TRG_USER
into   dbo.AUDIT_inventory
from   dbo.inventory

quote:
but now im getting error: Msg 213, Level 16, State 1, Procedure TRG_inventory, Line 86
Insert Error: Column name or number of supplied values does not match table definition.

Check your AUDIT_inventory table does it has the 3 TRG_xxx columns ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-21 : 01:39:37

Also, this is the Forum to post workable script. You should use Developer, Transact-SQL, etc forums to post your questions