Home » SQL & PL/SQL » SQL & PL/SQL » DELETING of RECORDS simultaneously.
DELETING of RECORDS simultaneously. [message #637604] Wed, 20 May 2015 23:51 Go to next message
saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai
Junior Member
HI All,

I need to delete the records from one table it has to delete the records in other table and vies versa.

here are some example tables
create table t1(id number,
                code varchar2(10),
                posi varchar2(30),
                name varchar2(50));
insert into t1 values (1, '6aot3', 'aaaa', 'mark');
insert into t1 values (2, '6aom7', '444h', 'phils');
insert into t1 values (5, '6ao73', 'trex', 'bills');
insert into t1 values (9, '6yut3', 'aa765', 'mark');

create table t2(id number,
                code varchar2(10),
                posi varchar2(30),
                name1 varchar2(50));
insert into t2 values (1, '6aot3', 'aaaa', 'mark1');
insert into t2 values (2, '6aom7', '444h', 'phils1');
insert into t2 values (5, '6ao73', 'trex', 'bills1');
insert into t2 values (9, '6yut3', 'aa765', 'mark1');


in this above table if i delete record from t1 it has to be deleted in t2
and if i delete the record from t2 it has to delete from t1.

i have tried with keeping the triggers on both the tables but it leads to dead lock

create or replace trigger t1_del
  AFTER delete on t1
  FOR each row
BEGIN
  IF deleting then
  
    DELETE from t2
     WHERE id = :old.id
       AND code = :old.code
       AND posi = :old.posi;
  END if;
END;


create or replace trigger t2_del
  AFTER delete on t2
  FOR each row
BEGIN
  IF deleting then
  
    DELETE from t1
     WHERE id = :old.id
       AND code = :old.code
       AND posi = :old.posi;
  END if;
END;


Tnx in advance

[Updated on: Wed, 20 May 2015 23:53]

Report message to a moderator

Re: DELETING of RECORDS simultaneously. [message #637605 is a reply to message #637604] Thu, 21 May 2015 00:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
No need of triggers, just add a foreign key to the child table as ON CASCADE DELETE.

Why would you have two identical tables? Seems like a bad design. You need two tables as a parent-child relation.

For example,

SQL> DROP TABLE t2 PURGE;

Table dropped.

SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL>
SQL> create table t1(id number,
  2                  code varchar2(10),
  3                  posi varchar2(30),
  4                  name varchar2(50));

Table created.

SQL> insert into t1 values (1, '6aot3', 'aaaa', 'mark');

1 row created.

SQL> insert into t1 values (2, '6aom7', '444h', 'phils');

1 row created.

SQL> insert into t1 values (5, '6ao73', 'trex', 'bills');

1 row created.

SQL> insert into t1 values (9, '6yut3', 'aa765', 'mark');

1 row created.

SQL>
SQL> ALTER TABLE t1 ADD CONSTRAINT id1_pk PRIMARY KEY(ID);

Table altered.

SQL>
SQL> create table t2(id number,
  2                  code varchar2(10),
  3                  posi varchar2(30),
  4                  name1 varchar2(50));

Table created.

SQL> insert into t2 values (1, '6aot3', 'aaaa', 'mark1');

1 row created.

SQL> insert into t2 values (2, '6aom7', '444h', 'phils1');

1 row created.

SQL> insert into t2 values (5, '6ao73', 'trex', 'bills1');

1 row created.

SQL> INSERT INTO t2 VALUES (9, '6yut3', 'aa765', 'mark1');

1 row created.

SQL>
SQL> --ALTER TABLE t2 ADD CONSTRAINT id2_pk PRIMARY KEY(ID);
SQL> ALTER TABLE t2
  2  ADD CONSTRAINT id2_fk
  3     FOREIGN KEY (ID)
  4     REFERENCES t1 (id)
  5     ON DELETE CASCADE;

Table altered.

SQL>
SQL> SELECT ID FROM t1;

        ID
----------
         1
         2
         5
         9

SQL> SELECT ID FROM t2;

        ID
----------
         1
         2
         5
         9

SQL> DELETE FROM t1 WHERE ID =1;

1 row deleted.

SQL> SELECT ID FROM t1;

        ID
----------
         2
         5
         9

SQL> SELECT ID FROM t2;

        ID
----------
         2
         5
         9

SQL>
Re: DELETING of RECORDS simultaneously. [message #637613 is a reply to message #637605] Thu, 21 May 2015 02:40 Go to previous messageGo to next message
saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai
Junior Member
HI Lalit,

Tnx for replay.

reg bad design these are sample tables.
sorry, i forgotten to mention earlier that table t1 contain some duplicates your solution is working fine for unique records
so can you help me with these tables.


drop table t1 cascade constraints purge;
drop table t2 cascade constraints purge;

create table t1(id number,
                code varchar2(10),
                posi varchar2(30),
                name varchar2(50),
                org1 number,
                org2 number);
insert into t1 values (1, '6aot3', 'aaaa', 'mark',23,24);
insert into t1 values (1, '6aot3', 'aaaa', 'mark',22,27);
insert into t1 values (2, '6aom7', '444h', 'phils',24,27);
insert into t1 values (2, '6aom7', '444h', 'phils',25,27);
insert into t1 values (5, '6ao73', 'trex', 'bills',37,45);
insert into t1 values (5, '6ao73', 'trex', 'bills',37,46);
insert into t1 values (9, '6yut3', 'aa765', 'mark',33,67);


create table t2(id number,
                code varchar2(10),
                posi varchar2(30),
                name1 varchar2(50),rate number);
insert into t2 values (1, '6aot3', 'aaaa', 'mark1',10);
insert into t2 values (2, '6aom7', '444h', 'phils1',100);
insert into t2 values (5, '6ao73', 'trex', 'bills1',1000);
insert into t2 values (9, '6yut3', 'aa765', 'mark1',10000);


sorry and tnx in advance.
Re: DELETING of RECORDS simultaneously. [message #637621 is a reply to message #637613] Thu, 21 May 2015 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I should say: fix the design, information should not be duplicated.

Re: DELETING of RECORDS simultaneously. [message #637624 is a reply to message #637621] Thu, 21 May 2015 02:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Michel Cadot wrote on Thu, 21 May 2015 00:54

I should say: fix the design, information should not be duplicated.



+1
Re: DELETING of RECORDS simultaneously. [message #637631 is a reply to message #637613] Thu, 21 May 2015 03:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
saipavan.plsql wrote on Thu, 21 May 2015 13:10

reg bad design these are sample tables.
sorry, i forgotten to mention earlier that table t1 contain some duplicates your solution is working fine for unique records
so can you help me with these tables.


I gave you a direction. As it has been told to you, fix the design. Read normalization.
Re: DELETING of RECORDS simultaneously. [message #637632 is a reply to message #637624] Thu, 21 May 2015 03:41 Go to previous message
naga.oracle
Messages: 6
Registered: August 2014
Location: india
Junior Member

Yes, you should fix the design as Michel Cadot & BlackSwan suggests....

try if it help you...

1) create a view on these tables(t1,t2)
2) then create instead of trigger on it
3) delete from view.

create or replace view vw as
select id,code,posi,name from t1
union all
select id,code,posi,name1 from t2;



create or replace trigger trg_view
istead of delete on vw
begin
delete from t1 where id=:old.id
and code=:old.code
and posi=:old.posi;
delete from t2 where id=:old.id
and code=:old.code
and posi=:old.posi;
end;
/


delete from vw where id=1;
Previous Topic: Date validation
Next Topic: Error using INTO clause in execute immediate
Goto Forum:
  


Current Time: Tue Mar 19 06:28:52 CDT 2024