Re: Add records to a file and fire a trigger ...

Thanks Raul,

so your table will have the proper value, but
this RecID will not be returned to your program.

I dont need it if just the table gets the new RecID (last RecID +1),
and right now I focus on the SQL trigger and not triggers with 'external'
programs.

Yes I can recreate the file - but the file has not been created with
SQL but with DDS, and I cant find any keywords for 'auto increment'
within DDS.

Have been playing (fooling :-) around with with 'Create Trigger' here today !!!

Btw: what do I do, if I create this SQL trigger and it works as 'designed',
but not as expected by me (gosh... haven't I heard this one before :-) ???
Do I just create a new one with the same name or..... ??????

Regards,
Leif


----- Original Message -----
From: "Raul A. Jager W." <raul@xxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: 6. oktober 2007 15:52
Subject: Re: Add records to a file and fire a trigger ...

A trigger can set the next value for RecID and put it in the buffer
before it gets written, so your table will have the proper value, but
this RecID will not be returned to your program.
If you have some other key, you can read the file and get the RecID number.

If you can re-create the file you can define RecID as auto increment.

Leif Guldbrand wrote:

Does someone have a brilliant suggestion to this scenario ???

Have a file (DB2/400) which receives records from programs in an ERP system,
but also from other servers in the neighborhood using ODBC.

The file has a keyfield (RecID - 9,0). This keyfield should ofcause be unique, but
when I receive records into the file via ODBC I have no control and right now I only
receive *zeros in the field (RecID).

Then I thought... create a trigger program (RPG or SQL) and fire it *BEFORE an
ADD to the file. Get the next number (last used RecID + 1) and return this value
and initialize RecID with the new number !!!!

But I'm not sure this can be done.... seem to remember a trigger program cannot
return any parameters ??? !!!

Any comments or ideas ????
Btw: I haven't worked with triggers before ;-)

Regards,
Leif