March 20, 2006 at 6:53 pm
i stumbled across this trigger for auditing purposes. currently, when it fires, it will post the time and document the change which was made. i would like for it to also show which user made the change. any ideas? ive included comments in the "add the audit fields" section:
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-2 INTEGER
DECLARE @SQLSTR AS VARCHAR(8000)
DECLARE @CURCOL SYSNAME
DECLARE @COLTYPE AS VARCHAR(10)
-- SETUP VARIABLES
SET @SQLSTR = ''
SET @CUR=1
SELECT @max-2 = MAX(ID) FROM @MEMTABLE
-- LOOP EVEY 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'
-- 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-2 - 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,
-- 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
March 21, 2006 at 1:23 pm
There are a number of system variables which hold the current user identity, used_id etc. search in BOL and add that to your trigger.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 22, 2006 at 2:38 pm
I am confused by this. Where did you find and have you gotten it to successfully work. Then even if you do it will fail the moment you add or remove a column from Inventory. I think conceptually it is pretty cool but I don't think it will work as well as you hope. That is unless you have really altered the code a lot as I cannot get it to work even after taking your noted line out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply