Skip to Main Content
  • Questions

  • Delete a record at the at end when others stage

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sanjay.

Asked: September 18, 2017 - 2:14 pm UTC

Last updated: September 18, 2017 - 4:26 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

I have a procedure which writes a record each in two tables. Want to delete the record inserted into the first table if the second table insert fails. However, the delete does not happen. The problem is that the before insert trigger in the second table sometimes fails. Could that be the reason the procedure's exception handling does not get a chance to delete the record from the first table?

Any suggestions?

Pseudo-code (hope this clearly defines the problem):

...

  insert into table_1 ( ...
  commit ;       -- Needed as this table's primary key is referenced as a foreign key by the second table
  v_flag_first_table_inserted := 'Y' ;    -- Initially set as 'n'

  insert into table_2 ( ...
  commit ;
  v_flag_second_table_inserted := 'Y' ;    -- Initially set as 'n'

  EXCEPTION
    when others then
    pr_internal_delete_first_table_if_needed ;
    raise_application_error ( ...

...
 
     pr_internal_delete_first_table_if_needed is begin  -- DEFINED EARLIER IN THE PROCEDURE
       if ( v_flag_first_table_inserted  = 'Y' and 
            v_flag_second_table_inserted = 'n' ) then
         delete table_1 where table_1_pk = ...
         commit:
       end if ;
     end ;

and Chris said...

So if you get an error you just want to undo the inserts before it? If so, you're overcomplicating this!

All you need to do is rollback.

Or, if you're calling this from the client, nothing.

Due to statement level atomicity, Oracle Database rolls back all the work done by the top-level call when an exception is raised:

create table t1 (
  x int primary key
);
create table t2 (
  x int primary key
);

insert into t2 values (1);
commit;

select * from t1;

no rows selected

select * from t2;

X  
1  

create or replace procedure p ( p int ) as
begin
  insert into t1 values (p);  
  insert into t2 values (p);  
end p;
/

exec p(1);

ORA-00001: unique constraint (CHRIS.SYS_C0052933) violated

select * from t1;

no rows selected

select * from t2;

X  
1

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rollback won't work ...

Sanjay Banerji, September 18, 2017 - 3:55 pm UTC

Rollback won't work.

The first table's primary key (PK) is a foreign key (FK) to the second table. Therefore, a commit is mandatory after the first table insert.

Am alternative is to temporarily disable the foreign key - but that IMHO is strongly dis-advised.
Chris Saxon
September 18, 2017 - 4:26 pm UTC

"Therefore, a commit is mandatory after the first table insert."

What?!

This makes no sense to me. There is zero technical need to commit a parent insert before adding a child in the same session:

create table t1 (
  x int primary key
);
create table t2 (
  x int references t1 (x)
);

insert into t1 values (1);
insert into t2 values (1);

select * from t1;

X  
1 

select * from t2;

X  
1


Get rid of the commit and let rollback do the work for you!

FK's does not require commit

Vidar, September 18, 2017 - 4:24 pm UTC

Sanjay,

There's no need to commit just because there's a foreign key present. That's the whole idea about transactions and relational databases...
-- Creating two tables with a foreign key t2.y->t1.x
create table vei_t1 (
  x int primary key
);
create table vei_t2 (
  x int primary key,
  y int not null,
  constraint t2_t1_fk foreign key (y) references vei_t1(x)
);

insert into vei_t1 values (1);
insert into vei_t2 values(1,1);
-- See: No commit!
-- This one will fail du to a missing value in t1
insert into vei_t2 values(2,2);

Chris Saxon
September 18, 2017 - 4:26 pm UTC

Indeed...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library