Kbase P102261: 4GL/ABL: How to monitor field changes in a database table using its WRITE schema trigger?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/29/2008 |
|
Status: Verified
GOAL:
4GL/ABL: How to monitor field changes in a database table using its WRITE schema trigger?
GOAL:
How can the WRITE schema trigger be used to audit database changes?
FACT(s) (Environment):
All Supported Operating Systems
Progress 8.x
Progress 9.x
OpenEdge 10.x
FIX:
The following sample WRITE schema trigger creates a new record in an auditing database table called 'Monitor'. Each record logs the table name, field name, old field value, new field value and other information of interest:
TRIGGER PROCEDURE FOR WRITE OF testbf OLD BUFFER oldtestbf.
DEFINE VARIABLE hOldRecord AS HANDLE NO-UNDO.
DEFINE VARIABLE hNewRecord AS HANDLE NO-UNDO.
DEFINE VARIABLE hOldField AS HANDLE NO-UNDO.
DEFINE VARIABLE hNewField AS HANDLE NO-UNDO.
DEFINE VARIABLE cChangedFields AS CHARACTER NO-UNDO.
DEFINE VARIABLE iChangedFields AS INTEGER NO-UNDO.
BUFFER-COMPARE testbf TO oldtestbf SAVE RESULT IN cChangedFields NO-ERROR.
IF cChangedFields <> "" THEN DO:
ASSIGN
hOldRecord = BUFFER testbf:HANDLE
hNewRecord = BUFFER oldtestbf:HANDLE.
DO iChangedFields = 1 TO NUM-ENTRIES(cChangedFields):
CREATE Monitor.
ASSIGN
Monitor.rOldRecid = RECID(oldtestbf)
Monitor.rNewRecId = RECID(testbf)
Monitor.cUserId = USERID("DICTDB")
Monitor.cDate = TODAY
Monitor.cTime = STRING(TIME,"HH:MM:SS")
Monitor.cTableName = hOldRecord:NAME
Monitor.cFieldName = ENTRY(iChangedFields, cChangedFields)
hOldField = hOldRecord:BUFFER-FIELD(ENTRY(iChangedFields, cChangedFields))
Monitor.cOldStringValue = hOldField:STRING-VALUE
hNewField = hNewRecord:BUFFER-FIELD(ENTRY(iChangedFields, cChangedFields))
Monitor.cNewStringValue = hNewField:STRING-VALUE.
END.
END.