Re: Beginner: trigger doesn't rollback insert

Thanks, Jim. So is there not a way to allow the 'good' inserts and prevent the 'bad' inserts?

Requirements for this assignment are:
* Must use the trigger (not sp)
* Can't use INSTEAD OF trigger

Thanks.

• Assaf

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:apmdnUrUpYHa2uDfRVn-og_at_comcast.com...
>
> "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 Sun May 08 2005 - 11:24:13 CDT