oracle - 如何打破触发事件？

Question

有一些触发器:

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;

Answer 1

要防止语句成功，只需引发一个错误。这将回滚语句(每个语句都是原子的，它失败或完全成功)。在插入开始之前，您不会丢失在此事务中完成的工作。

您可以使用 raise_application_error 过程来引发错误。

这是一个小例子:

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