1

When creating an Instead Of trigger, Sql Developer allows to include all 3 DML actions in the same trigger:

enter image description here

The result SQL looks like:

CREATE OR REPLACE TRIGGER TRIGGER1 
INSTEAD OF DELETE OR INSERT OR UPDATE ON PO_COSTED_BOM_V 
BEGIN
  NULL;
END;

I'm able to combine Insert and Update like this:

CREATE OR REPLACE TRIGGER TRIGGER1 
INSTEAD OF INSERT OR UPDATE OR DELETE ON PO_COSTED_BOM_V 
BEGIN
  UPDATE SRC_TBL SET CFG_VALUE = :NEW.CFG_VALUE
  WHERE CFG_NAME = :NEW.CFG_NAME;
  IF SQL%NOTFOUND THEN
      INSERT INTO SRC_TBL (CFG_NAME, CFG_VALUE) 
         VALUES(:NEW.CFG_NAME, :NEW.CFG_VALUE);
  END IF;
END;
  • But how do i get the Delete statement into the trigger?
  • How does the trigger know which DML action was executed?

thx

5

From Coding the Trigger Body

Detecting the DML Operation that Fired a Trigger

If more than one type of DML operation can fire a trigger (for example, ON INSERT OR DELETE OR UPDATE OF emp), the trigger body can use the conditional predicates INSERTING, DELETING, and UPDATING to check which type of statement fire the trigger.

Within the code of the trigger body, you can execute blocks of code depending on the kind of DML operation that fired the trigger:

IF INSERTING THEN ... END IF;
IF UPDATING THEN ... END IF;
IF DELETING THEN ... END IF;

in your case

...
if inserting then

INSERT INTO SRC_TBL (CFG_NAME, CFG_VALUE) 
         VALUES(:NEW.CFG_NAME, :NEW.CFG_VALUE);

elsif updating then

 UPDATE SRC_TBL SET CFG_VALUE = :NEW.CFG_VALUE
  WHERE CFG_NAME = :NEW.CFG_NAME;

elsif deleting then

 ...

END IF;
  • great! thx much – johny why Sep 23 '16 at 23:38
  • extra credit: currently, manual update in the grid of the view in Sql Dev is treated as a direct edit of the view (which therefore fails, since my view is not updatable)-- it does not fire the trigger. Is it possible to make a grid edit fire the trigger? Or does Sql Dev offer an alternate native grid which could fire the trigger? if possible, i shall post as a new question so others can benefit. – johny why Sep 23 '16 at 23:40
  • the best, you post a new question, with the definition of the view and the trigger. – schurik Sep 24 '16 at 10:59
  • is the else required? Can't we just do IF INSERTING THEN ... END IF; IF UPDATING THEN ... END IF; IF DELETING THEN ... END IF; – johny why Sep 27 '16 at 19:17
  • no, the elsif is not requied, many ways lead to Rome :) – schurik Sep 28 '16 at 8:10

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.