Here's one strategy: CREATE TABLE AuditUpdates + ( + AudUpID INTEGER NOT NULL PRIMARY KEY, + AudUpTable TEXT (18), + AudUpTablePKValue INTEGER, + AudUpColumn TEXT (18), + AudUpOldValue TEXT (50), + AudUpNewValue TEXT (50), + AudUpUserID INTEGER, + AudUpTime DATETIME + ) AUTONUM AudUpID in AuditUpdates USING 1, 1
For each table you will audit, create an SP that does SELECT ... WHERE CURRENT OF SYS_OLD, and SELECT ... WHERE CURRENT of SYS_NEW. For each column you are auditing, check each old variable against its new counterpart, and when it changes, or goes from null to a value, or from a value to null, insert a row into the tracking table. If it's a note or varchar column, maybe get the SLEN of the column and just insert a note that the comment was changed and its new length. If you don't log in users with integer identifiers, change the AuditUpdate.AudUpUserID to the appropriate data type. Don't allow true deletes, just have a "deleted" column that can be set to "1", when an authorized users picks the "Delete" button or menu choice. In almost all where clauses, you can add a WHERE deletecolumn = 0 condition, or better yet, create a view with that condition, and use the view for most queries. (Use a separate table to log who chose to "delete" and when.) On Thu, 9 Jan 2003 12:05:16 -0800 (PST), marc schluter wrote: >I still need to find an easy way to save the old >record before it was changed so I have an audit trail. > >Triggers seem to be the way to go but I haven't used >them, so I am a little lost as where to start. >
