有没有一种方法可以终止触发器中的插入查询

Question

如果遇到异常，我想知道它是否能够终止或停止Insert。

触发编码为：

CREATE OR REPLACE TRIGGER TRG_UPT_SOLVED_RPT
AFTER INSERT ON Payment
FOR EACH ROW
DECLARE
more_one_row EXCEPTION;
v_rowCount number;
BEGIN
SELECT TCOUNT(ReportID) INTO v_rowCount
FROM Report;
IF v_rowCount <= 1 THEN
**Do anything else**
ELSIF v_rowCount > 0 THEN
RAISE more_one_row;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Update table failed. Row to be update is not found.');
WHEN more_one_row THEN
DBMS_OUTPUT.PUT_LINE('Update table failed. Row to be update is more than one.');
END;
/

Answer 1

这就是我对它的理解；但是，还不清楚代码中的UPDATE TABLE代表什么。更新哪个表？怎样使用哪些值？

总之：样本数据：

SQL> select * from report order by reportid;
REPORTID NAME
---------- ------
100 name A
100 name B
200 name C
SQL> select * from payment;
no rows selected

触发器：

SQL> create or replace trigger trg_upt_solved_rpt
2    before insert on payment
3    for each row
4  declare
5    v_rowcount number;
6  begin
7    select count(*)
8      into v_rowcount
9      from report
10      where reportid = :new.reportid;
11
12    if v_rowcount = 0 then
13       raise_application_error(-20000, 'Update table failed. Row to be update is not found.');
14    elsif v_rowcount > 1 then
15       raise_application_error(-20001, 'Update table failed. Row to be update is more than one.');
16    end if;
17  end;
18  /
Trigger created.

测试：

SQL> insert into payment (reportid) values (100);
insert into payment (reportid) values (100)
*
ERROR at line 1:
ORA-20001: Update table failed. Row to be update is more than one.
ORA-06512: at "SCOTT.TRG_UPT_SOLVED_RPT", line 12
ORA-04088: error during execution of trigger 'SCOTT.TRG_UPT_SOLVED_RPT'

SQL> insert into payment (reportid) values (200);
1 row created.
SQL> insert into payment (reportid) values (300);
insert into payment (reportid) values (300)
*
ERROR at line 1:
ORA-20000: Update table failed. Row to be update is not found.
ORA-06512: at "SCOTT.TRG_UPT_SOLVED_RPT", line 10
ORA-04088: error during execution of trigger 'SCOTT.TRG_UPT_SOLVED_RPT'

SQL> select * from payment;
REPORTID
----------
200
SQL>