How to break trigger event?

Question

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;

Thanks in advance

Answer 1

Hi fvital,

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
----------

Answer 2

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