SQL Trigger will not Create

I'm trying to call a stored procedure from an SQL trigger. I created an RPGLE program. I then created a stored procedure over the RPGLE program.

Here is the code to the RPGLE program:

CTL-OPT OPTION(*SRCSTMT:*NODEBUGIO) DFTACTGRP(*NO)
BNDDIR('SRVDLL') ALWNULL(*USRCTL) ACTGRP('QILE');

DCL-S PS_MsgQ CHAR(20) DIM(1);

DCL-S PS_MsgId CHAR(7) INZ('CPF9898');
DCL-S PS_Msgf_Lib CHAR(20) INZ('QCPFMSG *LIBL ');
DCL-S PS_MsgData CHAR(100) INZ(*BLANKS);
DCL-S PS_MsgDataLen INT(10) INZ(*ZEROS);
DCL-S PS_MsgType CHAR(10) INZ('*INFO ');
DCL-S PS_NbrMsgQ INT(10) INZ(%ELEM(PS_MsgQ));
DCL-S PS_MsgQRply CHAR(20) INZ('*PGMQ ');
DCL-S PS_MsgKey CHAR(4) INZ(*BLANKS);

DCL-S V_Action CHAR(10) INZ(*BLANKS);

DCL-S V_PDFName CHAR(54) INZ(*BLANKS);


// API Error Structure

DCL-DS DS_Error;
DSE_BytesPrv INT(10) INZ(%SIZE(DS_Error));
DSE_BytesAvl INT(10) INZ(*ZERO);
DSE_MsgID CHAR(7);
DSE_Reserved CHAR(1);
DSE_MsgData CHAR(256);
END-DS;

/COPY QRPGLESRC,PGM_SDS

DCL-PR QCMD EXTPGM('QCMDEXC');
PARMCMD CHAR(500) CONST;
PARMLEN PACKED(15 : 5) CONST;
END-PR QCMD;

// Send non-program Message

DCL-PR QMHSNDM EXTPGM('QMHSNDM');
MsgID CHAR(7) CONST;
QualMsgF CHAR(20) CONST;
MsgData CHAR(32767) CONST OPTIONS(*VARSIZE);
MsgDataLen INT(10) CONST;
MsgType CHAR(10) CONST;
MsgQueues CHAR(20) CONST DIM(50) OPTIONS(*VARSIZE);
NumQueues INT(10) CONST;
RpyQueue CHAR(20) CONST;
MsgKey CHAR(4);
ErrorCode CHAR(8000) OPTIONS(*VARSIZE);
CCSID INT(10) CONST OPTIONS(*NOPASS);
END-PR;

DCL-C C_Low CONST('abcdefghijklmnopqrstuvwxyz');
DCL-C C_High CONST('ABCDEFGHIJKLMNOPQRSTUVWXYZ');

DCL-PR SNDSYNCMGQ;
PR_TktType CHAR(10);
PR_PDFName CHAR(50);
PR_BfrSigned CHAR(1);
PR_AftSigned CHAR(1);
END-PR SNDSYNCMGQ;

DCL-PI SNDSYNCMGQ;
PR_TktType CHAR(10);
PR_PDFName CHAR(50);
PR_BfrSigned CHAR(1);
PR_AftSigned CHAR(1);
END-PI SNDSYNCMGQ;

/FREE

// Format Action


SELECT;
WHEN PR_BfrSigned = *BLANKS;
IF PR_AftSigned = 'Y';
V_Action = '*ADD';
ELSE;
V_Action = *BLANKS;
ENDIF;

WHEN PR_BfrSigned = 'Y';
IF PR_AftSigned = 'N';
V_Action = '*REMOVE';
ELSE;
V_Action = *BLANKS;
ENDIF;

WHEN PR_BfrSigned = 'N';
IF PR_AftSigned = 'Y';
V_Action = '*ADD';
ELSE;
V_Action = *BLANKS;
ENDIF;
ENDSL;

// Send message to msgq that NEP is waiting on

IF V_Action <> *BLANKS;
V_PDFName = %TRIM(PR_PDFName) + '.PDF';
PS_MsgData = PR_TktType + V_Action + V_PDFName + DS_User;
PS_MsgDataLen = %LEN(%TRIM(PS_MsgData));
PS_MsgQ(1) = 'MV2IPADFLR*LIBL ';

QMHSNDM(PS_MsgId :
PS_Msgf_Lib :
PS_MsgData :
PS_MsgDataLen:
PS_MsgType :
PS_MsgQ :
PS_NbrMsgQ :
PS_MsgQRply :
PS_MsgKey :
DS_Error );
ENDIF;

*INLR = *ON;
/END-FREE

It compiles and works correctly when I call it.
--------------------------------------------------------------------------------------
Here is the SQL code for creating a stored procedure over the RPG

CREATE PROCEDURE SNDSYNCPRC (
IN TICKTYPE CHAR(10),
IN PDFNAME CHAR(50),
IN BEFORESIGNED CHAR(1),
IN AFTERSIGNED CHAR(1))
LANGUAGE RPGLE
DETERMINISTIC
NO SQL
EXTERNAL NAME SNDSYNCMGQ
PARAMETER STYLE GENERAL

This builds correctly.
Here is the file that I am trying to create the SQL Trigger over:

A WTRCDSTS 1A TEXT('Record Status')
A WTTKTTYPE 10A TEXT('Ticket Type')
A WTLOC 3S 0 TEXT('Location')
A WTTICK 9P 0 TEXT('Ticket Number')
A WTCUST 5P 0 TEXT('Customer Number')
A WTDLVR 9P 0 TEXT('Delivery Number')
A WTCMMDTY 2S 0 TEXT('Commodity')
A WTPDFSEQ 3S 0 TEXT('PDF Sequence#')
A WTSIGNREQ 1A TEXT('Requires Signature')
A WTPDFNAME 50A TEXT('PDF File Name')

The file exists in a library that is in my library list
------------------------------------------------------------------------------------------------------------------------------------------

Here is the SQL code that I am using to create the SQL Trigger

CREATE TRIGGER TESTTRIGGER
AFTER INSERT ON WEBTICK
REFERENCING NEW ROW AS NEWTICKROW
FOR EACH ROW
MODE DB2ROW
WHEN (NEWTICKROW.WTSIGNREQ = 'Y')
CALL SNDSYNCPRC (NEWTICKROW.WTTKTTYPE, NEWTICKROW.WTPDFNAME,
'N', NEWTICKROW.WTSIGNREQ)

This fails with the following error:

Message ID . . . . . . : SQL0312 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : Variable WTSIGNREQ not defined or not usable.
Cause . . . . . : The variable WTSIGNREQ appears in the SQL statement, but
one of the following conditions exists:
-- No declaration for the variable exists or the declaration is not within
the current scope.
-- The attributes are not correct for the use specified.
-- The host variable was specified in dynamic SQL. Host variables are not
valid in dynamic SQL.
-- In REXX, host variable names cannot contain embedded blanks.
-- The variable name is used in the routine body of an SQL procedure or
function, but the variable is not declared as an SQL variable or parameter.
The scope of an SQL variable is the compound statement that contains the
declaration.


this create trigger code is following an example that I found in the STORED PROCEDURES, TRIGGERS AND UDF Functions Redbook.

Can anyone out there point out what I am doing wrong on this?

Thanks

Jon





This email and its attachments contain information intended for the specified individual(s) only. This information is confidential. If you are not the intended recipient, or the person responsible for delivering it to the intended recipient, any use, retention, dissemination, distribution, copying or unauthorized distribution of this communication is strictly prohibited.