'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 needINSERT (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 |