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



Luis,

I could do that, or I could simply do the trigger as an RPG program.
Thought it'd need to be SQLRPGLE in order to use the
Identity_value_local() function.

Charles

On Thu, Mar 11, 2010 at 8:47 AM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:
Charles,

Just a wild guess but, What happens if, inside your SQL trigger, you call a
RPG program with the required writes/updates?

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Thu, Mar 11, 2010 at 8:40 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
--
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 ...

Follow-Ups:
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.