如何中断触发事件？

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

Answer 2

我会尽我所能将这个逻辑嵌入到检查条件中，而不是触发器中。