Re: Before Insert SQL Trigger can't use INSERT or UPDATE statements?

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You can't update the inserted row on a AFTER trigger.

Charles

On Thu, Mar 11, 2010 at 9:58 AM, Dan Kimmel <dkimmel@xxxxxxxxxxxxxxx> wrote:
> Why not do it on an AFTER trigger where you can do all that stuff
> including updating the inserted row with the new id value?
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
> Sent: Thursday, March 11, 2010 7:11 AM
> To: Midrange Systems Technical Discussion
> Subject: Before Insert SQL Trigger can't use INSERT or UPDATE
> statements?
>
> All,
>
> I was putting together a Before Insert SQL Trigger and was suprised to
> learn that it can't use the INSERT or UPDATE statements...you get an
> SQL0751 error:
>
>  -- Statements not allowed in an SQL BEFORE trigger are INSERT, UPDATE,
> DELETE, ALTER TABLE, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, RENAME,
> REVOKE, REFRESH TABLE, and ALTER PROCEDURE.
>
> What I was trying to do (error handling omitted, also note FILEB has an
> identity column FILEB_ID)
>
> create trigger FILEA_BEFORE_INSERT
>  before insert on FILEA
>  referencing NEW ROW as new_row
>  for each row mode DB2ROW
>
> begin
>  if new_row.fileb_id = 0 then
>    insert into FILEB (fld1, fl2, fld3)
>        values (new_row.fld1, new_row.fld2, new_row.fld3);
>    new_row.fileb_id = indentity_val_local();
>  else
>    update  FILEB
>     set (fld1, fld2, fld3) =  (new_row.fld1, new_row.fld2,
> new_row.fld3)
>    where FILEB_ID = new_row.FILEB_ID;
>  end if;
> end
>
> I guess I'm going to have to use RPG for the trigger?
>
> Charles
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> moment to review the archives at http://archive.midrange.com/midrange-l.
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.