Re: Beginner: trigger doesn't rollback insert

Assaf wrote:
> Clarifying: I'm trying to get the valid records into the table but
prevent
> the 'bad' (XX9999)
>
> Code:
>
> CREATE OR REPLACE PROCEDURE TEST_BAD
> AS
> BEGIN
> INSERT INTO PO_ITEM VALUES(4, 'FA5555', 0,0,0,99);
> INSERT INTO PO_ITEM VALUES(1, 'XX9999', 0,0,0,99);
> INSERT INTO PO_ITEM VALUES(1, 'FA5555', 0,0,0,99);
> DBMS_OUTPUT.PUT_LINE('proc ended');
>
> END;
>
>
> Trigger 1: doesn't do the trick because it fails ALL inserts, not
just the
> bad one (XX9999)
>
> CREATE OR REPLACE TRIGGER Test_Insert1
> Before Insert or update on PO_Item
> for each row
>
> DECLARE
> Invalid_Partno EXCEPTION;
> BEGIN
> IF (:new.Partno = 'XX9999') THEN
> raise_application_error (-20666, 'Bad POnum');
> ELSE
> :new.LineExt := 99;
> END IF;
> DBMS_OUTPUT.PUT_LINE('trigger ended');
>
> END;
>
> Trigger 2: doesn't do the trick because it DOES an insert for XX9999
>
> CREATE OR REPLACE TRIGGER Test_Insert2
> Before Insert or update on PO_Item
>
> for each row
>
> DECLARE
> Invalid_Partno EXCEPTION;
>
> BEGIN
>
> IF (:new.Partno = 'XX9999') THEN
> raise Invalid_Partno;
>
> ELSE
> :new.LineExt := 99;
> END IF;
>
> DBMS_OUTPUT.PUT_LINE('Part #: ' || :new.Partno);
>
> EXCEPTION
> WHEN Invalid_Partno THEN
> DBMS_OUTPUT.PUT_LINE('Bad Part Number');
> END;
>
>
> "Dave" <x_at_x.com> wrote in message
> news:hv5fe.27082$G8.22680_at_text.news.blueyonder.co.uk...
> >
> > "Assaf" <assaf_tamir.no-spam_at_no.sp-am.hotmail.com> wrote in message
> > news:7d5fe.4900$pe3.2731_at_newsread3.news.atl.earthlink.net...
> > > My BEFORE INSERT trigger checks for invalid quantities.
> > >
> > >
> > >
> > > If QTY <= 0, a raise_application_error is issued and outputs a
message
> to
> > > the user. I was also expecting the error to roll back the insert
but the
> > > record is still inserted into the table.
> > >
> > >
> > >
> > > Manual inserts via SQL*Plus seem to be OK (i.e., rolled back by
> trigger).
> > > But the trigger does commit records inserted through stored
procs.
> > >
> > > How can I prevent the trigger from performing the insert? Any
pointers?
> > >
> > > Thanks!
> > >
> > > - Assaf
> > >
> >
> > code?
> >
> >

This trigger works:

CREATE OR REPLACE TRIGGER Test_Insert2
Before Insert or update on PO_Item
for each row
DECLARE
 Invalid_Partno EXCEPTION;
 pragma exception_init(Invalid_Partno, -20101); BEGIN
 IF :new.PartNo = 'XX9999' THEN
  raise Invalid_Partno;
 ELSE
  :new.LineExt := 99;
 END IF;
 DBMS_OUTPUT.PUT_LINE('Part #: ' || :new.Partno); EXCEPTION
 WHEN Invalid_Partno THEN
  DBMS_OUTPUT.PUT_LINE('Bad Part Number');   raise_application_error(-20101, 'Incorrect or invalid part number'); END;
/

As proven by:

SQL> exec test_bad
Part #: FA5555
Bad Part Number
BEGIN test_bad; END;

*
ERROR at line 1:

ORA-20101: Incorrect or invalid part number
ORA-06512: at "SCOTT.TEST_INSERT2", line 14
ORA-04088: error during execution of trigger 'SCOTT.TEST_INSERT2'
ORA-06512: at "SCOTT.TEST_BAD", line 5
ORA-06512: at line 1

SQL> select *
  2 from po_item;

no rows selected

SQL> Of course, since the second insert violates the trigger the third insert never gets processed, and the entire set of inserts is rolled back. Commiting after each insert would at least allow the first insert to actually have a row in the table (it IS inserted, but the error on the second rolls back all work to the last committed stage, which is prior to the start of this procedure).

David Fitzjarrell Received on Sat May 07 2005 - 21:04:35 CDT