Re: SQL Trigger will not Create

On 10-Sep-2014 14:37 -0500, Jon Sinner wrote:

I'm trying to call a stored procedure from an SQL trigger. <<SNIP>>

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

<<SNIP>>

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)

The above line probably should be changed to the following, to prevent another issue:
CHAR('N', 1), NEWTICKROW.WTSIGNREQ)

This fails with the following error:

Message ID . . . . . . : SQL0312 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Message . . . . : Variable WTSIGNREQ not defined or not usable.
<<SNIP>>

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?

Was the apparent DDS [snipped from my reply] actual DDS, or merely representative? If the DDS used ALIAS or the TABLE was created instead with DDL and the column names had other than /system name/ names [i.e. have long column names], then the issue is presumably the restriction whereby [transition] variables are created only for the long-name. See the following:

<http://archive.midrange.com/midrange-l/201205/msg00837.html>
"... Eventually in the v6r1 CREATE TRIGGER documentation came:
Start of change≥≥ All transition variable names are column names of the subject table. System column names of the subject table cannot be used as transition variable names. ≤≤End of change"

<http://archive.midrange.com/midrange-l/201406/msg00363.html>
"... I do not see anything incorrect, although the DDL for the TABLE might be important. IIRC the SQL supports reference only to the /long name/ column names, so if for example the long column name for WBXPRC were WBX_Process, then the reference may need to be coded as NEW_ROW.WBX_Process instead of NEW_ROW.WBXPRC

I just tested that issue [about alias name for a column] and that is the issue seen on v5r3, but diagnosed as SQL0312 [-312] "Variable WBXPRC not defined or not usable." instead of -206. ..."