Home » SQL & PL/SQL » SQL & PL/SQL » DELETING of RECORDS simultaneously.
DELETING of RECORDS simultaneously. [message #637604] |
Wed, 20 May 2015 23:51 |
|
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 |
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 |
|
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 #637631 is a reply to message #637613] |
Thu, 21 May 2015 03:34 |
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.
|
|
|
|
Goto Forum:
Current Time: Tue Mar 19 06:28:52 CDT 2024
|