Shawn,
can u post the other 2 as well
thanks
jak
----- Original Message -----
From: "Shawn Hayes" <[email protected]>
To: <[email protected]>
Sent: Thursday, May 27, 2010 7:18 AM
Subject: [U2] Historical File Tracking
This routine is a trigger routine that will track a historical perspective
of a file. It can't be used by copy and pasting it into a routine and
running it. There are 2 programs FILE.OPEN and GET.NEXT.SEQ.NUM that you
will need or need to write (I can give you those if you want also).
FILE.OPEN - facilitates the opening of the files by putting the file name
and variable in a named common to allow for cross account opens.
GET.NEXT.SEQ.NUM - gets the next sequential number (key) for a file.
Once you have this in place, all you need to do is set the file you want to
call this routine as a trigger.
SUBROUTINE TRIGGER.UPDATE.CHANGE.LOG(TRIGGER.NAME, SCHEMA, TABLE, EVENT,
TRIGGER.TIME, NEW.RECORDID, NEW.RECORD, OLD.RECORDID, OLD.RECORD,
ASSOCIATION, ASSOCIATION.EVENT, TRIGGER.COUNT, CHAIN.CASCADE, CASCADE)
************************************************************************
*
*
* SUMMARY: New routine to update change logs for file triggers
*
* Params : This is the standard parameter list passed in by the trigger.
* TRIGGER.NAME
* SCHEMA
* TABLE
* EVENT
* TRIGGER.TIME
* NEW.RECORDID
* NEW.RECORD
* OLD.RECORDID
* OLD.RECORD
* ASSOCIATION
* ASSOCIATION.EVENT
* TRIGGER.COUNT
* CHAIN.CASCADE
* CASCADE
* DATE INIT REQ# DESCRIPTION
************************************************************************
*
************************************************************************
*
* File open routine.
CALL *FILE.OPEN("AUDIT.LOG",AUDIT.LOG)
CALL *FILE.OPEN("VOC",VOC)
CALL *FILE.OPEN("FILECTL",FILECTL)
DONE = 0
************************************************************************
*
* Begin Transactional processing
*
BEGIN TRANSACTION ISOLATION LEVEL 1
*
GOSUB INITIALIZE.INFORMATION
*
* Check to see if this is an Update or Delete. If so, we need to GOSUB
LOAD.INIT
* The LOAD.INIT will initialize a first record item.
*
IF EVENT = "UPDATE" OR EVENT = "DELETE" THEN
MATCH.VAR = NEW.RECORDID
IF OLD.RECORDID # "" THEN MATCH.VAR = OLD.RECORDID
MATCH.VAR = TABLE:"_":MATCH.VAR:"_"
BSCAN ID.VAR, REC.VAR FROM AUDIT.LOG, MATCH.VAR USING "IDX.FULL.ID" THEN
IF (FIELD(ID.VAR,"_",1) = FIELD(MATCH.VAR,"_",1)) AND (FIELD(ID.VAR,"_",2) =
FIELD(MATCH.VAR,"_",2)) ELSE
GOSUB LOAD.INIT
END
END ELSE
GOSUB LOAD.INIT
END
END
*
* If this is an Update or Insert we do comparisons of the OLD and NEW
record.
IF EVENT = "UPDATE" OR EVENT = "INSERT" THEN
DELIM = ""
ATT.REC = ""
VM.REC = ""
SVM.REC = ""
NEW.ITEM = ""
OLD.ITEM = ""
*
* Loop through the record that has to largest number of Attributes.
AM.NEW.CNT = DCOUNT(NEW.RECORD,@AM)
AM.OLD.CNT = DCOUNT(OLD.RECORD,@AM)
IF AM.NEW.CNT > AM.OLD.CNT THEN
TOTAL.AM.CNT = AM.NEW.CNT
END ELSE
TOTAL.AM.CNT = AM.OLD.CNT
END
FOR ATT.CNT = 1 TO TOTAL.AM.CNT
*
* Check to see of the attribute is the same. Set the flag to true if
* they are the same so we can skip the logging of that attribute.
IF OLD.RECORD<ATT.CNT> # NEW.RECORD<ATT.CNT> THEN
*
* There is a FILECTL file out there that tracks information of a file (the
key is the filename).
* In my FILECTL record, I have identified attribute 9 as the attributes that
I track
* and a flag in the associated attribute 12 identifying whether we are
tracking the
* changes in this attribute.
LOCATE ATT.CNT IN FILECTL.REC<9> BY "AR" SETTING POS THEN
IF FILECTL.REC<12,POS> = "Y" THEN
SKIP.ATT = 0
END ELSE
SKIP.ATT = 1
END
END ELSE
SKIP.ATT = 1
END
*
* If we are not skipping this attribute, loop through the Values in each
attribute.
* Again, we find the number of Values in the old record and new record and
loop through
* the larger of the 2.
IF NOT(SKIP.ATT) THEN
VM.NEW.CNT = DCOUNT(NEW.RECORD<ATT.CNT>,@VM)
VM.OLD.CNT = DCOUNT(OLD.RECORD<ATT.CNT>,@VM)
IF VM.NEW.CNT > VM.OLD.CNT THEN
TOTAL.VM.CNT = VM.NEW.CNT
END ELSE
TOTAL.VM.CNT = VM.OLD.CNT
END
FOR VM.CNT = 1 TO TOTAL.VM.CNT
IF OLD.RECORD<ATT.CNT,VM.CNT> # NEW.RECORD<ATT.CNT,VM.CNT> THEN
*
*If there is a change, we loop through the Sub-Values.
SVM.NEW.CNT = DCOUNT(NEW.RECORD<ATT.CNT,VM.CNT>,@SVM)
SVM.OLD.CNT = DCOUNT(OLD.RECORD<ATT.CNT,VM.CNT>,@SVM)
IF SVM.NEW.CNT > SVM.OLD.CNT THEN
TOTAL.SVM.CNT = SVM.NEW.CNT
END ELSE
TOTAL.SVM.CNT = SVM.OLD.CNT
END
FOR SVM.CNT = 1 TO TOTAL.SVM.CNT
IF OLD.RECORD<ATT.CNT,VM.CNT,SVM.CNT> # NEW.RECORD<ATT.CNT,VM.CNT,SVM.CNT>
THEN
*
* Keep track of the Attribute, Value, Sub-Value, and the New Record value.
* NOTE: We do not need to track the old value because we have already logged
it.
ATT.REC := DELIM:ATT.CNT
VM.REC := DELIM:VM.CNT
SVM.REC := DELIM:SVM.CNT
NEW.ITEM := DELIM:NEW.RECORD<ATT.CNT,VM.CNT,SVM.CNT>
DELIM = @VM
END
NEXT SVM.CNT
END
NEXT VM.CNT
END
END
NEXT ATT.CNT
*
* Go to the WRITE.UPDATE.LOG if there have been changes.
IF ATT.REC # "" THEN GOSUB WRITE.UPDATE.LOG
*
* If this record is deleted, we do not need any position information since
the
* record has been deleted.
END ELSE
ATT.REC = ""
VM.REC = ""
SVM.REC = ""
OLD.ITEM = ""
NEW.ITEM = ""
GOSUB WRITE.UPDATE.LOG
END
*
* Commit and end the transaction.
COMMIT
END TRANSACTION
RETURN
*
************************************************************************
* SUBROUTINES:
************************************************************************
*
*******************
WRITE.UPDATE.LOG : *
*******************
AUDIT.LOG.REC = ""
AUDIT.LOG.REC<1> = SYSTEM(32)
AUDIT.LOG.REC<2> = @PATH
AUDIT.LOG.REC<3> = @ACCOUNT
AUDIT.LOG.REC<4> = @USERNO
AUDIT.LOG.REC<5> = @WHO
AUDIT.LOG.REC<6> = @DATE
AUDIT.LOG.REC<7> = TIME()
AUDIT.LOG.REC<8> = @COMMAND
AUDIT.LOG.REC<9> = TABLE
AUDIT.LOG.REC<10> = NEW.RECORDID
AUDIT.LOG.REC<11> = ATT.REC
AUDIT.LOG.REC<12> = NEW.ITEM
AUDIT.LOG.REC<14> = EVENT
AUDIT.LOG.REC<15> = SCHEMA
AUDIT.LOG.REC<16> = TRIGGER.NAME
AUDIT.LOG.REC<17> = TRIGGER.TIME
AUDIT.LOG.REC<18> = OLD.RECORDID
AUDIT.LOG.REC<19> = VM.REC
AUDIT.LOG.REC<20> = SVM.REC
AUDIT.LOG.REC<21> = LOWER(SYSTEM(9001))
*
* This is a next sequencial ID tracking routine.
CALL *GET.NEXT.SEQ.NUM(AUDIT.LOG,"",NEXT.ID)
WRITE AUDIT.LOG.REC TO AUDIT.LOG,NEXT.ID
*
RETURN
**************
LOAD.INIT : **
**************
*
* This uses pretty much the same logic as above. This will initialize the
first record
* of the AUDIT.LOG. This was written so that you could implement at any
time.
ATT.REC = ""
VM.REC = ""
SVM.REC = ""
NEW.ITEM = ""
DELIM = ""
TOTAL.AM.CNT = DCOUNT(OLD.RECORD,@AM)
FOR ATT.CNT = 1 TO TOTAL.AM.CNT
IF OLD.RECORD<ATT.CNT> # "" THEN
*
LOCATE ATT.CNT IN FILECTL.REC<9> BY "AR" SETTING POS THEN
IF FILECTL.REC<12,POS> = "Y" THEN
SKIP.ATT = 0
END ELSE
SKIP.ATT = 1
END
END ELSE
SKIP.ATT = 1
END
*
IF NOT(SKIP.ATT) THEN
TOTAL.VM.CNT = DCOUNT(OLD.RECORD<ATT.CNT>,@VM)
FOR VM.CNT = 1 TO TOTAL.VM.CNT
IF OLD.RECORD<ATT.CNT,VM.CNT> # "" THEN
TOTAL.SVM.CNT = DCOUNT(OLD.RECORD<ATT.CNT,VM.CNT>,@SVM)
FOR SVM.CNT = 1 TO TOTAL.SVM.CNT
IF OLD.RECORD<ATT.CNT,VM.CNT,SVM.CNT> # "" THEN
ATT.REC := DELIM:ATT.CNT
VM.REC := DELIM:VM.CNT
SVM.REC := DELIM:SVM.CNT
NEW.ITEM := DELIM:OLD.RECORD<ATT.CNT,VM.CNT,SVM.CNT>
DELIM = @VM
END
NEXT SVM.CNT
END
NEXT VM.CNT
END
END
NEXT ATT.CNT
*
HOLD.EVENT = EVENT
EVENT = "INIT"
IF ATT.REC # "" THEN GOSUB WRITE.UPDATE.LOG
EVENT = HOLD.EVENT
RETURN
*************************
INITIALIZE.INFORMATION: *
*************************
*
* This is used to ensure we are using the correct file. The FILECTL records
* uses a compound key (not my decision) to identify the ACCOUNT*FILENAME.
* This GOSUB will make sure we are using the correct FILECTL record.
CONTINUE.FLAG = 0
*
LOOP
UNTIL DONE DO
READU VOC.REC FROM VOC, TABLE LOCKED
END THEN
IF TRIM(VOC.REC<1>)[1,1] = "F" THEN
CURR.ACCOUNT=OCONV(@WHO,'MCU')
FILECTL.ID = CURR.ACCOUNT:"*":TABLE
END ELSE
FILECTL.ID = VOC.REC<2>:"*":VOC.REC<3>
END
*
LOOP
UNTIL DONE DO
READU FILECTL.REC FROM FILECTL,FILECTL.ID LOCKED
END THEN
DONE = 1
CONTINUE.FLAG = 1
END ELSE
DONE =1
CONTINUE.FLAG = 0
END
REPEAT
END ELSE
DONE = 1
CONTINUE.FLAG = 0
END
REPEAT
*
RELEASE VOC,TABLE
RELEASE FILECTL,FILECTL.ID
*
RETURN
************************************************************************
* END OF TRIGGER.UPDATE.CHANGE.LOG
************************************************************************
'We act as though comfort and luxury were the chief requirements of life,
when all that we need to make us happy is something to be enthusiastic
about.'
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
