Re: Beginner: trigger doesn't rollback insert

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

You need to understand transactions. Let me give you an example. For example, you go to your local ATM to transfer money from your savings account to your checking account. There is always a possibility that a hardware or software failure will happen in the middle of your moving money from savings to checking. What must happen is no matter what failure occurs the end result will be:
1. The money will transfer from savings to checking and the correct amounts subtracted from savings and added to checking. OR
2. No money will be transferred at all. (either you tried to transfer too much money or an incorrect amount, or a software or hardware failure occurred. - disk crash, software crash, network failure etc.)

To meet these requirements the concept of a database transaction came about. A database transaction is an all or nothing operation. To start a transaction you issue a commit or rollback statement. Then you issue whatever SQL statements you want. Then you issue a commit statement (to "save" the transaction) or a rollback statement (to "undo" the transaction). In our example the statements might look like this:

commit; -- start the transaction, logon implicitly starts a transaction select balance from savings where accountnumber=1234; (transfer $300.00 from savings to checking) update savings set balance=balance-300 where accountnumber=1234; (contraint where balance>=0)
update checking set balance=balance+300; commit; -- end the transaction

In your case all your inserts are in one transaction. So a failure or error rollsback the transaction.(all your inserts) To the database it has no way of knowing which ones it should keep or not so it rolls them all back. It is up to you to properly define your transactions. If you want to keep the good rows and throw out the others then define your transaction as such. insert....
commit
insert...
....

Some databases allow you to put them into autocommit mode. They commit after every statement. This behavior is a terrible and is used to get around poorly designed database software. Oracle does not have such a feature.
Jim
Jim Received on Sun May 08 2005 - 14:27:08 CDT