UPDATE function in a trigger - problem 1

[jjefferson]

I'm working on a trigger which selects column names from a table to determine which columns to "audit". The user can then add or remove column names from this control table and the trigger will not need modification. At least, that's what I hope to do.

I have a problem with the UPDATE() function. My trigger works fine when I use the actual column name (if update (realname)...).

However, I can't seem to use "if update (@tempname)...", where I've populated the @tempname variable with some column name.

Perhaps the function won't accept a variable. Or, it may be that this should work, and my problem is elsewhere, perhaps where I'm initializing the @tempname variable.

Thanks for any advice.

-Jim-

 

[SQLSister]

It would be a very bad idea to allow users to determine which columns to audit. This would be a violation of the whole idea of auditing and if you are auditing for legal reasons would invalidate your whole audit scheme. This is a separation of duties issue - you do not allow the people who are being audited to determine what information they want saved in the audit tables. This opens you up for fraud.

Also triggers should never for performance reasons use dynamic SQL or any kind of cursor if it can be avoided.

Questions about posting. See faq183-874

 

[jjefferson]

I can see now that "audit" was a very poor choice of words on my part; sorry. This case has nothing to do with auditing, legal, etc. What my goal is, is to have a user-maintainable table where they can add or remove column names that belong to another data table. We have no business reason to not allow this, in this case.

The trigger would be on the data table, and on update of the data table the trigger would find out which fields it needs to examine from the list in the user-maintained table.

This way, the application responds to the user's business needs for flexibility and rapid response, something we can not get out of our DBAs here.

I was told that killing the DBAs would be frowned upon, so this became "Plan B". Performance isn't an issue in this case as well, and I've written some list processing that doesn't use a cursor (though with the column name table only containing 10 to 12 rows I can't measure a performance difference when using a cursor and when not, so I may go back to the cursor for clarity of code).

I've done some other digging (Books Online doesn't speak to this; they only say the column can't be computed) and I think I've come to the conclusion that the UPDATE() function needs the actual column name and can't use a variable. So we may be stuck with the old-fashioned way of modifying the trigger every time we need to change the field list.

Hope I'm wrong, but just wanted outside confirmation that "IF UPDATE(@COLNAME)..." won't work, where @COLNAME contains a valid column name.

Thanks!

-Jim-

 

[SQLSister]

I've never tried this, but have you tried creating the update statments using dynamic SQL rather than if update(@somevariable)? Might work.

Questions about posting. See faq183-874

 

[jjefferson]

I will give that a try. In this very limited situation I think the performance will be adequate.

Thanks for the hint!

-Jim-