MERGE inside triggers
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: MERGE inside triggers

  1. #1
    Join Date
    Jan 2007
    Posts
    2

    MERGE inside triggers

    We have MERGE statement in a trigger on a table to populate another table.
    The data we are trying to load does not exist in the table yet. But it tries to update the table and errors out (12:24:39 ORA-06502: PL/SQL: numeric or value error: NULL index table key value).

    Here is the Code:

    DECLARE
    L_GIR_SYSTEM_ID NUMBER(5);
    BEGIN
    IF :new.DESCRIPTION != ld.DESCRIPTION then

    SELECT b.gir_system_id
    INTO L_GIR_SYSTEM_ID
    FROM gir_system b
    WHERE b.gir_system_cd = 'IVH';

    MERGE
    INTO GIR_DATA i
    USING (SELECT NULL GIR_DATA_ID,
    :new.DESCRIPTION ||
    ' - ' ||
    to_char(:new.ivh_product_id, 'FM99999') DESCRIPTION,
    L_GIR_SYSTEM_ID GIR_SYSTEM_ID,
    :new.ivh_product_id EXT_SRC_PK_ID
    FROM DUAL) t
    ON ( i.EXT_SRC_PK_ID = t.EXT_SRC_PK_ID
    AND i.GIR_SYSTEM_ID = t.GIR_SYSTEM_ID)
    WHEN MATCHED THEN
    UPDATE
    SET i.DESCRIPTION = t.DESCRIPTION
    WHEN NOT MATCHED THEN
    INSERT
    ( GIR_DATA_ID,
    DESCRIPTION,
    GIR_SYSTEM_ID,
    EXT_SRC_PK_ID)
    VALUES
    ( NULL,
    :new.DESCRIPTION ||
    ' - ' ||
    to_char(:new.ivh_product_id, 'FM99999'),
    L_GIR_SYSTEM_ID,
    :new.IVH_PRODUCT_ID);
    END IF;
    END;

    Thank you.

    Hema

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Cool


    Maybe because you are trying to insert a NULL value into a PK?

    Code:
    INSERT 
    ( GIR_DATA_ID,     --< Here, from --+
    DESCRIPTION,                        |
    GIR_SYSTEM_ID,                      |
    EXT_SRC_PK_ID)                      |
    VALUES                              |
    ( NULL,   --<= NULL Here -----------+
    ...Etc


    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jan 2007
    Posts
    2
    Thanks for your response.

    We have Table API triggers that auto generate pk on insert when null values is provided. We also tested this by doing id_seq.nextval in the insert instead of NULL. This the actual error:
    13:35:02 ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    13:35:02 ORA-06512: at "FAC.CG$AUS_GIR_DATA", line 18
    13:35:02 ORA-04088: error during execution of trigger 'FAC.CG$AUS_GIR_DATA'

    So you can see that it tried to Update instead of insert. And the data we are trying to load is new and it doesn't already exist. So it shuld have done the insert and not Update.

    Thank you.

    Hema.

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    Firstly, what is the point of the select from dual in the merge when you've had to precede that by a select from gir_system.
    Secondly, MERGE is designed for great big chunks of data. When you are dealing with a single row, just stick to an "INSERT/CATCH DUPLICATE KEY ERROR and UPDATE" or "UPDATE/IF NO ROWS UPDATED then INSERT"
    Thirdly, Keep things simple. An insert into a table that fires a trigger to insert into a different table which fires a trigger to populate the primary key of that table is UGLY
    Fourthly, "NULL index table key value" arises from use of PL/SQL tables. Its probably an error from a trigger that you are firing from the insert/update into the other table which is getting picked up as an error at line 18 of this trigger because that's where the insert/update is happening. If that isn't clear, then its because this mess of triggers is UGLY.
    Finally, DML (select/insert/update/delete/merge) in triggers isn't cached in the same way as it is in functions/procedures/packages, so doesn't perform as well. Best to just have the trigger call a packaged procedure.

Posting Permissions

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



Click Here to Expand Forum to Full Width