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

["Leif Guldbrand" <Guldbrand@xxxxxxxxxxx>]

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