0

I have created a trigger so I could insert every row in a backup table

CREATE OR REPLACE TRIGGER xyz AFTER INSERT
ON table_1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO table_2
(Did, Dvalue)
VALUES(:NEW.Did, :NEW.Dvalue);
END ; 

My question is if I have AFTER INSERT OR UPDATE instead of AFTER INSERT. Will my trigger try to insert a new row in table_2 every time even if an UPDATE query which is acting on table_1 and throw a unique constraint error for that Did

Or like I want, and this is what I am guessing, it will UPDATE the same record.

CREATE OR REPLACE TRIGGER xyz AFTER INSERT OR UPDATE
ON table_1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO table_2
(Did, Dvalue)
VALUES(:NEW.Did, :NEW.Dvalue);
END ; 

I get this doubt since I am using FOR EACH ROW and BEGIN INSERT INTO...

0

INSERT INTO table_2 will always insert and never update. If you need different operation depends on which operation(insert or update) actually fired the trigger, you need to check it with IF UPDATING THEN ... END IF; or (IF INSERTING THEN ... END IF; )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.