'Create a Trigger with merge to monitor tables

I need to create a Trigger to monitor this table :

    CREATE TABLE "REFERENCE" 
   (    "NUM_CONTRACT" VARCHAR2(20 CHAR),  
    "NATURE" VARCHAR2(20 CHAR), 
    "PR" VARCHAR2(14 CHAR), 
   )

I just want to store the date of the last modification and his "PR" in this table :

CREATE TABLE EVENT_REFERENCE (
    ID NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
    reference VARCHAR(14) NOT NULL UNIQUE,
    date_modification TIMESTAMP(6),
    PRIMARY KEY (ID)
)

I use a merge in order to avoid to have duplicate rows with the same reference and different date . I just want to keep the date of last modification for each reference rows

For that i created this trigger but idk what's wrong with it . Can anyone help me ?

CREATE OR REPLACE TRIGGER TRG_REFERENCE
    AFTER INSERT OR UPDATE ON REFERENCE
                               FOR EACH ROW
DECLARE
PRAGMA autonomous_transaction;
BEGIN
   IF INSERTING
   THEN
        MERGE INTO EVENT_REFERENCE hist
        USING (select :new.pr
               from   dual) t1
          ON (t1.pr=hist.reference)
        WHEN MATCHED THEN
          UPDATE SET hist.date_modification=systimestamp
        WHEN NOT MATCHED THEN
          INSERT INTO EVENT_REFERENCE (REFERENCE, DATE_MODIFICATION)
          VALUES (:NEW.prm, systimestamp);
   END IF;  
   COMMIT;
END;


Solution 1:[1]

You have:

  • :NEW.prm when it should be :NEW.pr
  • INSERT INTO EVENT_REFERENCE (REFERENCE, DATE_MODIFICATION) when you just need INSERT (REFERENCE, DATE_MODIFICATION)

You also use AFTER INSERT OR UPDATE and then check IF INSERTING why not just use AFTER INSERT?


The fixed code:

CREATE OR REPLACE TRIGGER TRG_REFERENCE
    AFTER INSERT OR UPDATE ON REFERENCE
                               FOR EACH ROW
DECLARE
PRAGMA autonomous_transaction;
BEGIN
   IF INSERTING
   THEN
        MERGE INTO EVENT_REFERENCE hist
        USING DUAL t1
          ON (:new.pr=hist.reference)
        WHEN MATCHED THEN
          UPDATE SET hist.date_modification=systimestamp
        WHEN NOT MATCHED THEN
          INSERT (REFERENCE, DATE_MODIFICATION)
          VALUES (:NEW.pr, systimestamp);
   END IF;  
   COMMIT;
END;
/

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MT0