Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58

    Unanswered: Trigger and cursor error

    Is it possible to delcare a cursor in a trigger? The following trigger returns:

    SQL0104N An unexpected token "FOR" was found following " ". Expected tokens may include: "<space>". LINE NUMBER=13.
    SQLSTATE=42601



    CREATE TRIGGER InsertCF_Tgr
    AFTER INSERT ON CargoFolder
    REFERENCING NEW AS CF_New
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC

    -- declare variables
    DECLARE SQLSTATE CHAR(5);
    DECLARE Tgr_DeliveryStatus SMALLINT DEFAULT 0;
    DECLARE Tgr_Count SMALLINT DEFAULT 0;

    -- declare the cursor
    DECLARE C1 CURSOR FOR SELECT DeliveryStatus, COUNT(*) FROM CargoMessage WHERE CCN = CF_New.CCN AND Type IN (501,502,504) GROUP BY DeliveryStatus;

    -- open the cursor
    OPEN C1;

    -- Fetch from the cursor until it is empty
    FETCH FROM C1 INTO Tgr_DeliveryStatus, Tgr_Count;
    WHILE (SQLSTATE = '00000') DO

    IF (Tgr_DeliveryStatus = 201)
    THEN UPDATE CF_New.CountNew = Tgr_Count;
    ELSEIF (Tgr_DeliveryStatus = 202)
    THEN UPDATE CF_New.CountSent = Tgr_Count;
    ELSEIF (Tgr_DeliveryStatus = 203)
    THEN UPDATE CF_New.CountDelivered = Tgr_Count;
    ELSEIF(Tgr_DeliveryStatus = 204)
    THEN UPDATE CF_New.CountFailed = Tgr_Count;
    ENDIF;

    FETCH FROM C1 INTO Tgr_DeliveryStatus, Tgr_Count;
    END WHILE;

    CLOSE C1;
    END@

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Nope, only is stored procedures.

    Andy

Posting Permissions

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