[RBASE-L] - Re: [No Subject]

Jim Belisle Mon, 28 Mar 2011 12:36:57 -0700

Thanks James. I will look into this as an alternative to the UPDATE
command.


 

James Belisle

________________________________

From: [email protected] [mailto:[email protected]] On Behalf Of James
Bentley
Sent: Monday, March 28, 2011 1:21 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: [No Subject]

 

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

[RBASE-L] - Re: [No Subject]

Reply via email to