syntax errors 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)

syntax errors in Audit trigger

Author

Topic

Russell_sivalingam
Starting Member

1 Post

Posted - 2006-04-03 : 06:12:49

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

Russell Sivalingam
Applications Support Analyst
SEEDA Headquarters, Cross Lanes,
Guildford, Surrey,
England
GU1 1YA
T: +44 (0) 1483 501 356
Mobile : +44 7877634723

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-03 : 10:21:06

Do NOT try to do what you are trying to do.

Remember, triggers get fired for every transaction that hits the table, and auditing triggers have to process every record that is affected. Inefficient triggers can have significant performance impacts on your application, as well as making debugging really difficult.

Trying to write a generic auditing trigger like this is not wise. You should write a simple and efficient custom trigger for each table you need to audit. And yes, enumerate the columns.

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 11:35:44

Hi Russell_sivalingam, Welcome to SQL Team!

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Triggers+to+audit+changes

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources