Steve,
I thought I had pasted them into the message. I guess not. Must be old age
creeping in. In any case here they are:
-- following should be in file PRC00049.put
SET PROCEDURE TBDMbrCurWorkAdr LOCK ON
PUT PRC00049.STP AS TBDMbrCurWorkAdr +
'TBDMbrCurWorkAdr create record in MbrPrvWorkAdr. Update count and audit fields
in RegisterOfMbrs'
SET PROCEDURE TBDMbrCurWorkAdr LOCK OFF
RETURN
-- following should be in file PRC00049.STP
-- values from Previous version MbrCurWorkAdr
SET VAR TBD049PMbrNumber INTEGER
SET VAR TBD049PWAdrSource TEXT
SET VAR TBD049PIv01 INTEGER
SET VAR TBD049PWJobTitle TEXT
SET VAR TBD049PIv02 INTEGER
SET VAR TBD049PWCpyName TEXT
SET VAR TBD049PIv03 INTEGER
SET VAR TBD049PWMainAdrLn TEXT
SET VAR TBD049PIv04 INTEGER
SET VAR TBD049PWExtraAdrLn TEXT
SET VAR TBD049PIv05 INTEGER
SET VAR TBD049PWCity TEXT
SET VAR TBD049PIv06 INTEGER
SET VAR TBD049PWStateUSPS TEXT
SET VAR TBD049PIv07 INTEGER
SET VAR TBD049PWUSPSZip TEXT
SET VAR TBD049PIv08 INTEGER
SET VAR TBD049PWUSPSDpbc TEXT
SET VAR TBD049PIv09 INTEGER
SET VAR TBD049PWUSPSCarrRt TEXT
SET VAR TBD049PIv10 INTEGER
SET VAR TBD049PWCountry TEXT
SET VAR TBD049PIv11 INTEGER
SET VAR TBD049PWPhoneNbr TEXT
SET VAR TBD049PIv12 INTEGER
SET VAR TBD049PWFaxNbr TEXT
SET VAR TBD049PIv13 INTEGER
SET VAR TBD049PWebSiteURL TEXT
SET VAR TBD049PIv14 INTEGER
SET VAR TBD049PPrvWOrgDate DATE
SET VAR TBD049PIv15 INTEGER
-- VALUES FROM RegisterOFMbrs
SET VAR TBD049RCntCWorkAdr INTEGER -- This sould always be +1
SET VAR TBD049RCurWAudAct TEXT
SET VAR TBD049RCurWAudDate DATE
SET VAR TBD049RCurWAudTime TIME
SET VAR TBD049RCntPWorkAdr INTEGER
-- SWITCH VALUES
SET VAR TBD049SwInsPrv INTEGER = 0
SELECT MbrNumber, WAdrSource, JobTitle, CpyName, WMainAdrLine, WExtraAdrLine,+
WCity, WStateUSPSCode, WUSPSZipCode, WUSPSDpbc, WUSPSCarrRoute, WCountry,+
WPhoneNbr, WFaxNbr, WebSiteURL, WorkChgDate +
INTO TBD049PMbrNumber, TBD049PWAdrSource IND TBD049PIv01,+
TBD049PWJobTitle IND TBD049PIv02,TBD049PWCpyName IND TBD049PIv03,+
TBD049PWMainAdrLn IND TBD049PIv04,TBD049PWExtraAdrLn IND TBD049PIv05,+
TBD049PWCity IND TBD049PIv06,TBD049PWStateUSPS IND TBD049PIv07,+
TBD049PWUSPSZip IND TBD049PIv08,TBD049PWUSPSDpbc IND TBD049PIv09,+
TBD049PWUSPSCarrRt IND TBD049PIv10,TBD049PWCountry IND TBD049PIv11,+
TBD049PWPhoneNbr IND TBD049PIv12,TBD049PWFaxNbr IND TBD049PIv13,+
TBD049PWebSiteURL IND TBD049PIv14, TBD049PPrvWOrgDate IND TBD049PIv15 +
FROM MbrCurWorkAdr WHERE CURRENT OF SYS_OLD
SELECT CurWorkAdrCnt,CurWorkAudAction,CurWorkAudDate,CurWorkAudTime,+
PrvWorkAdrCnt +
INTO TBD049RCntCWorkAdr INDICATOR TBD049PIv01, TBD049RCurWAudAct +
INDICATOR TBD049PIv02, TBD049RCurWAudDate +
INDICATOR TBD049PIv03, TBD049RCurWAudTime +
INDICATOR TBD049PIv04, TBD049RCntPWorkAdr INDICATOR TBD049PIv05 +
FROM RegisterOfMbrs WHERE MbrNumber = .TBD049PMbrNumber
SET VAR TBD049SwInsPrv = 1
SET VAR TBI056FromOtherTrg INTEGER = 1
INSERT +
INTO MbrPrvWorkAdr (MbrNumber, WAdrSource, JobTitle, CpyName, WMainAdrLine,+
WExtraAdrLine, WCity, WStateUSPSCode, WUSPSZipCode, WUSPSDpbc,+
WUSPSCarrRoute, WCountry, WPhoneNbr, WFaxNbr, WebSiteURL, PrvWorkOrgDate,+
PrvWorkAudAction, PrvWorkAudDate) +
VALUES (.TBD049PMbrNumber, .TBD049PWAdrSource, .TBD049PWJobTitle,+
.TBD049PWCpyName, .TBD049PWMainAdrLn, .TBD049PWExtraAdrLn, .TBD049PWCity,+
.TBD049PWStateUSPS, .TBD049PWUSPSZip, .TBD049PWUSPSDpbc,+
.TBD049PWUSPSCarrRt, .TBD049PWCountry, .TBD049PWPhoneNbr, .TBD049PWFaxNbr,+
.TBD049PWebSiteURL, .TBD049RCurWAudDate, 'Chg ', .#DATE)
-- here set up the items for RegisterOfMbrs update
UPDATE RegisterOfMbrs SET CurWorkAudAction = +
(IFGT(.TBD049SwInsPrv,0,'Chg ',.TBD049RCurWAudAct)), CurWorkAudDate = +
(IFGT(.TBD049SwInsPrv,0,.#DATE,.TBD049RCurWAudDate)), CurWorkAudTime = +
(IFGT(.TBD049SwInsPrv,0,.#TIME,.TBD049RCurWAudTime)), PrvWorkAdrCnt = +
(IFGT(.TBD049SwInsPrv,0,(1 + .TBD049RCntPWorkAdr),.TBD049RCntPWorkAdr)) +
WHERE MbrNumber = .TBD049PMbrNumber
CLEAR VAR TBD049%
-- STP: TBDMbrCurWorkAdr v7.6 05-JUN-2008 By JRB. For Delete of Work Address
-- Insert audit records into MbrPrvWorkAdr and
-- update counts and audit info on RegisterOfMbrs
RETURN
-- following should be in file PRC00051.PUT
SET PROCEDURE TBUMbrCurWorkAdr LOCK ON
PUT PRC00051.STP AS TBUMbrCurWorkAdr +
'TBU MbrCurWorkAdr for changed INSERT into MbrPrvWorkAdr'
SET PROCEDURE TBUMbrCurWorkAdr LOCK OFF
RETURN
-- following should be in file PRC00051.STP
-- values from Previous version MbrCurWorkAdr
SET VAR TBU051PMbrNumber INTEGER
SET VAR TBU051PWAdrSource TEXT
SET VAR TBU051PIv01 INTEGER
SET VAR TBU051PWJobTitle TEXT
SET VAR TBU051PIv02 INTEGER
SET VAR TBU051PWCpyName TEXT
SET VAR TBU051PIv03 INTEGER
SET VAR TBU051PWMainAdrLn TEXT
SET VAR TBU051PIv04 INTEGER
SET VAR TBU051PWExtraAdrLn TEXT
SET VAR TBU051PIv05 INTEGER
SET VAR TBU051PWCity TEXT
SET VAR TBU051PIv06 INTEGER
SET VAR TBU051PWStateUSPS TEXT
SET VAR TBU051PIv07 INTEGER
SET VAR TBU051PWUSPSZip TEXT
SET VAR TBU051PIv08 INTEGER
SET VAR TBU051PWUSPSDpbc TEXT
SET VAR TBU051PIv09 INTEGER
SET VAR TBU051PWUSPSCarrRt TEXT
SET VAR TBU051PIv10 INTEGER
SET VAR TBU051PWCountry TEXT
SET VAR TBU051PIv11 INTEGER
SET VAR TBU051PWPhoneNbr TEXT
SET VAR TBU051PIv12 INTEGER
SET VAR TBU051PWFaxNbr TEXT
SET VAR TBU051PIv13 INTEGER
SET VAR TBU051PWebSiteURL TEXT
SET VAR TBU051PIv14 INTEGER
SET VAR TBU051PPrvWOrgDate DATE
SET VAR TBU051PIv15 INTEGER
-- values from new version MbrCurWorkAdr
SET VAR TBU051NMbrNumber INTEGER
SET VAR TBU051NWAdrSource TEXT
SET VAR TBU051NIv01 INTEGER
SET VAR TBU051NWJobTitle TEXT
SET VAR TBU051NIv02 INTEGER
SET VAR TBU051NWCpyName TEXT
SET VAR TBU051NIv03 INTEGER
SET VAR TBU051NWMainAdrLn TEXT
SET VAR TBU051NIv04 INTEGER
SET VAR TBU051NWExtraAdrLn TEXT
SET VAR TBU051NIv05 INTEGER
SET VAR TBU051NWCity TEXT
SET VAR TBU051NIv06 INTEGER
SET VAR TBU051NWStateUSPS TEXT
SET VAR TBU051NIv07 INTEGER
SET VAR TBU051NWUSPSZip TEXT
SET VAR TBU051NIv08 INTEGER
SET VAR TBU051NWUSPSDpbc TEXT
SET VAR TBU051NIv09 INTEGER
SET VAR TBU051NWUSPSCarrRt TEXT
SET VAR TBU051NIv10 INTEGER
SET VAR TBU051NWCountry TEXT
SET VAR TBU051NIv11 INTEGER
SET VAR TBU051NWPhoneNbr TEXT
SET VAR TBU051NIv12 INTEGER
SET VAR TBU051NWFaxNbr TEXT
SET VAR TBU051NIv13 INTEGER
SET VAR TBU051NWebSiteURL TEXT
SET VAR TBU051NIv14 INTEGER
SET VAR TBU051NPrvWOrgDate DATE
SET VAR TBU051NIv15 INTEGER
-- VALUES FROM RegisterOFMbrs
SET VAR TBU051RCntCWorkAdr INTEGER -- This sould always be +1
SET VAR TBU051RCurWAudAct TEXT
SET VAR TBU051RCurWAudDate DATE
SET VAR TBU051RCurWAudTime TIME
SET VAR TBU051RCntPWorkAdr INTEGER
-- SWITCH VALUES
SET VAR TBU051SwInsPrv INTEGER = 0
--
SELECT MbrNumber, WAdrSource, JobTitle, CpyName, WMainAdrLine, WExtraAdrLine,+
WCity, WStateUSPSCode, WUSPSZipCode, WUSPSDpbc, WUSPSCarrRoute, WCountry,+
WPhoneNbr, WFaxNbr, WebSiteURL, WorkChgDate +
INTO TBU051PMbrNumber, TBU051PWAdrSource IND TBU051PIv01,+
TBU051PWJobTitle IND TBU051PIv02,TBU051PWCpyName IND TBU051PIv03,+
TBU051PWMainAdrLn IND TBU051PIv04,TBU051PWExtraAdrLn IND TBU051PIv05,+
TBU051PWCity IND TBU051PIv06,TBU051PWStateUSPS IND TBU051PIv07,+
TBU051PWUSPSZip IND TBU051PIv08,TBU051PWUSPSDpbc IND TBU051PIv09,+
TBU051PWUSPSCarrRt IND TBU051PIv10,TBU051PWCountry IND TBU051PIv11,+
TBU051PWPhoneNbr IND TBU051PIv12,TBU051PWFaxNbr IND TBU051PIv13,+
TBU051PWebSiteURL IND TBU051PIv14, TBU051PPrvWOrgDate IND TBU051PIv15 +
FROM MbrCurWorkAdr WHERE CURRENT OF SYS_OLD
--
SELECT MbrNumber, WAdrSource, JobTitle, CpyName, WMainAdrLine, WExtraAdrLine,+
WCity, WStateUSPSCode, WUSPSZipCode, WUSPSDpbc, WUSPSCarrRoute, WCountry,+
WPhoneNbr, WFaxNbr, WebSiteURL, WorkChgDate +
INTO TBU051NMbrNumber, TBU051NWAdrSource IND TBU051NIv01,+
TBU051NWJobTitle IND TBU051NIv02,TBU051NWCpyName IND TBU051NIv03,+
TBU051NWMainAdrLn IND TBU051NIv04,TBU051NWExtraAdrLn IND TBU051NIv05,+
TBU051NWCity IND TBU051NIv06,TBU051NWStateUSPS IND TBU051NIv07,+
TBU051NWUSPSZip IND TBU051NIv08,TBU051NWUSPSDpbc IND TBU051NIv09,+
TBU051NWUSPSCarrRt IND TBU051NIv10,TBU051NWCountry IND TBU051NIv11,+
TBU051NWPhoneNbr IND TBU051NIv12,TBU051NWFaxNbr IND TBU051NIv13,+
TBU051NWebSiteURL IND TBU051NIv14, TBU051NPrvWOrgDate IND TBU051NIv15 +
FROM MbrCurWorkAdr WHERE CURRENT OF SYS_NEW
--
IF TBU051PWJobTitle <> .TBU051NWJobTitle +
OR TBU051PWCpyName <> .TBU051NWCpyName +
OR TBU051PWMainAdrLn <> .TBU051NWMainAdrLn +
OR TBU051PWExtraAdrLn <> .TBU051NWExtraAdrLn +
OR TBU051PWCity <> .TBU051NWCity OR +
TBU051PWStateUSPS <> .TBU051NWStateUSPS +
OR TBU051PWUSPSZip <> .TBU051NWUSPSZip +
OR TBU051PWCountry <> .TBU051NWCountry +
OR TBU051PWPhoneNbr <> .TBU051NWPhoneNbr +
OR TBU051PWFaxNbr <> .TBU051NWFaxNbr +
OR TBU051PWebSiteURL <> .TBU051NWebSiteURL OR TBU051PIv02 <> .TBU051NIv02 +
OR TBU051PIv03 <> .TBU051NIv03 OR TBU051PIv04 <> .TBU051NIv04 +
OR TBU051PIv05 <> .TBU051NIv05 OR TBU051PIv06 <> .TBU051NIv06 +
OR TBU051PIv07 <> .TBU051NIv07 OR TBU051PIv08 <> .TBU051NIv08 +
OR TBU051PIv11 <> .TBU051NIv11 OR TBU051PIv12 <> .TBU051NIv12 +
OR TBU051PIv13 <> .TBU051NIv13 OR TBU051PIv14 <> .TBU051NIv14 +
OR TBU051PIv15 <> .TBU051NIv15 THEN
SET VAR TBU051SwInsPrv = 1
SET VAR TBI056FromOtherTrg INTEGER = 1
INSERT +
INTO MbrPrvWorkAdr (MbrNumber, WAdrSource, JobTitle, CpyName,+
WMainAdrLine, WExtraAdrLine, WCity, WStateUSPSCode, WUSPSZipCode,+
WUSPSDpbc, WUSPSCarrRoute, WCountry, WPhoneNbr, WFaxNbr, WebSiteURL,+
PrvWorkOrgDate, PrvWorkAudAction, PrvWorkAudDate) +
VALUES (.TBU051PMbrNumber, .TBU051PWAdrSource, .TBU051PWJobTitle,+
.TBU051PWCpyName, .TBU051PWMainAdrLn, .TBU051PWExtraAdrLn, .TBU051PWCity,+
.TBU051PWStateUSPS, .TBU051PWUSPSZip, .TBU051PWUSPSDpbc,+
.TBU051PWUSPSCarrRt, .TBU051PWCountry, .TBU051PWPhoneNbr,+
.TBU051PWFaxNbr, .TBU051PWebSiteURL, .TBU051RCurWAudDate, 'Chg ', .#DATE)
ENDIF
SELECT CurWorkAdrCnt,CurWorkAudAction,CurWorkAudDate,CurWorkAudTime,+
PrvWorkAdrCnt +
INTO TBU051RCntCWorkAdr INDICATOR TBU051PIv01, TBU051RCurWAudAct +
INDICATOR TBU051PIv02, TBU051RCurWAudDate +
INDICATOR TBU051PIv03, TBU051RCurWAudTime +
INDICATOR TBU051PIv04, TBU051RCntPWorkAdr INDICATOR TBU051PIv05 +
FROM RegisterOfMbrs WHERE MbrNumber = .TBU051PMbrNumber
-- here set up the items for RegisterOfMbrs update
UPDATE RegisterOfMbrs SET CurWorkAudAction = +
(IFGT(.TBU051SwInsPrv,0,'Chg ',.TBU051RCurWAudAct)), CurWorkAudDate = +
(IFGT(.TBU051SwInsPrv,0,.#DATE,.TBU051RCurWAudDate)), CurWorkAudTime = +
(IFGT(.TBU051SwInsPrv,0,.#TIME,.TBU051RCurWAudTime)), PrvWorkAdrCnt = +
(IFGT(.TBU051SwInsPrv,0,(1 + .TBU051RCntPWorkAdr),.TBU051RCntPWorkAdr)) +
WHERE MbrNumber = .TBU051PMbrNumber
--
CLEAR VAR TBU051%
-- STP: TBUMbrCurWorkAdr v7.6 05-JUN-2008 By JRB. For Change of Work Address
-- Insert audit records into MbrPrvWorkAdr and
-- update counts and audit info on RegisterOfMbrs
RETURN
Jim Bentley
American Celiac Society
[EMAIL PROTECTED]
tel: 1-504-737-3293
--- On Wed, 7/30/08, Wills, Steve <[EMAIL PROTECTED]> wrote:
> From: Wills, Steve <[EMAIL PROTECTED]>
> Subject: [RBASE-L] - Re: Transaction logs
> To: "RBASE-L Mailing List" <[email protected]>
> Date: Wednesday, July 30, 2008, 9:36 AM
> Jim, I assume that PRC00051.STP is one of your stored
> procedures, but,
> since you say "Check (it) out ...", I was
> wonderin' where it is?
>
> If I missed something from earlier, my apologies. Been
> outta' town for
> a few days, so I'm late to this party and all the
> others.
>
>
> Thanks,
> Steve in Memphis
>
>
>
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED] On
> Behalf Of James
> Bentley
> Sent: Tuesday, July 29, 2008 3:55pm 15:55
> To: RBASE-L Mailing List
> Subject: [RBASE-L] - Re: Transaction logs
>
> 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.
> > >
> > >
> > >
