Beginner: trigger doesn't rollback insert
Assaf
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
Dave
"Assaf" <assaf_tam...@no.sp-am.hotmail.com> wrote in message
news:7d5fe.4900$pe3....@newsread3.news.atl.earthlink.net...
code?
Jim Smith
<assaf_tam...@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.
>
>
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>
Assaf
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...@x.com> wrote in message
news:hv5fe.27082$G8.2...@text.news.blueyonder.co.uk...
Assaf
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:
END;
END;
for each row
DECLARE
Invalid_Partno EXCEPTION;
BEGIN
DBMS_OUTPUT.PUT_LINE('Part #: ' || :new.Partno);
"Jim Smith" <j...@jimsmith.demon.co.uk> wrote in message
news:d16iujEW...@jimsmith.demon.co.uk...
DA Morgan
Oracle is not SQL Server. Look up 'atomic transactions' with respect to
Oracle. A trigger can not contain either COMMIT or ROLLBACK. These
actions must be taken explicitly, as in SQL*Plus, or in code within a
procedure or package.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
Jim Kennedy
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
fitzj...@cox.net
This trigger works:
CREATE OR REPLACE TRIGGER Test_Insert2
Before Insert or update on PO_Item
for each row
DECLARE
Invalid_Partno EXCEPTION;
pragma exception_init(Invalid_Partno, -20101);
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');
raise_application_error(-20101, 'Incorrect or invalid part number');
END;
/
As proven by:
SQL> exec test_bad
Part #: FA5555
Bad Part Number
BEGIN test_bad; END;
*
ERROR at line 1:
ORA-20101: Incorrect or invalid part number
ORA-06512: at "SCOTT.TEST_INSERT2", line 14
ORA-04088: error during execution of trigger 'SCOTT.TEST_INSERT2'
ORA-06512: at "SCOTT.TEST_BAD", line 5
ORA-06512: at line 1
SQL> select *
2 from po_item;
no rows selected
SQL>
Of course, since the second insert violates the trigger the third
insert never gets processed, and the entire set of inserts is rolled
back. Commiting after each insert would at least allow the first
insert to actually have a row in the table (it IS inserted, but the
error on the second rolls back all work to the last committed stage,
which is prior to the start of this procedure).
David Fitzjarrell
Assaf
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-downwit...@attbi.net> wrote in message
news:apmdnUrUpYH...@comcast.com...
Jim Kennedy
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
Sybrand Bakker
<assaf_tam...@no.sp-am.hotmail.com> wrote:
>Thanks, Jim. So is there not a way to allow the 'good' inserts and prevent
>the 'bad' inserts?
Please refrain from top posting, and other bad behaviors you learned
from Microsux.
--
Sybrand Bakker, Senior Oracle DBA
Mr. Smith
"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message
news:s2ts71924l843746s...@4ax.com...
> On Sun, 08 May 2005 16:24:13 GMT, "Assaf"
> <assaf_tam...@no.sp-am.hotmail.com> wrote:
>
>>Thanks, Jim. So is there not a way to allow the 'good' inserts and prevent
>>the 'bad' inserts?
>
> Please refrain from top posting, and other bad behaviors you learned
> from Microsux.
Ah yes, Sybrand the wanker is still up to his/her preachy tricks.
Do us all a favor and learn some manners.
Sybrand Bakker
<gwbise...@hotmail.com> wrote:
>Ah yes, Sybrand the wanker is still up to his/her preachy tricks.
>
>Do us all a favor and learn some manners.
I guess you are the one that should learn manners (if you do at least
know what they are)
Mr. Smith
> On Wed, 11 May 2005 11:31:05 -0700, "Mr. Smith"
> <gwbise...@hotmail.com> wrote:
>
>>Ah yes, Sybrand the wanker is still up to his/her preachy tricks.
>>
>>Do us all a favor and learn some manners.
>
> I guess you are the one that should learn manners (if you do at least
> know what they are)
>
You're the master of the unprovoked flame.
Deal with it!
DA Morgan
If you have a problem with Sybrand please deal with it off-line.
Thank you.
Mr. Smith
"DA Morgan" <damo...@x.washington.edu> wrote in message
news:1115941425.703302@yasure...
> Mr. Smith wrote:
>
>> "Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message
>> news:3dq481du1l763c3e3...@4ax.com...
>>
>>>On Wed, 11 May 2005 11:31:05 -0700, "Mr. Smith"
>>><gwbise...@hotmail.com> wrote:
>>>
>>>
>>>>Ah yes, Sybrand the wanker is still up to his/her preachy tricks.
>>>>
>>>>Do us all a favor and learn some manners.
>>>
>>>I guess you are the one that should learn manners (if you do at least
>>>know what they are)
>>>
>>
>>
>> You're the master of the unprovoked flame.
>>
>> Deal with it!
>
> If you have a problem with Sybrand please deal with it off-line.
um.. no.