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.
Well, stick your procedure into a .i file and include it in each of the trigger procedures you want. replace <TABLE-NAME> with {&tableName}, and include this .i like this:
{ genericTrigger.i &tableName = "<table-name>" }
You can generate these one line trigger-files by looping through the _File table, looking for all records where _hidden = FALSE...
BTW: Of course you know that it is not a good idea to have any user-interaction in a trigger (MESSAGE); I am assuming you are just using the message statement for the moment, until your proof of concept is working... right?
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.
Write a code generator that generates the code for you, by reading through the tables in your database. Some example pseudo-code (not syntax checked);
for each _file:
output to value (_file.-file-name + "trigger.p").
put unformatted
"trigger procedure for write of " + _file.file-name + " old buffer beforetablebuffer" skip.
/* rest of the required code */
output close.
end.
If you want to do a generic procedure, it's to my mind much better to have a single .p and buffer handles.
/* genericTrigger.p */
define input parameter oldBuffer as handle no-undo.
define input parameter newBuffer as handle no-undo.
if not newBuffer:buffer-compare(oldBuffer) then
message "Table: " + oldBuffer:name.
That won't save you from the trigger procedure itself, but the business logic is in a single location, and not duplicated in every rcode.
/* Table trigger */
trigger procedure for write of <tableName> new buffer bNew old buffer bOld.
run genericTrigger.p (buffer bOld:handle, buffer bNew:handle).
Agree with Gilles (Riverside Software). I'd just use a persistent procedure instead of an external .p
Or a static class.
Many of us have been around for so long we forget the modern capabilities and fall back on bad old patterns. Parameterized includes were the ruin of many very very early Progress packages. Generating code makes for hundreds of programs none of which are reusable. Although I would clearly suggest an OO class based on a buffer handle if programming was best, why program this? It's exactly what auditing is. Just turn that on and you're done without having to program anything.
Yeap, sticking the logic into a .p and calling the .p persistently from the trigger-procedure would be how I would deal with this, too - especially, if all your code-base is procedural and your programmers are not familiar with OOABL. Many of us are so excited about new functionality, that they forget that the end result has to be maintainable by the folks who are regularly working the code...
Not knowing what logic would be in the "if changed" THEN block, I suggested .i - If you're referencing specific fields you'll be better off with .i, rather than trying to make everything dynamic - just my preference...
If you use the persistent procedure route, it is of course important not to forget to include some logic that checks if the procedure is already running, and if so just use this instance, rather than start a new one. Otherwise you won't get the performance benefit of not having to startup the .p every time the trigger is executed. If the .p is getting larger, this is a significant improvement - in my experience...
Generating code is sometimes the best path - as long as it is done with brains. I generate the whole application - the specific stuff, that is. All the logic that can be extracted is extracted, thus not generated but reused. And all the customizable stuff is separated out as well, so you don't loose your manually done work when you have to regenerate... Whether you generate the trigger-files or type them manually, you'll end up with two files per table...
Of course, if auditing is fulfilling your purpose, and if it is not an extra cost (as so much in Progress is), this might be the easiest route - definitely worth looking into...
... SAVE RESULT IN V_LOGICAL ...
If you are doing buffer-compare inside a write trigger, the result always will be FALSE , is'nt?
From discussions elsewhere the OP is trying to create a Warehousing solution. I have recommended talking to Progress about Pro2SQL as an alternative to rolling his own.
Marco's note is very true, of course - as long as you don't exclude fields from the buffer-compare. As written above, no buffer-compare is needed.
Also, I would advise to make the assign of the three fields following the buffer-copy part of the buffer-copy; and I always recommend to always prefix any field with the table-name. That way, if somebody at some other time adds a variable with the same name, the assign will still assign the value to the record-field, rather than the variable. (Happened before, took three days to find that bug!)
Have you considered auditing ... no need to reinvent the wheel.
If you are considering writing up an implementation of this, you might want to get involved in the 11.7 ESAP program. This will allow you to preview the Change Data Capture (CDC) feature planned for 11.7. The intention is to be "lighter" than Auditing. I can't commit to dates, but we hope to have an ESAP drop for CDC soon. We'd welcome feedback in respect to how it meets your needs.
How does one sign up Garry? My old employer has a warehouse solution that currently extracts all pertinent changes from the last 6 years every single night and this would be a real boost for them.
See this announcement: community.progress.com/.../23816
Mike has the correct information. It is good to hear CDC is of interest to others. The more feedback we can get from the ESAP, the better the feature can be.