#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0

    Firebird trigger problem


    hi
    i have trouble with a trigger in firebird

    DECLARE VARIABLE CANT_TOTAL NUMERIC(9,2);
    BEGIN
    CANT_TOTAL = 0;
    FOR SELECT SUM (CANTITATE)
    FROM MEMBRU_PASUNE
    WHERE (MEMBRU_ID=OLD.MEMBRU_ID)
    GROUP BY MEMBRU_ID
    INTO:CANT_TOTAL
    DO

    IF (CANT_TOTAL IS NULL) THEN
    BEGIN
    CANT_TOTAL = 0;
    UPDATE MEMBRII
    SET CANT_TOTAL_PASUNE =: CANT_TOTAL,
    MEMBRU_PASUNE_IS = 2
    WHERE MEMBRU_ID=OLD.MEMBRU_ID;
    END
    ELSE
    BEGIN
    UPDATE MEMBRII
    SET CANT_TOTAL_PASUNE =: CANT_TOTAL
    WHERE MEMBRU_ID=OLD.MEMBRU_ID;
    END

    END

    this dont work

    who is the problem ???
    thanks !!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    What does "don't work" mean?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by tsteinmaurer
    What does "don't work" mean?
    dont gave me error message but dont update the CANT_TOTAL_PASUNE and MEMBRU_PASUNE_IS
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    CANT_TOTAL is initialized with 0 and if the FOR SELECT stuff doesn't find a record, CANT_TOTAL stays with 0 and won't get NULL assigned, thus your followed IF condition will evaluate to FALSE.

    Just a wild guess.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by tsteinmaurer
    CANT_TOTAL is initialized with 0 and if the FOR SELECT stuff doesn't find a record, CANT_TOTAL stays with 0 and won't get NULL assigned, thus your followed IF condition will evaluate to FALSE.

    Just a wild guess.
    I remove the CANT_TOTAL =0 from the begininng but still dont work!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    You are using FOR SELECT statement. It's a cycle. And all actions are in its body. So if the select statement doesn't return anything, none of the code is executed. Use simple SELECT statement, and remove CANT_TOTAL = 0;

    Here it is + small changes for shorter code:
    Code:
    BEGIN 
    SELECT 
      SUM (CANTITATE) 
    FROM 
      MEMBRU_PASUNE 
    WHERE 
      MEMBRU_ID = OLD.MEMBRU_ID  
    INTO
      :CANT_TOTAL;
    
      UPDATE MEMBRII SET 
        CANT_TOTAL_PASUNE = COALESCE(:CANT_TOTAL, 0),
        MEMBRU_PASUNE_IS = IIF(cant_total IS NULL, 2, MEMBRU_PASUNE_IS) 
      WHERE 
        MEMBRU_ID=OLD.MEMBRU_ID; 
    
    END
    Last edited by mIRCata; October 1st, 2013 at 03:34 AM.

IMN logo majestic logo threadwatch logo seochat tools logo