Is there a way to terminate the insert query in a trigger?-CodePudding

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

CodePudding user response：

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>