trigger

[Sonja]

Hello,

i have never worked with triggers for Progress Verison 9 and i have to do following:
Whenever a datafield of a table (ro_det) changes i want to have following information:

1. what was changed (contents of datafiled vefore change + contents of datafiled after change)

2. when was the change done (date + time)

3. who did the change (userid)

Now i want to create a 'shell' program that defines and implements a session trigger. Program should use the "on write of ro_det.... " option, something like

ON WRITE OF ro_det OLD BUFFER bu-ro_det DO:
{any actions }
END.
{gprun.i ""rwromt.p""}

Is there anybody who already has worked on such a problem - and who could give me a program coding ?

I appreciate any help.

Thanks
Sonja

 

[Lee Curzon]

There is a commercial library here which may do some of what you ask.

I've never tried it, but it looks interesting, particularly because it is very cheap.

http://www.findfirstconsultant.com/broker.html

FYI: OE 10 does all this as you require.

Trivia: the author of Data Broker wrote the 'Transaction Leak' whitepaper on the Peg.

 

[Lee Curzon]

I read the post too quickly, thought you were talking about general auditing, rather than one table in particular.

You should be able to use BUFFER-COMPARE etc. 4GL statements, and you'll need a table to record the activity. If you use SmartObjects, you can override write procedures.

For normal 4GL, there are a few examples in the knowledgebase, eg.

P1986
Title: "Is it possible to audit database activity from the 4GL?"
http://tinyurl.com/pso2f

P18098
"How to implement an AUDIT within the Progress DB and 4GL?"

/* The following lines should be put in the DB trigger for CREATE,
UPDATE or DELETE */
DO WHILE PROGRAM-NAME(w_n) <> ?:
ASSIGN w_n = w_n + 1.
END.
ASSIGN w_n = w_n - 1./* This will be the trigger for Create */


CREATE LOG.
/*
In the following code replace 'Table_Name' with the actual table name
being Audited.
*/
ASSIGN LOG.TableName = "Table_Name"
LOG.TableKey = STRING(Table_Name, Index Name)
LOG.operation = /*Will depend in the trigger you are C -
Creation,
U - Update, D - Delete*/
LOG.ldate = TODAY
LOG.ltime = STRING(TIME, "HH:MM:SS")
LOG.origin = PROGRAM-NAME(w_n)
LOG._userid = USERID.
RAW-TRANSFER Table_Name TO Log.field_raw.
END.
If you need to recover any record:

RAW-TRANSFER Log.Field_raw TO Table_Name.

HTH

 

[jingaustria]

RE: Triggers

Here's an example: You will understand how things works based on your requirements

/*----------------------------------------------------------------
Trigger Name : ptw.t
----------------------------------------------------------------*/
TRIGGER PROCEDURE FOR WRITE OF PT_MSTR OLD BUFFER OLD_PT_MSTR.

{mfdeclre.i }

def var lch-userid as character.
def var lda-date as date.
def var lch-time as character.

def var lch-oldval like pt_mstr.pt_part.
def var lch-newval like pt_mstr.pt_part.

do:

assign lch-oldval = old_pt_mstr.pt_part /* old value */
lch-newval = pt_mstr.pt_part. /* new value */


assign lda-date = today /* date */
lch-time = string(time,"hh:mm:ss"). /* time */

/* for you to identify who did the change */
lch-userid = global_userid.

end.