--要不这样???
--测试环境
create table a
(
a int primary key
)
go
create table b
(
a int primary key
)
go
create table c
(
id int identity primary key,
a int ,
constraint fk_a foreign key (a) references a(a) on delete cascade ,
constraint fk_b foreign key (a) references b(a) on delete cascade
)
go--测试数据
insert a values(1)
insert a values(2)
insert a values(3)
insert b values(1)
insert b values(2)
insert b values(3)
insert c values(1)
insert c values(2)
insert c values(3)
select * from a
select * from b
select * from c
go
--测试
delete from a where a = 1
select * from a
select * from b
select * from c
go
delete from b where a = 2
select * from a
select * from b
select * from c
go
--测试环境
create table a
(
a int primary key
)
go
create table b
(
a int primary key
)
go
create table c
(
id int identity primary key,
a int ,
constraint fk_a foreign key (a) references a(a) on delete cascade ,
constraint fk_b foreign key (a) references b(a) on delete cascade
)
go--测试数据
insert a values(1)
insert a values(2)
insert a values(3)
insert b values(1)
insert b values(2)
insert b values(3)
insert c values(1)
insert c values(2)
insert c values(3)
select * from a
select * from b
select * from c
go
--测试
delete from a where a = 1
select * from a
select * from b
select * from c
go
delete from b where a = 2
select * from a
select * from b
select * from c
go
解决方案 »
- sql2000好象除了union外,没有象oralce一样的差集和交集(minus intersect)?
- 对于未知字段的查找,有无人知?
- 高分求解
- 请教一个跟排序有关的算法
- 向各位高手请教:数据库事务进程间死锁问题。
- [DBNETLIB][ConnectionOpen (Connect()).]SQL Server!特别奇怪的问题!!!只要笔记本的无线关闭就出这错误!链接到网络上时没有错误!
- 急需技术经理,高薪诚聘,详情请见我公司CSDN招聘页面http://job.csdn.net/Jobs/de9a50727e4d45cfa5002085f4c4a84b/ViewCompany.aspx
- Oracle的存储过程如何转成SQL Server存储过程
- 查询的逻辑分析问题。。
- 关于两个数据项的模糊比较
- 求一sql
- 关于两个表的复制问题
create table a (a int primary key)
go--创建c表
create table c(c int primary key)
go--创建b表
create table b(a int not null,b int,c int not null
,constraint fk_b_a foreign key (a) references a(a) on delete cascade --a与表a的a关联
,constraint fk_b_c foreign key (c) references c(c) on delete cascade --c与表c的c关联
)go
drop table b,a,c
create table a (a int primary key)
go--创建c表
create table c(c int primary key)
go--创建b表
create table b(a int not null,b int,c int not null
,constraint fk_b_a foreign key (a) references a(a) on delete cascade --a与表a的a关联
,constraint fk_b_c foreign key (c) references c(c) on delete cascade --c与表c的c关联
)
go--插入测试数据
insert into a
select 1
union all select 2
union all select 3
goinsert into c
select 11
union all select 22
goinsert into b(a,c,b)
select 1,11,1
union all select 1,22,2
union all select 2,22,3
union all select 2,11,4
union all select 3,11,5
union all select 3,22,6
go--显示表的内容
select a.*,c.*
from b
join a on a.a=b.a
join c on c.c=b.c
go--删除a表内容测试
delete from a where a=1
go--显示表的内容
select a.*,c.*
from b
join a on a.a=b.a
join c on c.c=b.c
go--删除c表内容测试
delete from c where c=22
go--显示表的内容
select a.*,c.*
from b
join a on a.a=b.a
join c on c.c=b.c--删除测试环境
drop table b,a,c/*--测试结果a c
----------- -----------
1 11
1 22
2 22
2 11
3 11
3 22(所影响的行数为 6 行)
(所影响的行数为 1 行)a c
----------- -----------
2 22
2 11
3 11
3 22(所影响的行数为 4 行)
(所影响的行数为 1 行)a c
----------- -----------
2 11
3 11(所影响的行数为 2 行)
--*/
create trigger t_delete on a
for delete
delete b from b join deleted d on b.a=d.a
go--创建维护c,b之间关系的删除触发器
create trigger t_delete on c
for delete
delete b from b join deleted d on b.c=d.c
go