Problem With Compound Triggers Losing Local Values When Executing A Merge Statement. (Doc ID 1393441.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms


The value of a variable initialized in a compound trigger "BEFORE STATEMENT" is nullified prior to executing the subsequent statement ("BEFORE ROW") when a MERGE sql statement is executed.

The following sample illustrates the issue.

This is the table to be used.

CREATE TABLE PTEST (
 PK INTEGER PRIMARY KEY NOT NULL,
 VALOR INTEGER NOT NULL,
 TSLAST_MODIF TIMESTAMP(6) NOT NULL)
/


The following creates the TRIGGER.

CREATE OR REPLACE TRIGGER PTEST_iu FOR INSERT OR UPDATE ON PTEST
REFERENCING NEW AS New OLD AS Old COMPOUND TRIGGER
-------------------------------
Ts TIMESTAMP (6);
-------------------------------
BEFORE STATEMENT IS
BEGIN
   DBMS_OUTPUT.Put_line ('Before statement...');
   Ts := SYSTIMESTAMP;
   DBMS_OUTPUT.PUT_LINE('Ts=' || CAST(Ts AS VARCHAR2));
END
BEFORE STATEMENT;
-------------------------------
BEFORE EACH ROW IS
BEGIN
   DBMS_OUTPUT.Put_line ('Before each row...');
   DBMS_OUTPUT.PUT_LINE('Ts=' || CAST(Ts AS VARCHAR2));
   :new.Tslast_modif := Ts;
   IF INSERTING OR :old.Pk IS NULL THEN
      << inserting_block >>
      BEGIN
         DBMS_OUTPUT.Put_line ('Before INSERTING each row...');
      END Inserting_block;
   ELSE
      << Updating_block >>
      BEGIN
         DBMS_OUTPUT.Put_line ('Before UPDATING each row...');
      END Updating_block;
   END IF;
END
BEFORE EACH ROW;
END PTEST_iu;
/



The following creates the Variables and the anonymous block assigns values.

VARIABLE pkparam NUMBER
VARIABLE Valueparam NUMBER
BEGIN
   :pkparam := 20;
   :valueparam := 30;
END;
/



The following is the MERGE command to illustrate the issue.

Set Serveroutput On
MERGE INTO PTEST Tgt
USING DUAL Src
ON (Tgt.Pk = :Pkparam)
WHEN MATCHED THEN
UPDATE SET Tgt.Valor = :Valueparam
WHEN NOT MATCHED THEN
INSERT (Pk, Valor)
VALUES (:Pkparam, :Valueparam);


Sample Output:

Before statement...
Ts=11-JAN-12 05.32.19.940662 PM
Before statement...
Ts=11-JAN-12 05.32.20.011088 PM
Before each row...
Ts=
Before INSERTING each row...
MERGE INTO PTEST Tgt
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("REFRESH"."PTEST"."TSLAST_MODIF")


The ORA-01400 occurs because of the "Not Null" constraint.  Since TS is nullified, the insert fails.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms