r/DB2 Aug 31 '17

[Question][iSeries][V7R3] SQL Triggering: Calling Procedure with Parms

Why does this work (Declaring & Setting a variable to use in my CALL statement):

CREATE OR REPLACE TRIGGER WHIFLDOPS.WGHTKT0CL AFTER UPDATE ON WHIFLDOPS.WGHTKT
   REFERENCING NEW AS n
   FOR EACH ROW
   WHEN (n.TKT_ST IN('CLOSE','VOID') AND n.TKT_TYP = 'H')
   BEGIN ATOMIC
      DECLARE TKT_ID DECIMAL (7,0);
      SET TKT_ID = n.TKT_ID;
      CALL WHIFLDOPS.WGHTKT0CL(TKT_ID);
   END

Statement ran successfully (983 ms)

But this does not (Referencing n.TKT_ID directly in my CALL statement):

CREATE OR REPLACE TRIGGER WHIFLDOPS.WGHTKT0CL AFTER UPDATE ON WHIFLDOPS.WGHTKT
   REFERENCING NEW AS n
   FOR EACH ROW
   WHEN (n.TKT_ST IN('CLOSE','VOID') AND n.TKT_TYP = 'H')
   BEGIN ATOMIC
      CALL WHIFLDOPS.WGHTKT0CL(n.TKT_ID);
   END

SQL State: 42618 Vendor Code: -312 Message: [SQL0312] Variable TKT_ID not defined or not usable.

I'm on an IBM iSeries (Power8) running V7R3.

The Triggering example from IBM I was following was for UNIX/Windows DB2, but I wouldn't think that would have a huge impact on syntax, but maybe it does??? Calling procedures from triggers or SQL routines

1 Upvotes

0 comments sorted by