Here is some code I use as Before DELETE, and Before UPDATE triggers. In essence I have a Current work address table which is related to the RegisterOfMbrs table by MbrNumber. I keep an audit history of changes to the MbrCurWorkAdr in MbrPrvWorkAdr. In addition I have audit action, date, time fields along with table record counts in the RegisterOfMbrs table. Check out PRC00051.STP to see hou I determine if fields have changed. In determining if a field has changed you must provide for either the old version or the current version of the field being NULL.
Jim Bentley American Celiac Society [EMAIL PROTECTED] tel: 1-504-737-3293 --- On Tue, 7/29/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > From: [EMAIL PROTECTED] <[EMAIL PROTECTED]> > Subject: [RBASE-L] - Re: Transaction logs > To: "RBASE-L Mailing List" <[email protected]> > Date: Tuesday, July 29, 2008, 1:20 PM > 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. > > > > > >
