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

I'll test it...but I was under the impression that that would not work.

Maybe it's different with SQL given the restrictions on the BEFORE
INSERT trigger.

But I'm pretty sure with a RPG AFTER INSERT trigger, trying to chain
to the row that caused the trigger to fire doesn't work.

Charles

On Thu, Mar 11, 2010 at 11:13 AM, Dan Kimmel <dkimmel@xxxxxxxxxxxxxxx> wrote:

Sure you can. With an UPDATE statement using a where clause with values from the inserted row.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, March 11, 2010 9:52 AM
To: Midrange Systems Technical Discussion
Subject: Re: Before Insert SQL Trigger can't use INSERT or UPDATE statements?

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.

--
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.