我想知道如果遇到例外，它是否能夠終止或停止插入。
觸發編碼將是：
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;
/
uj5u.com熱心網友回復：
我是這樣理解的；但是，尚不清楚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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/455867.html
上一篇：我無法創建和使用包