syntaxt error in Audit Trigger

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

SQL Server 2000 Forums

SQL Server Development (2000)

syntaxt error in Audit Trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-11 : 09:27:27

Russell writes "Alter TRIGGER trgContactLogTrig
ON tblContact
FOR UPDATE AS

DECLARE @Columns VARCHAR(200)
DECLARE @Count INTEGER

DECLARE @ColCount INTEGER
DECLARE @SQLSTR AS VARCHAR(8000)
DECLARE @Byte INTEGER
DECLARE @Bit INTEGER

DECLARE @Test INTEGER
DECLARE @strInserted AS VARCHAR(50)
DECLARE @strDeleted AS VARCHAR(30)
DECLARE @strIns AS VARCHAR(30)
DECLARE @field int
DECLARE @maxfield int
DECLARE @EXECSQLSTRING nvarchar (4000)

SET @maxfield = (( SELECT COUNT(*) FROM information_schema.columns where table_name = 'tblContact' ) + 1)
SET @field = 1

/* SELECT @field = 1, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tblContact' */

SELECT @ColCount = (@maxfield - 1) -- gives the number of columns
SELECT @Byte = ((@ColCount - 1) / 8) + 1
SELECT @Bit = (@ColCount - 1) % 8 + 1
SELECT @Bit = power (2,@Bit -1)

SET @SQLSTR = 'Changes are : '
SET @strInserted = ''
WHILE @field < @maxfield
/* IF (Substring(COLUMNS_UPDATED() , @Byte, 1) = power(2, @Bit-1 )) */
/* IF substring(COLUMNS_UPDATED(),@Byte, 1) & @Bit > 0 */
BEGIN
SET @Columns = ( SELECT COLUMN_NAME FROM information_schema.columns where table_name='tblContact' and ORDINAL_POSITION = @field )

set @EXECSQLSTRING = 'SELECT ' + @Columns + ' from inserted'
EXEC (@EXECSQLSTRING)

--SET @strDeleted = (SELECT @Columns from deleted) -- previous value

--SET @strInserted = (SELECT @Columns from inserted) -- current value

SET @SQLSTR = @SQLSTR + ' | ' + @Columns + '(' + @strDeleted + ',' + @strInserted + ')'
SET @field = @field + 1

END
INSERT INTO tblContactLog VALUES ( 1 , @strInserted , @Bit , 'test', @SQLSTR )
GO

Hi all,

I'm writing the above trigger to audit a table. I wan the trigger to write information of the changed columns only. So far I have got stuck in two places purely because I lack knowledge in SQL Triggers.

The two areas are the columns updated function and executing a dynamic sql statement to return a value to an variable. Can some one please tell me how to use the columns updated so I could track which column has changed and also how to write a dynamic select statement on the inserted table and get a value out to a variable.

Thank you all

Russell"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-04-11 : 09:28:42

Take a look:

http://www.mindsdoor.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html
http://www.mindsdoor.net/SQLTriggers/AuditTrailTrigger.html
http://www.mindsdoor.net/SQLTriggers/Columns_updated.html
http://www.mindsdoor.net/SQLTriggers/ViewAuditFieldChanges.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources