Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: trigger head-aches ...............

    What is wrong with the following trigger definition:


    CREATE OR REPLACE TRIGGER TBL_TRIGGER6
    AFTER UPDATE OF MOV_REASON_CODE ON STOCK_MOVEMENT
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    WHEN (NEW.MOV_REASON_CODE = 'RD1')
    DECLARE
    t_STK_ID STOCK_MOVEMENT.MOV_TO_LOC_ID%TYPE;
    t_REC STOCK%ROWTYPE;
    BEGIN
    SELECT * INTO t_REC
    FROM STOCK
    WHERE STK_LOC_ID = t_STK_ID AND
    STK_ZON_ID = 'BPI' AND STK_STAGE <> '90';
    INSERT INTO TEMP_TABLE(TEMP_STK_ID,TEMP_STK_LOC_ID,TEMP_STK_PR O_CODE,TEMP_STK_QTY)
    VALUES(t_REC.STK_ID,t_REC.STK_LOC_ID,t_REC.STK_PRO _CODE,t_REC.STK_QTY);
    COMMIT;
    END TBL_TRIGGER6;

    execution fails , because no data is found, but the SQL block does retrieve a single row. I presume that I can reference the variable t_STK_ID in the SQL block?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trigger head-aches ...............

    Yes, you can reference the variable t_stk_id in the SQL - but since you don't set t_stk_id to a value first, it is NULL and so the query will fail (nothing is EVER equal to NULL).

    I suspect you meant it to be equal to the value of STOCK_MOVEMENT.MOV_TO_LOC_ID for the row just updated? In that case your trigger should be:

    Code:
    CREATE OR REPLACE TRIGGER TBL_TRIGGER6
    AFTER UPDATE OF MOV_REASON_CODE ON STOCK_MOVEMENT
    FOR EACH ROW
    WHEN (NEW.MOV_REASON_CODE = 'RD1')
    BEGIN
    
      INSERT INTO TEMP_TABLE(TEMP_STK_ID,TEMP_STK_LOC_ID,TEMP_STK_PRO_CODE,TEMP_STK_QTY)
        SELECT STK_ID,STK_LOC_ID,STK_PRO_CODE,STK_QTY
        FROM   STOCK
        WHERE  STK_LOC_ID = :NEW.MOV_TO_LOC_ID
        AND    STK_ZON_ID = 'BPI'
        AND    STK_STAGE <> '90'; 
    
    END TBL_TRIGGER6;
    Note: You cannot COMMIT in a trigger (unless in an autonomous transaction, which you are not and should not be).

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Yes, indeed that was exactly the structure that I got with the 8th version of this trigger.

    Problem is that it keeps coming with wrong number of columns if I use the phrase:

    SELECT STK_ID,STK_LOC_ID,STK_PRO_CODE,STK_QTY
    FROM STOCK

    If I use the more general :

    SELECT * FROM STOCK the trigger work like it should.


    ???????????????????????????//

    By the way , is this allowed:

    SELECT STK_LOC_ID,STK_PRO_CODE,SUM(STK_QTY)
    FROM STOCK
    GROUP BY STK_LOC_ID,STK_PRO_CODE

    cause in some case there are multiple entries (QTY) for a given combination of STK_LOC_ID / STK_PRO_CODE
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by blom0344
    Problem is that it keeps coming with wrong number of columns if I use the phrase:

    SELECT STK_ID,STK_LOC_ID,STK_PRO_CODE,STK_QTY
    FROM STOCK

    If I use the more general :

    SELECT * FROM STOCK the trigger work like it should.
    What?! How can the first SELECT come up with any number of columns other than 4? And you specify 4 columns in the INSERT statement, so you'd better select those same 4 columns in the same order!

    Originally posted by blom0344
    By the way , is this allowed:

    SELECT STK_LOC_ID,STK_PRO_CODE,SUM(STK_QTY)
    FROM STOCK
    GROUP BY STK_LOC_ID,STK_PRO_CODE

    cause in some case there are multiple entries (QTY) for a given combination of STK_LOC_ID / STK_PRO_CODE
    Yes, that is allowed. But now you only have 3 columns, if that matters.

  5. #5
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    I checked the order of the columns, but the trigger only compiles when I state the select * from. If I state the selected columns it keeps coming back with the wrong number of columns in the select .... into .... part of the trigger. This prevents me from doing an aggregate, cause I have to use the column names .

    Would this work:

    Select * from
    (select a,b,c,d from table where .....................) into
    temp_table
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by blom0344
    I checked the order of the columns, but the trigger only compiles when I state the select * from. If I state the selected columns it keeps coming back with the wrong number of columns in the select .... into .... part of the trigger. This prevents me from doing an aggregate, cause I have to use the column names .

    Would this work:

    Select * from
    (select a,b,c,d from table where .....................) into
    temp_table
    No, that syntax is invalid.

    What is the code of the trigger when you specify the columns and it doesn't compile?

  7. #7
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    CREATE OR REPLACE TRIGGER TBL_TRIGGER9
    AFTER UPDATE OF MOV_REASON_CODE ON STOCK_MOVEMENT
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    WHEN (NEW.MOV_REASON_CODE = 'RD1' AND OLD.MOV_REASON_CODE IS NULL)
    DECLARE
    t_REC2 STOCK%ROWTYPE;
    BEGIN
    SELECT STK_LOC_ID,STK_PRO_CODE,SUM(STK_QTY) AS SUM_QTY INTO t_REC2
    FROM STOCK
    WHERE STK_LOC_ID = :OLD.MOV_TO_LOC_ID AND
    STK_ZON_ID = 'BPI' AND STK_STAGE <> '90'
    GROUP BY STK_LOC_ID,STK_PRO_CODE;
    INSERT INTO TEMP_TABLE(TEMP_STK_LOC_ID,TEMP_STK_PRO_CODE,TEMP_ STK_QTY)
    VALUES(t_REC2.STK_LOC_ID,t_REC2.STK_PRO_CODE,t_REC 2.SUM_QTY);
    END TBL_TRIGGER9;

    does not compile like it should for proper data storage. Is creation of a view a viable option to replace data in the STOCK table?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by blom0344
    CREATE OR REPLACE TRIGGER TBL_TRIGGER9
    AFTER UPDATE OF MOV_REASON_CODE ON STOCK_MOVEMENT
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    WHEN (NEW.MOV_REASON_CODE = 'RD1' AND OLD.MOV_REASON_CODE IS NULL)
    DECLARE
    t_REC2 STOCK%ROWTYPE;
    BEGIN
    SELECT STK_LOC_ID,STK_PRO_CODE,SUM(STK_QTY) AS SUM_QTY INTO t_REC2
    FROM STOCK
    WHERE STK_LOC_ID = :OLD.MOV_TO_LOC_ID AND
    STK_ZON_ID = 'BPI' AND STK_STAGE <> '90'
    GROUP BY STK_LOC_ID,STK_PRO_CODE;
    INSERT INTO TEMP_TABLE(TEMP_STK_LOC_ID,TEMP_STK_PRO_CODE,TEMP_ STK_QTY)
    VALUES(t_REC2.STK_LOC_ID,t_REC2.STK_PRO_CODE,t_REC 2.SUM_QTY);
    END TBL_TRIGGER9;

    does not compile like it should for proper data storage. Is creation of a view a viable option to replace data in the STOCK table?
    OK, now I can see what is happening. The SELECT into t_rec2 fails because you are selecting 4 columns but t_rec2 expects as many columns as there are in table STOCK. SELECT * would be the correct code in this case.

    But why select into a record variable at all? My suggestion was:

    INSERT INTO TEMP_TABLE(TEMP_STK_ID,TEMP_STK_LOC_ID,TEMP_STK_PR O_CODE,TEMP_STK_QTY)
    SELECT STK_ID,STK_LOC_ID,STK_PRO_CODE,STK_QTY
    FROM STOCK
    WHERE STK_LOC_ID = :NEW.MOV_TO_LOC_ID
    AND STK_ZON_ID = 'BPI'
    AND STK_STAGE <> '90';

    i.e. you select 4 columns and insert into 4 columns in temp_table, all in one statement.

    Note: SELECT ... INTO will FAIL if the select returns 0 or more than 1 row. It's not apparent to me that the SELECT here is guaranteed to return exactly 1 row - I mean, it's not selecting by a primary key is it?
    In contrast, INSERT ... SELECT will successfully copy 0, 1 or more rows from STOCK to TEMP_TABLE.

    I don't understand your question "Is creation of a view a viable option to replace data in the STOCK table?" What do you mean by that?

  9. #9
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    The view was a thought to do the aggregation beforehand (if calling a view is acceptable anyway)
    But nevermind, you last example got me the final result I was looking for:

    TRIGGER TBL_TRIGGER9
    AFTER UPDATE OF MOV_REASON_CODE ON STOCK_MOVEMENT
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    WHEN (NEW.MOV_REASON_CODE = 'RD1' AND OLD.MOV_REASON_CODE IS NULL)
    BEGIN
    INSERT INTO TEMP_TABLE(TEMP_STK_LOC_ID,TEMP_STK_PRO_CODE,TEMP_ STK_QTY)
    SELECT STK_LOC_ID,STK_PRO_CODE,SUM(STK_QTY)
    FROM STOCK
    WHERE STK_LOC_ID = :NEW.MOV_TO_LOC_ID
    AND STK_ZON_ID = 'BPI'
    AND STK_STAGE <> '90'
    GROUP BY STK_LOC_ID,STK_PRO_CODE;
    END TBL_TRIGGER9;

    and it works perfectly.................

    Thanks very much....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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