By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

V5R2 DB2/UDB iSeries: can a system trigger call a SQL stored proc?

P: n/a
I've created a system or external trigger on an AS/400 file a.k.a DB2 table.
(Note this is an external trigger defined with the ADDPFTRG CL command, not
a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined
a SQL stored proc, and the trigger is set to call this SP. I've posted the
simplified source below. I can manually call the stored proc, and the
external trigger is created without any errors. However, when I do an
insert into the table in question, the trigger returns an error (posted
below). I believe my problem is either 1) I'm not accepting the trigger
buffer contents correctly in the stored proc, or 2) you simply can't have an
external trigger call a SQL stored proc. (I don't believe #2 is the
problem, based on the docs, but I could be wrong.) I believe #1 is the
problem, but I can't find any example in the docs or any redbooks that show
how to have a system trigger call a SQL stored proc. What I'm actually
after is the RRN that is passed in inside the trigger buffer...that's the
piece I really need to get!

If #1 is the problem, please point me to an example, or make any suggestions
that come to mind. If #2 is the problem, then I'd imagine that I can wrap
the SQL stored proc in a CL program or RPG stub. Essentially, the trigger
would call the CL or RPG program, which would call the SQL stored proc.

I'm posting the simplified source below. If anyone can advise me or point
me to example, I would appreciate it very much!

Thx in advance,
Dave

Tables:
CREATE TABLE emp
(empnum DECIMAL(10,0),
empname VARCHAR(50));

ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empnum);
CREATE TABLE trigbuf (trigbuf VARCHAR(8000), buflength varchar(10));

SQL stored procedure:

CREATE PROCEDURE updchgq2
(IN in_triggerbuffer VARCHAR(8000),
IN in_length VARCHAR(10))
LANGUAGE SQL MODIFIES SQL DATA
BEGIN
INSERT INTO trigbuf values(in_triggerbuffer, in_length);
END

I modeled the input parameters that the stored proc expects roughly on the e
xample of a CL trigger I found in the IBM redbook on iSeries Stored Procs,
UDF's, and Triggers.

External trigger:

ADDPFTRG FILE(EMP) TRGTIME(*AFTER) TRGEVENT(*INSERT) PGM(UPDCHGQ2)
insert into emp values(1,'Test') returns this error message:

CEE9901 - Application error. MCH3601 unmonitored by UPDCHGQ2 at statement
0000000007, instruction X'0000'.

SQL stored procs get compiled into ILE C program objects on the iSeries, so
I'm not sure how relevant this error message is.
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.