CODEWITHSUNDEEP
oracle-database
fvital
fvital

Reputation: 485

How to break trigger event?

There is some trigger: 

CREATE OR REPLACE TRIGGER `before_insert_trigger` BEFORE INSERT 

ON `my_table` FOR EACH ROW 

DECLARE `condition` INTEGER:=0;

BEGIN

    IF **** THEN
        condition=1;
    END IF;

    IF condition <> 0 THEN    
        --Here I need to break trigger or event or smth to prevent
        --INSERT to be done    
    END IF;

END;

Upvotes: 2

Views: 7394

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

I would try to do whatever I could to embed this logic in a check condition rather than a trigger.

Upvotes: 5

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

To prevent the statement from succeeding, simply raise an error. This will rollback the statement (each statement is atomic, it fails or succeeds entirely). You won't lose work done in this transaction before the beginning of the insert.

You could use the raise_application_error procedure to raise the error.

Here is a small example:

SQL> CREATE TABLE foo (ID NUMBER);

Table created

SQL> CREATE OR REPLACE TRIGGER trg BEFORE INSERT ON foo FOR EACH ROW
  2  BEGIN
  3     IF :new.id = 2 THEN
  4        raise_application_error(-20001, 'Id should not be "2"');
  5     END IF;
  6  END;
  7  /

Trigger created

SQL> INSERT INTO foo (SELECT 1 FROM dual UNION ALL SELECT 2 FROM dual);

INSERT INTO foo (SELECT 1 FROM dual UNION ALL SELECT 2 FROM dual)

ORA-20001: Id should not be "2"
ORA-06512: at "VNZ.TRG", line 3
ORA-04088: error during execution of trigger 'VNZ.TRG'

SQL> select * from foo;

        ID
----------

Upvotes: 7

Related Questions