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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

This actually does appear to work...

But I'm wondering if it's a good idea or a bad one.

The "Stored Procedures, Triggers, and User-Defined Functions on DB2
Universal Database for iSeries" has the following:

"BEFORE
This specifies that the trigger is a BEFORE trigger. The database
manager executes the
triggered-action before it applies any changes caused by an insert,
delete, or update
operation on the subject table. It also specifies that the
triggered-action does not activate
other triggers because the triggered-action of a BEFORE trigger cannot
contain any updates."

So it would appear that being able to evoke a UDF that performs an
INSERT or UPDATE is basically lying to the system. :)

Since RPG BEFORE triggers don't have this caveat, would that be the
right way to do what I want to do?

(Elvis or Birgitta I'd love to hear what you think! ;)

Charles

On Thu, Mar 11, 2010 at 8:14 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
> Another thought....
>
> What if I place the INSERT/UPDATE of FILEB into a user defined
> function or stored procedure.
>
> Would that work?
>
> Charles
>
> On Thu, Mar 11, 2010 at 8:10 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
> > 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