Trigger with exec statement blocking execution of triggering cmd?

[Rebecca Lovelace]

I have a trigger on a table. I am trying to dynamically log the
changed fields on the table to another table, so I am iterating
through the bits in COLUMNS_UPDATED() to find what's changed, and
getting the column name programatically. This is all working fine.

If I do a regular insert command in my trigger then everything works
fine. However, since I want to retrieve data from the column name
which I got programatically from the inserted and deleted tables (to
get the old and new values) I wanted to do something like this:
insert into auditTransactionLog (TableName,
PrimaryKeyId,ColumnName,OldValue, NewValue, ActionType) EXEC( 'SELECT
(''cmContactInfo''), I.contactID,'''+ @colname+''', D.'+@colname+',
I.'+@colname+', '+@action+' FROM inserted I INNER JOIN Deleted D on
I.ContactId = D.ContactId')

The presence of this line of code appears to be preventing the
updating of the table with the trigger. Is there some reason why I
can't do the EXEC in the trigger? If I did it without EXEC it works
fine but I have no idea of getting at the D. and I. @colname columns
otherwise.

Thanks for any help!
Rebecca

[Erland Sommarskog]

Rebecca Lovelace (us********@yahoo.com) writes:

I have a trigger on a table. I am trying to dynamically log the
changed fields on the table to another table, so I am iterating
through the bits in COLUMNS_UPDATED() to find what's changed, and
getting the column name programatically. This is all working fine.

If I do a regular insert command in my trigger then everything works
fine. However, since I want to retrieve data from the column name
which I got programatically from the inserted and deleted tables (to
get the old and new values) I wanted to do something like this:
insert into auditTransactionLog (TableName,
PrimaryKeyId,ColumnName,OldValue, NewValue, ActionType) EXEC( 'SELECT
(''cmContactInfo''), I.contactID,'''+ @colname+''', D.'+@colname+',
I.'+@colname+', '+@action+' FROM inserted I INNER JOIN Deleted D on
I.ContactId = D.ContactId')

The presence of this line of code appears to be preventing the
updating of the table with the trigger. Is there some reason why I
can't do the EXEC in the trigger? If I did it without EXEC it works
fine but I have no idea of getting at the D. and I. @colname columns
otherwise.

The inserted/deleted tables are only avaliable directly in a trigger.
The dynamic SQL created by EXEC() constitutes a different scope,
akin a call to a stored procedure.

A possible work around, is to use a temp table and copy inserted/
deleted to this table.

I would however recommend you to reconsider you approach completely.
There are several problems with this approach:

o Columns_updated() only tells whether the column was assigned to in
an UPDATE statement; it does not say whether there was a change.
o Looping over columns and building dynamic SQL may take its time,
and triggers should be fast, as they execute in transaction context.

Better in to get the inserted/deleted variables into table variables,
and then have static SQL foreach column to catch changes. Yes, that
is boring code to write, but you could write a tool that generates
the triggers for you.

Or you could investigate available third-party products for auditing.
Although they do not come for free, they may be more cost-effective
in the long run.

I know of two such products SQL Audit from Red Matrix (www.red-matrix.com)
and Entegra from Lumgigent (www.lumigenet.com). SQL Audit is trigger
based, while Entegra works from a transaction log, and is likely to
be less intrusive.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp