RE: SQLCODE -907

[Pete Hall <pbhall@xxxxxxxxxxxxx>]

At 10:57 03/13/2002, Tom Hightower wrote:
>Not a red herring, but doesn't really help either.  The trigger time is
>*AFTER, trigger event is *INSERT.  We use SQL to 'INSERT INTO' the file,
>which causes the trigger to run, which in turn runs the program, which
>updates a field in the row we're inserting.
>
>On the SQL 'INSERT INTO' - if we use 'WITH CS' (isolation level: row is
>locked until end of current transaction) at the end of it, we get the
>SQLCODE -907.  If we leave the 'WITH CS' off, we get no error and the
>files are updated like we want but a ROLLBACK does nothing.
>
>What we want is to set an isolation level and not get the -907, and be
>able to do a ROLLBACK.  How do we accomplish that?  Do I need to post the
>C program to show how the files are being updated?

Tom,

The way I've done that is to set the trigger time to *BEFORE, and set
ALWREPCHG(*YES) in the trigger definition. The trigger program can then
update the new image trigger buffer and the updated value will be applied
to the database. The trigger must not commit or rollback, and must be
compiled with COMMIT(*NONE). Any database changes made by the trigger
(including SQL updates and inserts) will then be within the commit boundary
of the application (the SQL that does the insert).


Pete Hall
pbhall@ameritech.net