Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    NC, USA
    Posts
    1

    Question Unanswered: SQL0312 - Trigger not working

    Hey... I'm REAL NEW to this so please bear with me...
    I've been trying to learn triggers for a few days
    and I'm stuck with this problem.I'm trying to create
    a trigger as described below.I get an SQL error that
    tells me that PONO has NOT been declared as a
    variable. It's actually a field in the "JEBDATA"."FAXPODEV"
    file that needs to be updated. I have no idea how
    to fix this. The UPDATE will work fine if I take
    out the SET N.PONO part. Any help would be
    appreciated and pointers on where to get some
    good literature It almost seems that the trigger has
    no idea of the field structure of NEW or field names.

    CREATE TRIGGER SETFAXPONO
    AFTER INSERT ON "JEBDATA"."FAXPODEV"
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2ROW

    BEGIN ATOMIC
    SET N.PONO = ( SELECT MAX(LSTFPO)
    FROM "JEBDATA"."STORESDEV"
    WHERE STORE# = 1);
    UPDATE "JEBDATA"."STORESDEV"
    SET LSTFPO = LSTFPO +1;
    END

    -----------------
    Donnie Rakes
    DB2 on AS/400
    Client Access V5R1
    Delphi 6 Enterprise
    dbExpress

  2. #2
    Join Date
    Mar 2003
    Posts
    33

    Re: SQL0312 - Trigger not working

    I think you can't use SET -statement in AFTER -type trigger. Change it to the 'UPDATE .. SET -clause. Somehow like here :

    CREATE TRIGGER SETFAXPONO
    AFTER INSERT ON "JEBDATA"."FAXPODEV"
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2ROW

    BEGIN ATOMIC
    UPDATE JEBDATA.FAXPODEV SET N.PONO = ( SELECT MAX(LSTFPO)
    FROM JEBDATA.STORESDEV
    WHERE STORE# = 1);
    UPDATE JEBDATA.STORESDEV
    SET LSTFPO = LSTFPO +1;
    END



    Originally posted by Donnie Rakes
    Hey... I'm REAL NEW to this so please bear with me...
    I've been trying to learn triggers for a few days
    and I'm stuck with this problem.I'm trying to create
    a trigger as described below.I get an SQL error that
    tells me that PONO has NOT been declared as a
    variable. It's actually a field in the "JEBDATA"."FAXPODEV"
    file that needs to be updated. I have no idea how
    to fix this. The UPDATE will work fine if I take
    out the SET N.PONO part. Any help would be
    appreciated and pointers on where to get some
    good literature It almost seems that the trigger has
    no idea of the field structure of NEW or field names.

    CREATE TRIGGER SETFAXPONO
    AFTER INSERT ON "JEBDATA"."FAXPODEV"
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2ROW

    BEGIN ATOMIC
    SET N.PONO = ( SELECT MAX(LSTFPO)
    FROM "JEBDATA"."STORESDEV"
    WHERE STORE# = 1);
    UPDATE "JEBDATA"."STORESDEV"
    SET LSTFPO = LSTFPO +1;
    END

    -----------------
    Donnie Rakes
    DB2 on AS/400
    Client Access V5R1
    Delphi 6 Enterprise
    dbExpress
    -r-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •