表test
id name age check(1表示未审核,3表示审核)
1 a 11 1
2 b 13 1
3 c 14 3
4 d 15 1
5 e 16 3
要求删除数据时触发(执行多条删除语句)比如
delete from test
这时候要判断,只有check=1的数据删除,其他的数据回滚
注意,是回滚rollback tran,
不是这样写,
insert into b (select * from deleted where delete.check=1)应为我要id的值不变,那是自动编号的,
id name age check(1表示未审核,3表示审核)
1 a 11 1
2 b 13 1
3 c 14 3
4 d 15 1
5 e 16 3
要求删除数据时触发(执行多条删除语句)比如
delete from test
这时候要判断,只有check=1的数据删除,其他的数据回滚
注意,是回滚rollback tran,
不是这样写,
insert into b (select * from deleted where delete.check=1)应为我要id的值不变,那是自动编号的,
for delete
as
if exists (select 1 from deleted where check <>1 )
begin
rollback tran
end
---------------------
删除全部的话,那是一个事务,成功就全部删除,不然全部不删除。
这个事务的特性。
如果你想实现你的功能,为什么不另外想方法
看看ls有什么办法
create trigger t_test on test
for delete
as
if exists (select * from deleted where [check] <>1 )
begin
rollback tran
raiserror('触发器:check不等于1的不能删除',16,1)
end
go
只要删除数据时,不用 truncate table就可以回滚
instead of delete
as
if exists (select * from deleted where [check] <>1 )
begin
raiserror('触发器:check不等于1的不能删除',16,1)
end
else
delete from test where exists(select * from deleted where [check] =1)
go
if exists (select * from deleted where [check] <>1 )
begin
set identity_insert abc on
insert into abc(列名1,列名2,...,列名N)
select 列名1,列名2,...,列名N from deleted where [check]<>1
set identity_insert abc off
end
for delete
as
if exists (select * from deleted where [check] <>1 )
begin
set identity_insert test on
insert into abc(id,name,age,check)
select id,name,age,check from deleted where [check]<>1
set identity_insert test off
end
go
for delete
as
if exists (select 1 from deleted where [check] <>1 )
begin
rollback tran
raiserror('触发器:check不等于1的不能删除',16,1)
end
go