How to assign timestamp to current time/SYSTIMESTAMP in proc

Hi Tom,

I'm not all too familiar with SQL but I think you're trying to mix SQL and ABL.

Try this:

Assign tableA.timestampA = NOW.

Thank you for your reply. You are absolutely correct! The NOW syntax is what I was looking for. I do have a follow up problem however. Now that I have saved the procedure trigger in data dictionary, I got no syntax errors and procedure seems to have been saved fine.

The problem now is when I run an update of the table, tableA, my timestamp remains unchanged. Anyone else have any suggestions on how I can verify that I have my procedure trigger setup correctly for execution on an update? Thanks again.

And if you are mixing SQL & ABL remember the trigger you have created above will NOT fire on inserts done from a non-ABL client (ODBC/JDBC access etc).

If data is inserted via both SQL and ABL you will need another java equiv trigger to perform the same thing.

Do you have an example of the code you are using?

(will let us know if you are using ABL or SQL). The trigger will not fire until the newly created record is written to the database - so if you test the value straight after assigning another field in the same record the datetime will not have altered - the trigger will only fire (and thus change your timestamp) once the record is written (at transaction or record scope - or validation).

I guess being so new to OpenEdge in general is causing me great pains.... I was under the impression that this procedure trigger would fire whenever a SQL insert/update was done to the table (connecting using JDBC). But from what it sounds like, I am incorrect. What I am trying to do is simple in other databases, but I cannot get openedge to cooperate one bit. Here's a brief explanation.

What I need is to determine when a record has changed. I am piggybacking on an old app using a progress db that doesn't update timestamps when records are changed. So I want a trigger to specifically update a timestamp when any field other than the timestamp itself is updated. So doing a standard trigger seems to be the answer however, when I create a trigger like below, I get an error that I cannot get by (notice that the trigger is intentionally left blank so as not to cause the error I get)

CREATE TRIGGER triggerA AFTER UPDATE OF "City"

ON tableA

REFERENCING NEWROW

FOR EACH ROW

BEGIN

String idCode = " 2";

END;

update tableA set "City" = 'Test' where idcode = '1';

Produces the error below(in both eval 10.1c and full version 10.1a):

Internal error -2 (a column with the specified column id does not exist in the row) in SQL from subsystem RECORD SERVICES function rec_to_data called from stub_tpl_hdl_t:extract_fields on PUB.Profile for Sal. Save log for Progress technical support.

I would expect a successful update from this trigger when I update City because the trigger doesn't even do anything at this point. Any advice is appreciated. Thanks.

The key here is that there are actually two sets of triggers in an OpenEdge database (not the way I would have done it, but no one asked me). One set are written in ABL and are invoked when running ABL procedures and one set are written in Java and are invoked when accessing via SQL. Ne'er the twain shall meet.

Hi Tom,

The database supports two types of triggers, one based on an older 4gl code and the other based on odbc/jdbc access . Neither type will see the other - a real pain and something that should not have happened in the first place. This link may help descibe this legacy support position https://progress.atgnow.com/esprogress/docs/Progress/esupport.progress.com/EDocHTML/OpenEdge/10.1B/dmsdv/dmsdv-09-1.html (scroll down to "Working with triggers").

If your "old app" was written using 4GL code you will need the ABL triggers, if the new/old app access the data through an ODBC/JDBC connection you will need SQL triggers. If both forms of access is possible then you will need two complete set of triggers - the pain gets worse as one will be written in Java the other in the 4GL itself.

I can see from other recent postings of yours that you have not got too far with this gem, but from the error messages shown it wuld appear that you are doing the "update" via an odbc/jdbc connection. Are you sure that you;

a) Have a table in the database named "tableA", and

b) Have fields in this table named "City" and "idcode" (and both are character types), and

c) that your current connection has been granted access to both the table and these fields?

There is/was a bug where the sql server would not see schema changes. It was meant to be fixed in the versions you mentioned but it does still raise its head. Have you restarted your database recently ?

Thanks for everyone's reply. I definitely have a table, tableA with character types City and idcode, and I was logged in as both a dba and as sysprogress with the same result. The trigger will add and commit successfully but throw the error when I attempt to update.

In trying to find a solution, I was just trying both types of triggers, not knowing that each functioned separately (thanks for clarifying). But after your posts, I investigated and it appears that their old application is coded using 4gl, however, we need to connect through jdbc/odbc. So, it seems that if I use the 4gL trigger below:

TRIGGER PROCEDURE FOR WRITE OF tableA.

Assign tableA.changed_timestamp = NOW.

Then the 4GL app would fire this trigger update the timestamp and I could still use my jdbc app to interface and manipulate data in the database. Does this seem logical? I would test right now, but their app is not letting me update due to some missing procedures on this dev server, so I must get that squared away first. Thanks.

Should work - 4GL appp will fire these triggers as long as the compiled 4GL trigger code is on the 4GL apps path (called PROPATH in the 4GL). The 4GL triggers are compiled as '.r' files and not stored in the database (unlike SQL triggers).

Only other point, assume "jdbc app to interface and manipulate data" means SELECTs only, no UPDATE/INSERT statements ... as these will not fir the 4GL trigger .

Never understood the reason why the two sets of triggers could not have interchangable within OE10 - it is in other DB vendors, not that there are many who support a native language - but MSSql support allow CLR and "traditional" triggers to co-exist.

I do plan to do updates/inserts but don't need those triggers to fire in those cases, so I believe I have found a solution. I totally agree that OE needs to refine it's approach to triggers. My development time for this project is possibly more than 4 times what it would be on another database!

Thanks for your input.