Re: Beginner: trigger doesn't rollback insert

From: Assaf <assaf_tamir.no-spam_at_no.sp-am.hotmail.com>
Date: Sat, 07 May 2005 19:58:05 GMT
Message-ID: <h59fe.7410$V01.2743@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?
>
>
Received on Sat May 07 2005 - 14:58:05 CDT