Re: Beginner: trigger doesn't rollback insert

From: Assaf <assaf_tamir.no-spam_at_no.sp-am.hotmail.com>
Date: Sat, 07 May 2005 20:01:30 GMT
Message-ID: <u89fe.7423$V01.3767@newsread1.news.atl.earthlink.net>

Jim:

The assignment (college) prescribes a single trigger. I added the stored proc for debugging. So the solution should be somewhere in trigger. Here's sample code:

(trying to get the valid records into the table but prevent the 'bad' one - 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; "Jim Smith" <jim_at_jimsmith.demon.co.uk> wrote in message news:d16iujEWkPfCFw7Q_at_jimsmith.demon.co.uk...
> In message <7d5fe.4900$pe3.2731_at_newsread3.news.atl.earthlink.net>, Assaf
> <assaf_tamir.no-spam_at_no.sp-am.hotmail.com> writes
> >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.
> >
> >
> Does the stored proc trap the exception and then rollback? That would be
> the correct way to do it.
> >
> >How can I prevent the trigger from performing the insert? Any pointers?
> >
> >
> >
> >Thanks!
> >
> >
> >
> >- Assaf
> >
> >
> >
> >
>
> --
> Jim Smith
> Because of their persistent net abuse, I ignore mail from
> these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
> For an explanation see <http://www.jimsmith.demon.co.uk/spam>
Received on Sat May 07 2005 - 15:01:30 CDT