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



Ok...I should have known...

The system let me create the trigger with a call into the UDF...but at
run time I get an error.

SQL0577 - Modifying SQL data not permitted.
Cause . . . . . : One of the following errors has occurred:
-- A procedure was called or a function was invoked that was created with
READS SQL DATA or CONTAINS SQL DATA specified as the data access attribute.
A procedure or function created with READS SQL DATA or CONTAINS SQL DATA, or
any procedure or function that is called by the procedure or function,
cannot change data and cannot call a procedure or function that has the
MODIFIES SQL DATA attribute.


Charles

On Thu, Mar 11, 2010 at 9:35 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
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




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.