Hi.
I'm working on a trigger for logging changes made to a particular table.
For ugly legacy reasons, the table has 150+ fields, and currently
the trigger is a long string of 'If UPDATE(Fieldname)' statements.
Is there a better way?
I am considering trying to make a select statement that
finds all the field names that are updated, then somehow
iterating through them, converting if needed, and
logging. While this would make for much cleaner code,
I suspect it would be much slower, and speed is a
fair concern.

Any suggestions would be helpful.

Thanks!

Kyle wrote:
Show quoteHide quoteThe easiest way to do what you want is to use a change history table
that matches the design of this table and has a few additional columns:
AuditUserName, AuditDate, and AuditChange (with a default for I / U /
D).

From the trigger determine the mode (assuming the trigger is used for
inserts, updates and deletes). If there are rows in inserted and deleted
it's an UPDATE (U), just deleted DELETE (D), and just inserted INSERT
(I). Insert all the rows from the deleted table with an AuditType of D,
SUSER_SNAME() for the AuditUserName, and getdate() for the AuditDate.
From the inserted table, insert all rows with and I or U, depending on
mode.

You can add any other columns you find useful.

Show quote Hide quote You may want to do some reading in the BOL on the CREATE TRIGGER command
itself.   Take a look at the following code snippet.  There is more in
there, but this may help you out some.  There is additional definition on
the bit pattern etc.


<Snipped from BOL>
IF (COLUMNS_UPDATED())

Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or
columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit
pattern that indicates which columns in the table were inserted or updated.

The COLUMNS_UPDATED function returns the bits in order from left to right,
with the least significant bit being the leftmost. The leftmost bit
represents the first column in the table; the next bit to the right
represents the second column, and so on. COLUMNS_UPDATED returns multiple
bytes if the table on which the trigger is created contains more than 8
columns, with the least significant byte being the leftmost. COLUMNS_UPDATED
will return the TRUE value for all columns in INSERT actions because the
columns have either explicit values or implicit (NULL) values inserted.

COLUMNS_UPDATED can be used anywhere inside the body of the trigger



</Snipped from BOL>





Rick Sawtell

MCT, MCSD, MCDBA

Select Query
Multilanguage support
Combining Two Tables ?
SQL performance problem
Counting Records