× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




It probably has to do with implied commit points. The SQL is working within the commit. The RPG trigger using RLA is working with a pre-commit ODP.

One program inserts a record into a table. The BEFORE INSERT trigger is called, the record is inserted, then the AFTER INSERT is called, then the transaction is auto-committed. SQL in the AFTER INSERT is working with the uncommitted record in the table. If the AFTER INSERT calls an RPG program that uses RLA, the database engine has to protect that commit boundary from RLA so it gives the RPG program a pre-commit version of the ODP -- the record is not there.

Pure speculation on my part. I have no idea how to test it.

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

Dan,

Ok I learned something new, that does seem to work.

I wonder why the differences in functionality between SQL and RPG triggers....

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.


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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.