I wonder is it able to terminate or stop the Insert if it hits the exceptions.
The trigger coding will be:
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; /
Advertisement
Answer
This is how I understood it; though, it is not clear what UPDATE TABLE
in your code represents. Update which table? How? With which values?
Anyway: sample data:
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
Trigger:
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.
Testing:
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>