Jim,
Consider using a BEFORE UPDATE Trigger or possibly AFTER UPDATE Trigger on the
original table which in turn
inserts a record into the other table. to give you an idea of how this would
work I am giving you code of an actual
trigger from one of my applications. It deals with the MbrCurHomeAdr and the
RegisterofMbrs from which data is inserted
into MbrPrvHomeAdr (audit record of address changes). The RegisterofMbrs
contains record counts, audit action, audit date,
and audit time for actions take place on the Address records.
---- begin Before Update trigger --->
-- Columns from Previous version MbrCurHomeAdr
SET VAR TBU048PMbrNumber INTEGER
SET VAR TBU048PHAdrSource TEXT
SET VAR TBU048PIv01 INTEGER
SET VAR TBU048PHMainAdrLn TEXT
SET VAR TBU048PIv02 INTEGER
SET VAR TBU048PHExtraAdrLn TEXT
SET VAR TBU048PIv03 INTEGER
SET VAR TBU048PHCity TEXT
SET VAR TBU048PIv04 INTEGER
SET VAR TBU048PHStateUSPS TEXT
SET VAR TBU048PIv05 INTEGER
SET VAR TBU048PHUSPSZip TEXT
SET VAR TBU048PIv06 INTEGER
SET VAR TBU048PIv07 INTEGER
SET VAR TBU048PIv08 INTEGER
SET VAR TBU048PHCountry TEXT
SET VAR TBU048PIv09 INTEGER
SET VAR TBU048PHPhoneNbr TEXT
SET VAR TBU048PIv10 INTEGER
SET VAR TBU048PHCellNbr TEXT
SET VAR TBU048PIv11 INTEGER
SET VAR TBU048PEMailAdr TEXT
SET VAR TBU048PIv12 INTEGER
SET VAR TBU048PPrvHOrgDate DATE
SET VAR TBU048PIv13 INTEGER
-- Columns from New version MbrCurHomeAdr
SET VAR TBU048NMbrNumber INTEGER
SET VAR TBU048NHAdrSource TEXT
SET VAR TBU048NIv01 INTEGER
SET VAR TBU048NHMainAdrLn TEXT
SET VAR TBU048NIv02 INTEGER
SET VAR TBU048NHExtraAdrLn TEXT
SET VAR TBU048NIv03 INTEGER
SET VAR TBU048NHCity TEXT
SET VAR TBU048NIv04 INTEGER
SET VAR TBU048NHStateUSPS TEXT
SET VAR TBU048NIv05 INTEGER
SET VAR TBU048NHUSPSZip TEXT
SET VAR TBU048NIv06 INTEGER
SET VAR TBU048NIv07 INTEGER
SET VAR TBU048NIv08 INTEGER
SET VAR TBU048NHCountry TEXT
SET VAR TBU048NIv09 INTEGER
SET VAR TBU048NHPhoneNbr TEXT
SET VAR TBU048NIv10 INTEGER
SET VAR TBU048NHCellNbr TEXT
SET VAR TBU048NIv11 INTEGER
SET VAR TBU048NEmailAdr TEXT
SET VAR TBU048NIv12 INTEGER
SET VAR TBU048NPrvHOrgDate DATE
SET VAR TBU048NIv13 INTEGER
-- VALUES FROM RegisterOFMbrs
SET VAR TBU048RCntCHomeAdr INTEGER -- This sould always be +1
SET VAR TBU048RCurHAudAct TEXT
SET VAR TBU048RCurHAudDate DATE
SET VAR TBU048RCurHAudTime TIME
SET VAR TBU048RCntPHomeAdr INTEGER
SET VAR TBU048RCntCEMailAd INTEGER
SET VAR TBU048RCntPEMailAd INTEGER
SET VAR TBU048RCurEMAct TEXT
SET VAR TBU048RCurEMDate DATE
SET VAR TBU048RCurEMTime TIME
SET VAR TBU048RIv01 INTEGER
SET VAR TBU048RIv02 INTEGER
SET VAR TBU048RIv03 INTEGER
SET VAR TBU048RIv04 INTEGER
SET VAR TBU048RIv05 INTEGER
SET VAR TBU048RIv06 INTEGER
SET VAR TBU048RIv07 INTEGER
SET VAR TBU048RIv08 INTEGER
SET VAR TBU048RIv09 INTEGER
SET VAR TBU048RIv10 INTEGER
SET VAR TBU048RIv11 INTEGER
SET VAR TBU048RIv12 INTEGER
SET VAR TBU048RIv13 INTEGER
-- SWITCH VALUES
SET VAR TBU048SwInsEMA INTEGER = 0
SET VAR TBU048SwInsPrv INTEGER = 0
--
SELECT MbrNumber, HAdrSource, HMainAdrLine, HExtraAdrLine, HCity,+
HStateUSPSCode, HUSPSZipCode, HCountry, HPhoneNbr, HCellNbr, EMailAdr,+
HomeChgDate +
INTO TBU048PMbrNumber, TBU048PHAdrSource IND TBU048PIv01,+
TBU048PHMainAdrLn IND TBU048PIv02, TBU048PHExtraAdrLn IND TBU048PIv03,+
TBU048PHCity IND TBU048PIv04, TBU048PHStateUSPS IND TBU048PIv05,+
TBU048PHUSPSZip IND TBU048PIv06, TBU048PHCountry IND TBU048PIv09,+
TBU048PHPhoneNbr IND TBU048PIv10, TBU048PHCellNbr IND TBU048PIv11,+
TBU048PEMailAdr IND TBU048PIv12, TBU048PPrvHOrgDate IND TBU048PIv13,+
FROM MbrCurHomeAdr +
WHERE CURRENT OF SYS_OLD
--
SELECT MbrNumber, HAdrSource, HMainAdrLine, HExtraAdrLine, HCity,+
HStateUSPSCode, HUSPSZipCode, HCountry, HPhoneNbr, HCellNbr, EMailAdr,+
HomeChgDate +
INTO TBU048NMbrNumber, TBU048NHAdrSource IND TBU048NIv01,+
TBU048NHMainAdrLn IND TBU048NIv02, TBU048NHExtraAdrLn IND TBU048NIv03,+
TBU048NHCity IND TBU048NIv04, TBU048NHStateUSPS IND TBU048NIv05,+
TBU048NHUSPSZip IND TBU048NIv06, TBU048NHCountry IND TBU048NIv09,+
TBU048NHPhoneNbr IND TBU048NIv10, TBU048NHCellNbr IND TBU048NIv11,+
TBU048NEmailAdr IND TBU048NIv12, TBU048NPrvHOrgDate IND TBU048NIv13,+
FROM MbrCurHomeAdr +
WHERE CURRENT OF SYS_NEW
--
SELECT CurHomeAdrCnt,CurHomeAudAction,CurHomeAudDate,CurHomeAudTime,+
PrvHomeAdrCnt,CurEMailAdrCnt,PrvEMailAdrCnt,CurEMailAudAction,+
CurEMailAudDate,CurEMailAudTime +
INTO TBU048RCntCHomeAdr IND TBU048RIv01, TBU048RCurHAudAct IND TBU048RIv02,+
TBU048RCurHAudDate IND TBU048RIv03, TBU048RCurHAudTime IND TBU048RIv04,+
TBU048RCntPHomeAdr IND TBU048RIv05, TBU048RCntCEMailAd IND TBU048RIv06,+
TBU048RCntPEMailAd IND TBU048RIv07, TBU048RCurEMAct IND TBU048RIv08,+
TBU048RCurEMDate IND TBU048RIv09, TBU048RCurEMTime IND TBU048RIv10, +
FROM RegisterOfMbrs WHERE MbrNumber = .TBU048PMbrNumber
IF TBU048PHMainAdrLn <> .TBU048NHMainAdrLn +
OR TBU048PHExtraAdrLn <> .TBU048NHExtraAdrLn +
OR TBU048PHCity <> .TBU048NHCity OR +
TBU048PHStateUSPS <> .TBU048NHStateUSPS +
OR TBU048PHPhoneNbr <> .TBU048NHPhoneNbr +
OR TBU048PHCellNbr <> .TBU048NHCellNbr OR TBU048PIv02 <> .TBU048NIv02 +
OR TBU048PIv03 <> .TBU048NIv03 OR TBU048PIv04 <> .TBU048NIv04 +
OR TBU048PIv05 <> .TBU048NIv05 OR TBU048PIv06 <> .TBU048NIv06 +
OR TBU048PIv09 <> .TBU048NIv09 OR TBU048PIv10 <> .TBU048NIv10 +
OR TBU048PIv11 <> .TBU048NIv11 THEN
SET VAR TBI053FromOtherTrg INTEGER = 1
INSERT INTO MbrPrvHomeAdr (MbrNumber, HAdrSource, HMainAdrLine,+
HExtraAdrLine, HCity, HStateUSPSCode, HUSPSZipCode, HCountry, HPhoneNbr,+
HCellNbr, EMailAdr, PrvHomeOrgDate, PrvHomeAudAction, PrvHomeAudDate) +
VALUES (.TBU048PMbrNumber, .TBU048PHAdrSource, .TBU048PHMainAdrLn,+
.TBU048PHExtraAdrLn, .TBU048PHCity, .TBU048PHStateUSPS, .TBU048PHUSPSZip,+
.TBU048PHCountry, .TBU048PHPhoneNbr, .TBU048PHCellNbr, .TBU048PEMailAdr,+
.TBU048PPrvHOrgDate, 'Chg ', .#DATE)
SET VAR TBU048SwInsPrv = 1
SET VAR TBU048RCntPHomeAdr = (1 + .TBU048RCntPHomeAdr)
ENDIF
IF TBU048PEMailAdr <> .TBU048NEmailAdr OR TBU048PIv12 <> .TBU048NIv12 THEN
IF TBU048PEMailAdr IS NOT NULL THEN
INSERT +
INTO MbrPrvEMailAdr (MbrNumber, EMailAdr, PrvEMailAudAction,+
PrvEMailAudDate) +
VALUES (.TBU048PMbrNumber, .TBU048PEMailAdr, 'Chg ', .#DATE)
SET VAR TBU048SwInsEMA = 1
SET VAR TBU048RCntPEMailAd = (1 + .TBU048RCntPEMailAd)
ENDIF
ENDIF
-- Fix potential column value problems
IF TBU048RCurEMDate IS NULL THEN
SET VAR TBU048RCurEMDate=.TBU048RCurHAudDate
ENDIF
IF TBU048RCurEMTime IS NULL THEN
SET VAR TBU048RCurEMTime=.TBU048RCurHAudTime
ENDIF
IF TBU048SwInsPrv = 1 OR TBU048SwInsEMA = 1 THEN
UPDATE RegisterOfMbrs SET CurHomeAudAction = 'Chg ',+
CurHomeAudDate = .#DATE, CurHomeAudTime = .#TIME,+
PrvHomeAdrCnt = .TBU048RCntPHomeAdr, CurEMailAdrCnt = +
(IFEXISTS(.TBU048NEmailAdr,1,0)), CurEMailAudAction= +
(IFEXISTS(.TBU048NEmailAdr,'Chg ',.TBU048RCurEMAct)), CurEMailAudDate= +
(IFEXISTS(.TBU048NEmailAdr,.#DATE,.TBU048RCurEMDate)), CurEMailAudTime= +
(IFEXISTS(.TBU048NEmailAdr,.#TIME,.TBU048RCurEMTime)),+
PrvEMailAdrCnt = .TBU048RCntPEMailAd WHERE MbrNumber = .TBU048PMbrNumber
ENDIF
--
CLEAR VAR TBU048%
-- STP: TBUMbrCurHomeAdr v7.6 19-OCT-2010 By JRB. For Change of Home Address
-- Insert audit records into MbrPrvHomeAdr, MbrPrvEMailAdr and
-- upDate counts and audit info on RegisterOfMbrs
RETURN
<< === End Before Update Trigger Code ==<<
Note only want to maintain records under certain circumstances. Note special
handling of NULL values via the indicator
variable values.
Jim Bentley
American Celiac Society
[email protected]
tel: 1-504-737-3293
>
>From: Jim Belisle <[email protected]>
>To: RBASE-L Mailing List <[email protected]>
>Sent: Sat, March 26, 2011 8:11:07 PM
>Subject: [RBASE-L] - Re: [No Subject]
>
>
>Bernie,
>
>Thanks. That is what I needed.
>
>James Belisle
>
________________________________
>From:[email protected] [mailto: [email protected] ] On Behalf Of jan johansen
>Sent: Saturday, March 26, 2011 7:14 PM
>To: RBASE-L Mailing List
>Subject: [RBASE-L] - Re: [No Subject]
>
>Jim,
>
>Since it doesn't look like it matters which column may be updated just update
>your second table like this.
>
>UPDATE table2 SET +
> colname1 = colname1, +
> colname2 = colname2, +
> colname3 = colname3, +
> colname4 = colname4 +
>FROM table1, table2 +
>WHERE table2.linkcolumnid = table1.linkcolumnid
>
>Jan
>
>
>
>-----Original Message-----
>>From: "Jim Belisle" <[email protected]>
>>To: [email protected] (RBASE-L Mailing List)
>>Date: Sat, 26 Mar 2011 15:59:33 -0500
>>Subject: [RBASE-L] - [No Subject]
>>I have two tables that have the same type information.
>>When updating one table with a form, I want to be able to update the other
>>table
>>at the same time.
>>
>>Since there are 40 fields and I would not know which field might be edited,
>>what
>>would be the best way to go about this?
>>
>>
>>Would I have to have a compID on all FIELDS then have the update command
>>always
>>update all fields?
>>
>>Any suggestions would be appreciated. All fields are text fields
>>
>>
>>James Belisle
>>
