Help Required To Write A Generic Trigger Program

[NThomas]

Hi All,

Could you please help me to write an generic trigger program, where it can be attached to any table as the operation it is going to perform is the same.

Is this can be achieved using any system tables?

Below is the sample trigger program.

The table name should be able arrived based on the table to which the trigger is attached.

TRIGGER PROCEDURE FOR WRITE OF <TABLE-NAME> OLD BUFFER OLD_TABLE_NAME.
DEF VAR V_LOGICAL AS LOGICAL NO-UNDO.

BUFFER-COMPARE TABLE_NAMETO OLD_TABLE_NAME SAVE RESULT IN V_LOGICAL.

IF NOT V_LOGICAL THEN
DO:
Message <table-name>
END.

 

[RealHeavyDude]

Database triggers are not generic - they are static and resolved at compile time.

You don't give any hint - apart from your code - but to me it looks like you are trying to build some generic audit trail. Do you know that the database engine has a built-in policy-based audit feature?

I would strongly advise you not to use database triggers in any fashion. There are a lot of reasons why not. For one, and most important, they are not transparent to your business logic and, secondly, almost equally worse - depending on your point of view - they can become an error handling nightmare causing transactions to fail silently without a hint what is going on.

Nevertheless, if you really, really, really must use them, then your best option would be to pass the handle to the buffer and and before image buffer to an external procedure ( something like this - coded in Firefox ... ):

trigger procedure for write of YourTable
  new buffer TableBuffer
  old buffer BeforeTableBuffer.

run yourAuditProcedure (
  input 'write',
  input buffer TableBuffer:handle,
  input buffer BeforeTableBuffer:handle ).

And you could write ABL code that would automtically generate those triggers.

Heavy Regards, RealHeavyDude.

 

[NThomas]

Thanks for the reply, my purpose of this is to copy the incremental data or the changed data in the source table to a staging table with some additional field
to identify the update time stamp.

I will use this staging table to populate the NON-Progress Datawarehouse with daily incremental data extracted through ETL.


I am avoiding the search on the staging table considering the performance impact on the transaction and all the write operation in source table is simply copied to the staging table.

The actual code will look like this.



TRIGGER PROCEDURE FOR WRITE OF <source table> OLD BUFFER BeforeTableBuffer.
DEF VAR V_LOGICAL AS LOGICAL NO-UNDO.

BUFFER-COMPARE <source table> TO BeforeTableBuffer SAVE RESULT IN V_LOGICAL.

IF NOT V_LOGICAL THEN
DO:
CREATE <STAGING TABLE>.
BUFFER-COPY <source table> TO <STAGING TABLE>.
ASSIGN LAST_UPDT_DATE = TODAY
LAST_UPDT_TIME = STRING(TIME,"HH:MM:SS")
UPDT_FLAG = 'W'.

END.


Housekeeping Job will be introduced to purge the data in this table.


There is multiple tables data has to be done like this, hence I was looking for an generic procedure which I can pass the table name and use the same code.

 

[RealHeavyDude]

You need to use dynamic buffer objects in order to use the table name derived from a variable. You can't do this with static references to buffers ( which is what your code is ). Static references are resolved at compile time while dynamic buffers are resolved at runtime.

Have a look on dynamic buffer objects.

Something similar like this could be a starting point for your logic:

define variable beforeBuffer  as handle  no-undo.
define variable afterBuffer  as handle  no-undo.
define variable stagingBuffer  as handle  no-undo.
define variable changed  as logical  no-undo.

create buffer beforeBuffer for table 'BeforeSoureTable'.
create buffer afterBuffer for table 'SourceTable'.
create buffer stagingBuffer for table 'StagingBuffer'.

assign changed = afterBuffer:buffer-compare ( beforeBuffer, 'binary' ).
if changed then do transaction:

  stagingBuffer:buffer-create ( ).
  stagingBuffer:buffer-copy ( afterBuffer ).
  assign stagingBuffer::updateTimeStamp = now
  stagingBuffer::updateType  = 'write'.
  
  stagingBuffer:buffer-release ( ).

end.

/* Very important - clean up when you're done  */
delete object beforeBuffer.
delete object afterBuffer.
delete object stagingBuffer.

Heavy Regards, RealHeavyDude.

 

[Cringer]

Before you get too far down this route have a call with Progress about Pro2SQL. It's an application that writes data changes to a SQL (or Progress) replica in near real time.

If you do go down this route of rolling your own, have a look at the transaction trigger rather than the write trigger as this only fires after a transaction successfully completes AFAIK.

 

[Marco Mendoza]

And remember,
If you are doing buffer-compare inside a write trigger, the result always will be FALSE , is'nt?