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

Frank,

Remember, I said our application was _not_ on the iSeries. In the JSP,
ASP (yes, the app has both), and SQLServer world we don't have things as
simple as a call stack -- and boy I miss that call stack.

Anyway, in our case we don't really care which program caused the
update, just what user. However, even that was a pain. We can't use the
user id logged into the database in our case since we are using pooled
connections and even if the connections weren't pooled, the "users" in
our case are simply database rows with a username and password, not
users defined to the operating system. Therefore we had to pass the
userid into each and every update & create. We don't have such luck with
delete, but then again we don't delete anything, we just flag the rows
as deleted so it's really an update. It's amazing what the FDA and OHRP
wants to see when a research study kills someone.

-Walden. 


------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Frank.Kolmann@xxxxxxxxxx
Sent: Monday, July 12, 2004 6:12 PM
To: midrange-l@xxxxxxxxxxxx
Subject: What activated the trigger (was: Fastest way to get a unique
identifier/tracking column changes)





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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.