要求在oracle触发器中实现:
例如 有3个talbe:
A(字段有A_id,B_id);A_id为主键
B(字段有B_id,C_id);B_id为主键
C(字段有C_id,D_id);C_id为主键现在已知D_id的值,要删除c表里面含有D_id的记录,根据3个table的关联性,同时删除B表记录和c表记录的
我想用这样实现:SELECT a.A_id into Aid from t00hz009 a where a.B_id in (
select b.B_id into Bid from t00hz008 b where b.C_id in (
SELECT c.C_id into Cid FROM T00HZ007 c WHERE c.D_id =D_id ;
)
)
Aid、Bid、Cid 该如何定义了?
定义好了后怎么for循环删除?请帮忙提供多种解法。
例如 有3个talbe:
A(字段有A_id,B_id);A_id为主键
B(字段有B_id,C_id);B_id为主键
C(字段有C_id,D_id);C_id为主键现在已知D_id的值,要删除c表里面含有D_id的记录,根据3个table的关联性,同时删除B表记录和c表记录的
我想用这样实现:SELECT a.A_id into Aid from t00hz009 a where a.B_id in (
select b.B_id into Bid from t00hz008 b where b.C_id in (
SELECT c.C_id into Cid FROM T00HZ007 c WHERE c.D_id =D_id ;
)
)
Aid、Bid、Cid 该如何定义了?
定义好了后怎么for循环删除?请帮忙提供多种解法。
delete from a where b_id in (
select b_id from b where c_id in (
select c_id from c where d_id=xxx))delete from b where c_id in (
select c_id from c where d_id=xxx)delete from c where d_id=xxx
2 (c_id number(10) primary key,
3 d_id number(10),
4 name varchar2(10));
Table created
SQL> create table tableb
2 (b_id number(10) primary key,
3 c_id number(10) ,
4 name varchar2(10));
Table created
SQL> alter table TABLEB
2 add constraint TABLEB_CHECK1 foreign key (C_ID)
3 references TABLEC (C_ID) on delete cascade;
Table altered
SQL> create table tablea
2 (a_id number(10) primary key,
3 b_id number(10) ,
4 name varchar2(10));
Table created
SQL> alter table TABLEA
2 add constraint TABLEA_CHECK1 foreign key (B_ID)
3 references TABLEB (B_ID) on delete cascade;
Table altered
SQL> insert into tablec values(1,1,'test');
1 row inserted
SQL> insert into tableb values(1,1,'test');
1 row inserted
SQL> insert into tablea values(1,1,'test');
1 row inserted
SQL> commit;
Commit complete
SQL> delete from tablec;
1 row deleted
SQL> commit;
Commit complete
SQL> select * from tableb;
B_ID C_ID NAME
----------- ----------- ----------
SQL> select * from tablea;
A_ID B_ID NAME
----------- ----------- ----------
SQL>
create or replace trigger delete_c_trg
after delete on c
for each row
declare
cursor cur is select b_id from b where c_id=:old.c_id for update;
id b.b_id%type;
begin
open cur;
loop
fetch cur into id;
exit when cur%notfound;
delete from a where b_id=id;
delete from b where current of cur;
end loop;
close cur;
end;
/
DROP TABLE B;
DROP TABLE C;
CREATE TABLE C (c_id NUMBER PRIMARY KEY, d_id NUMBER);
CREATE TABLE B (b_id NUMBER PRIMARY KEY, c_id NUMBER constraint fk_c references c (c_id) ON DELETE CASCADE);
CREATE TABLE A (a_id NUMBER PRIMARY KEY, b_id NUMBER constraint fk_b references b (b_id) ON DELETE CASCADE);INSERT INTO C (c_id, d_id) VALUES (1, 1);
INSERT INTO C (c_id, d_id) VALUES (2, 2);
INSERT INTO C (c_id, d_id) VALUES (3, 1);INSERT INTO B (b_id, c_id) VALUES (1, 1);
INSERT INTO B (b_id, c_id) VALUES (2, 3);
INSERT INTO B (b_id, c_id) VALUES (3, 2);INSERT INTO A (a_id, b_id) VALUES (1, 1);
INSERT INTO A (a_id, b_id) VALUES (2, 1);
INSERT INTO A (a_id, b_id) VALUES (3, 3);COMMIT;DELETE FROM C WHERE d_id = 1;COMMIT;
这个简单啊。我只要建立个外键,用一个sql就ok了。我收藏了!
外键的级联删除便可 on delete cascade 不需要触发器