I have some code that I can share from a relatively old system, before we had
Update triggers. What I would do now is to take this code and put it into an
update trigger and trap the sys_new and sys_old values of the columns you're
interested in. But this is how it is done without a trigger.
1. Before going into the edit form, project temp tables for all tables
they can change (in my code you'll see tmpOldInvDet).
2. After exiting the form, run the code that checks 3 things (1) did they
add any detail rows, (2) did they delete any detail rows, (3) did they update
data in a row. Initialize a text variable vChanges that will hold a
concatenation of the changes they made.
3. To check for deleting rows:
SET VAR cText TEXT = NULL
SELECT (LISTOF(item)) INTO cText FROM tmpOldInvDet WHERE item +
NOT IN (SELECT item FROM InvDet WHERE invoiceno = .vInvno)
IF cText IS NOT NULL THEN
SET VAR vChanges = (.vChanges + ' Items deleted: ' + .cText)
ENDIF
4. This declare cursor looks at specific columns to check for changes. If
you wanted all columns it probably would be better to incorporate a search to
the sys_columns table. Invdetno is the primary key of the table. In my
case, they cannot change the "item" on a row so that's why I don't check for
it.
I shortened this code a bit so I hope I got all the correct syntax.
DECLARE c1 CURSOR FOR SELECT Invdetno, item, qty, rate +
FROM InvDet WHERE invoiceno = .vInvno
OPEN c1
WHILE 1 = 1 THEN
FETCH c1 INTO cInvdetno, citemno, cqty, crate
IF SQLCODE = 100 THEN
BREAK
ENDIF
SELECT COUNT(*) INTO vCount FROM tmpOldInvDet WHERE Invdetno = .cInvdetno
IF vCount = 0 THEN
-- Added a new item; don't need to do other checks
SET VAR vChanges = (.vChanges + ' New Item ' + CTXT(.citemno) + ';' )
CONTINUE
ENDIF
SELECT qty, rate INTO +
c2qty, c2rate +
FROM tmpOldInvDet WHERE Invdetno = .cInvdetno
-- this is so that the messages are clear; and so it will compare nulls
SET VAR crate = (IFNULL(.crate ,0,.crate))
SET VAR c2rate = (IFNULL(.c2rate ,0,.c2rate))
IF crate <> .c2rate THEN
SET VAR vChanges = (.vChanges + ' Item ' + CTXT(.citemno) + ' rate ' +
+ CTXT(.c2rate) + ' to ' + CTXT(.crate) + ';' )
ENDIF
IF cqty <> .c2qty THEN
SET VAR vChanges = (.vChanges + ' Item ' + CTXT(.citemno) + ' qty ' +
+ CTXT(.c2qty) + ' to ' + CTXT(.cqty) + ';' )
ENDIF
ENDWHILE
5. If vChanges is not null, then I load a row into a Log table, with
vChanges going into a Note column, along with name, date, etc...
Hope this gives you some ideas!
Karen
> Is there a preferred method of tracking changes users make to database
> info? We currently track who was the last user, computer used, date/time to
> make
> changes to rows in a table. While this works great, it does nothing to track
> who changed individual fields within the row or what was changed. It would be
> very common for three users open a form for Table INFO, each change 1 of 20
> different fields, and all we would know was who was the last person at INFO.
> We are looking at several options (i.e., load row into variables, delete
> unchanged rows, convert data to text, and append to a TRANS table with the
> user
> ID info). Is this a case of paranoia on our part? Yep, but we can also see a
> bunch of coming performance problems if we do nothing. Is there a sample in
> R:Base or a basic procedure we should follow? This feels like a potentially
> large project but needed.
>
>
>
