Triggers / Data Dictionary / Copy fields

[bugg_tb]

Hi Guys,

I have very little knowledge of 4GL(Progress 9.1D) and usually use SQL to rip the data I need from the tables, but I need to implement a trigger on a table that copies a few fields from one table to another whenever a record is inserted and I was hoping someone could help.

Ive created a new table using the data dictionary and copied the relevant field names across.

Old Table: A2_RECODE
New Table: A2_RECSTORE

Fields: old_code, new_code, date.

I've found the screen where you can add the trigger to A2_RECODE and what I need it to do is insert the latest record's fields from A2_RECODE to A2_RECSTORE whenever a record is added to the old table. There appears to be a Unique ID column called SEQ_NUM if that is of any use.

Any help appreciated

Cheers

Tom

 

[TomBascom]

Something like this might be what you want...

/* trig/wr/customer.p
 */

ON WRITE OF customer NEW new-cust OLD old-cust DO:

   find x_table exclusive-lock where x_table.custNum = new-cust.custNum no-error.
   if not available x_table then create x_table.
   assign
    x_table.custNum = new-cust.custNum
    x_table.name     = new-cust.name
  .

END.

It's a WRITE trigger so it won't just fire for newly created records -- it'll also keep the fields consistent as they change. That may, or may not, be what you want. (I'm deliberately ignoring the old-cust buffer but if you wanted to get fancy you could check that before doing anything to avoid pointless updates...)

 

[bugg_tb]

Thanks for the reply,

I've actually managed to come up with a quick bit of code but I'm still struggling. I want my trigger to copy the data then forget about the new one so when the old records are deleted (the following night)it will leave the new copies(I'm not sure if I understand you correctly with the write trigger). Also looking at the code I don't have a unique ID that stands out, is it possible for 4GL to workout which record was just inserted/written to the table or am I going to have to come up with a load of checks to uniqueness?

Cheers

Tom

Edit: Oh I think I understand the trigger, you mean if a field is updated then another action is triggered?

 

[TomBascom]

Ok, that's important detail... And, yes, that's what I mean.

In the sample code I'm using the world famous "sports2000" database where the unique id for customer records is custNum.

I'm not sure what you mean by "forget about the new one"? Do you mean that you only want the "original" values preserved and no updates after that? If so then I suspect that you may need to either compare the new & old buffers to decide if this is an update or not or you might need to add some sort of flag field.

As for the uniqueness bit... the trigger is passed 2 buffers. There are two views of one record being dealt with by the trigger. One is the "new" copy of the record that is about to be written to the database. The other is the "old" copy -- prior to the changes reflected in the new copy that are about to be written. If the record has a unique index and your shadow table has the same structure then copying the record should be all that you need to do. BTW the 4gl BUFFER-COPY statement might be very helpful to you.

 

[bugg_tb]

Sorry by forget it I mean it not going to update the new record if the old one is edited? This parent table is deleted at the end of the day and obviously I don't want all the old new records disappearing, I'm just trying to keep a log of every product code entered into this table.

Anyway I've come up with some code(that doesn't compile yet) but I was hoping you'd tell me if I'm heading the right direction?

DEF VAR qbf-count AS INT.
DEF VAR qbf-loop  AS INT.
DEF VAR qbf-time  AS INT.

/* Buffer Definitions - program 1 */
DEF BUFFER A2_RECODE FOR SYDB.A2_RECODE.
DEF BUFFER A2_test FOR SYDB.A2_test.

ASSIGN
  qbf-count    = 0
  qbf-time     = TIME.


/* PROGRAM 1 */
/* Branches */


main-loop:
FOR EACH SYDB.A2_RECODE NO-LOCK
  WHERE (SYDB.A2_RECODE.date_recoded <> SYDB.A2_test.date_recoded 
  AND SYDB.A2_RECODE.new_code <> SYDB.A2_test.new_code):

  qbf-count  = qbf-count + 1.
  PUT CONTROL
    '~"' SYDB.A2_RECODE.old_code '~"'
    "~,"
    '~"' SYDB.A2_RECODE.new_code '~"'
    "~,"
    '~"' SYDB.A2_RECODE.date_recoded '~"'
    CHR(13) CHR(10).

buffer-copy A2_RECODE to A2_test.

END.

QUIT.

The above is a hacked version of a colleagues script, but hes not here to quiz

Anyway thanks a lot

Tom