27,579
社区成员
发帖
与我相关
我的任务
分享
create trigger tri_d
on borrow
instead of delete
declare @bookid int
select @bookid=bookid from deleted
delete from borrow where bookid=@bookid
delete from book where bookid=@bookid
当你执行:
delete from book where bookid=1时,就会删除掉borrow中bookid=1与book表中id=1的数据库
--->>>>TravyLee生成测试数据
if object_id('test')is not null
drop table test
go
create table test(
id int primary key,
value varchar(10)
)
go
insert test(id,value)
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3' union all
select 4,'test4' union all
select 5,'test5'
go
if object_id('tbl')is not null
drop table tbl
go
create table tbl(
id int foreign key references test(id)
on delete cascade --指定级联删除
on update cascade, --指定级联更新
value varchar(5)
)
go
insert tbl
select 1,'true' union all
select 2,'false' union all
select 3,'false' union all
select 4,'true' union all
select 5,'false'
go
delete from test where value='test2'
select * from test
/*
test数据
-------------------------
id value
1 test1
3 test3
4 test4
5 test5
-------------------------
tbl数据
--------------------------
id value
1 true
3 false
4 true
5 false
*/
--级联删除
create table ta(id int not null primary key)
insert ta
select 1
create table tb(id int foreign key references ta(id) on delete cascade)
insert tb
select 1
select * from ta
select * from tb
delete ta
select * from ta
select * from tb
drop table tb
drop table ta