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

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    Nesting Triggers


    I have an issue with Nesting the Triggers :

    I Have 3 tables

    1. ITEMS
    2. STOCK
    3.MOVES

    WHEN I INSERT A RECORD IN MOVES I WANT THE TABLE STOCK UPDATED AS FOLLOWS :

    CREATE OR ALTER TRIGGER TR_INSERT_MOVE FOR MOVEMENT
    ACTIVE AFTER INSERT POSITION 0
    AS
    begin
    /* Trigger text */

    UPDATE STOCK A
    SET A.stk_stock_real=A.stk_stock_real +
    CASE NEW.mve_in_out
    WHEN 0
    THEN NEW.mve_quantity * 1
    ELSE
    NEW.mve_quantity*-1
    end
    WHERE A.stk_itm_seq=NEW.mve_itm_seq AND A.stk_dep_seq=NEW.mve_dep_seq
    ;
    end
    IT WORKS PERFECT !!!

    I ALSO WANT THE TABLE ITEMS TO BE UPDATED WHENEVER THE TABLE STOCK IS UPDATED AS FOLLOWS :

    CREATE OR ALTER TRIGGER TR_UPDATE_STOCK FOR STOCK
    ACTIVE AFTER UPDATE POSITION 0
    AS
    begin
    EXCEPTION TEST 'ITEMS :' || NEW.stk_itm_seq ;
    UPDATE ITEMS B
    SET B.itm_stock_real =
    (SELECT SUM(C.stk_stock_real)
    FROM STOCK C WHERE C.stk_itm_seq=OLD.stk_itm_seq )

    WHERE B.itm_seq=OLD.stk_itm_seq ;
    end


    BUT THE TABLE ITEMS IS NEVER UPDATED

    Does the trigger on STOCK is activated when the trigger on MOVEMENT is invoked ?

    Is there any other valuable method to do it ?

    Thanks for your help

    Comments on this post

    • mariuz agrees
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    Remove "EXCEPTION TEST 'ITEMS :' || NEW.stk_itm_seq ;" from the second trigger - this throws an exception and stops the execution of the rest of the code. What is the idea for the exception.
    It's like any exception in the other programming languages - when you throw an exception in some function's body the rest of the code is skipped.

IMN logo majestic logo threadwatch logo seochat tools logo