Re: Beginner: trigger doesn't rollback insert

"Assaf" <assaf_tamir.no-spam_at_no.sp-am.hotmail.com> wrote in message news:h59fe.7410$V01.2743_at_newsread1.news.atl.earthlink.net...
> 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?
> >
> >
>
>

As it should. An error will rollback a transaction. All your inserts in your example are in the same transaction. One does not commit in a trigger. Jim Received on Sat May 07 2005 - 17:55:51 CDT