CODEWITHSUNDEEP
sqloracle-database
Hongwei Li
Hongwei Li

Reputation: 79

Oracle PL/SQL How to rollback the newly inserted row

create or replace trigger trig_redeem_coffee
before insert
on buycoffee
for each row
declare
CID int;
customerPoint float;
pointNeeded float;
begin
    select customer_id into CID
    from purchase
    where purchase_id = :new.purchase_id;
    select total_points into customerPoint
    from customer
    where customer_id = CID;
    pro_get_redeem_point (:new.coffee_ID, :new.redeem_quantity, pointNeeded);
    if pointNeeded>customerPoint
    then
        rollback;
    else
        pointNeeded := -1*pointNeeded;
        pro_update_point(CID, pointNeeded);
        end if;
    commit;
end;
/

The trigger can be successfully created, but when I insert into buycoffee table(it will meet the condition that pointNeeded>customerPoint), it returns an error that it cannot rollback in a trigger. Is this a proper way to rollback a newly inserted row? Or is there any better way to do it. (all procedures are built properly)

Upvotes: 0

Views: 4807

Answers (1)

Mark J. Bobak
Mark J. Bobak

Reputation: 14403

You cannot COMMIT or ROLLBACK inside of a TRIGGER, unless it's an autonomous transaction.

Inside your TRIGGER, you should do whatever logic you wish to apply, but if you reach an error condition, you should raise an application error, rather than ROLLBACK. That should cause the INSERT statement that fired the TRIGGER to error, doing a statement level rollback, and return your transaction to the state it was just before you executed the INSERT. At that point, you can evaluate the error, decide whether to rollback the entire transaction, or re-try the INSERT, or something else.

More on autonomous transactions: https://docs.oracle.com/database/121/CNCPT/transact.htm#GUID-C0C61571-5175-400D-AEFC-FDBFE4F87188

More on statement-level rollback: https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm#i8072

Upvotes: 3

Related Questions