What activated the trigger (was: Fastest way to get a unique identifier/tracking column changes)

[Frank.Kolmann@xxxxxxxxxx]

Hi Walden,
Seems like SOX is causing simultaneous solutions to the problem
of auditing files changes.  I considered using triggers but instead
chose journalling simply because triggers have no easy way to tell
'what program activated the trigger'.  I found some stuff that
traverses the program stack looking for the culprit but I have found
nothing that definitively tells me what program caused the trigger
to fire.  How did you solve this, please.

Frank Kolmann


>date: Mon, 12 Jul 2004 13:36:25 -0400
>from: "Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx>
>subject: RE: Fastest way to get a unique identifier/tracking column
>            changes
>
>Reeve,
>
>>I've decided to build a single file with before and after values, etc.
>>for all the tables by using triggers.
>
>We have a medical industry application (not iSeries based) that does
>exactly that. It works like a champ. From a design view you've got the
>right idea.
>
>>My design is to assign every row an "entity number"; the entity number
>>would be like a record serial number, would be unique on a system-wide
>>basis,andwould be the key to the historical tracking table. ...
>>Using SQL's AS IDENTITY with the table name could work to provide
>>a key to a specific record.
>
>If I understand you correctly, you're willing (able) to change the
>original tables to add a new field. Why not use a GUID? Every table
>we're auditing has a column we call RowGUID which is a GUID field. On
>the iSeries it would be a 16 byte character field. This field (and only
>this field) is the key to the audit table. On the audit table we call it
>OwningRowGUID. Want to find all the changes to a row in any table? Run:
>
>Select ColumnName, OldValue, NewValue, UpdateDate, UpdateUser
>            from AuditLog
>            where OwningRowGUID = :RowGUID
>And you're all set.
>Some things we've done (or wish we'd done) to make our life easier:
>
>1) Don't store the table and column name on the audit log. You've got
>LOTS of rows in that table and it grows like wildfire. Create another
>table called RowColumnLookup in which you store an identity column and
>the original table, column name. Then you need to store a 4 byte int on
>the audit row, not 2 10 byte names. (and in our case the names can be >
>10 bytes so it's even worse)
>
>2) Don't write the triggers by hand. Once you write 10 to 20 of them
>you'll realize that they're all the same with different column names.
>Write a program that you pass the table name to and it reads the schema
>(or dds) and writes the RPG you need. After you've written a few by hand
>you'll realize how easy that really is.
>
>3) In the trigger allocate a new GUID to represent the change you're
>processing. Remember, in this design you'll have multiple rows in the
>audit log for a single change in the original table. If the user changes
>5 columns in a row you'll be inserting 5 rows into the audit table. This
>GUID will be the same for all 5 rows so you can tell these all came from
>the same update.
>
>4) If you have long fields (we have some varchars that can be up to 8000
>characters) then consider holding "small" values on the base audit
>table, and having a second table that has the long old and new columns.
>We had to do that because SQLServer doesn't allow a row greater than 8K
>(one page in SQLServer) and since the old and new value could _each_ be
>8K we needed one row for the old value and one row for the new value.
>
>5) Consider storing some sort of session identifier on the audit table
>too. An obvious choice is Job name, number and user. Then you can also
>answer the query: "Tell me everything Bob did when he logged in last
>night at 8PM after we fired him at 4PM and IT didn't disable his login
>until 9AM this morning." <G>
>
>-Walden
>------------
>Walden H Leverich III
>President & CEO
>Tech Software
>(516) 627-3800 x11
>WaldenL@xxxxxxxxxxxxxxx
>http://www.TechSoftInc.com