--你可以在t2的h列上建立外键约束t1中的主键h,设定on delete CASCADE选项. alter table t2 add constraint FK_T2_T1 foreign key(k) references t1(h) --这样当你删除t1的h列上的值的时候,就可以删除对应的t2表上的字段.--第二个的实现方法就只能用触发器来实现了. create trigger tri_t2 on t2 for delete as delete from t1 where h in(select h from deleted) delete from t2 where h in(select h from deleted)
--测试 create table t1(id int primary key,name varchar(10)) create table t2(sid int,id int references t1(id) on delete cascade) --建立外键约束 insert t1 --插入数据 select 1,'aa' union select 2,'bb' union select 3,'dd' insert t2 select 1001,1 union select 1002,1 union select 1003,2 union select 1004,3delete from t1 where id=2 /* sid id ----------- ----------- 1001 1 1002 1 1004 3(所影响的行数为 3 行)id name ----------- ---------- 1 aa 3 dd(所影响的行数为 2 行) */ --创建触发器 create trigger tri_t2 on t2 for delete as delete from t1 where id in(select id from deleted) delete from t2 where id in(select id from deleted) go delete from t2 where id=1 /* id name ----------- ---------- 3 dd(所影响的行数为 1 行)sid id ----------- ----------- 1004 3(所影响的行数为 1 行) */--测试成功
t1中h为唯一的,而t2中h可以重复出现,也就是同一个h可以出现多次,现要
一、删除t1中记录时,t2中对应所有h等同的记录都要删除掉,
二、删除t2中记录时,当然也要同时把t1中h等同的记录给删掉,但还要让t2中其余与当前要删除的记录h等同的记录(t2中h可以重复)也要同时删除掉
请问这该如何实现?
on t2 for delete
as
delete from t1 where h in(select h from deleted)
delete from t2 where h in(select h from deleted)
create table t1(id int primary key,name varchar(10))
create table t2(sid int,id int references t1(id) on delete cascade) --建立外键约束
insert t1 --插入数据
select 1,'aa'
union select 2,'bb'
union select 3,'dd'
insert t2
select 1001,1
union select 1002,1
union select 1003,2
union select 1004,3delete from t1 where id=2
/*
sid id
----------- -----------
1001 1
1002 1
1004 3(所影响的行数为 3 行)id name
----------- ----------
1 aa
3 dd(所影响的行数为 2 行)
*/
--创建触发器
create trigger tri_t2
on t2 for delete
as
delete from t1 where id in(select id from deleted)
delete from t2 where id in(select id from deleted)
go
delete from t2 where id=1
/*
id name
----------- ----------
3 dd(所影响的行数为 1 行)sid id
----------- -----------
1004 3(所影响的行数为 1 行)
*/--测试成功