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 != :old.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

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

---

:rolleyes:

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.

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.