create trigger tr_tablea_delete
on tablea
for delete
as
if exists (select * from deleted where bitfield=1)
begin
RAISERROR ('Some record bitfield=1,Can not to delete.', 16, 1)
ROLLBACK TRANSACTION
end
go
on tablea
for delete
as
if exists (select * from deleted where bitfield=1)
begin
RAISERROR ('Some record bitfield=1,Can not to delete.', 16, 1)
ROLLBACK TRANSACTION
end
go
delete tablea
from deleted
where tablea.标识键 = deleted.标识键 and bit = 0
触发器和删除操作位于同一个事务,如果用游标判断或者其他方法,势必影响性能。
可以试一下以下触发器,性能影响影响不敢保证,但是必用游标判断好:create trigger tr_tablea_delete
on tablea
for delete
as
insert tablea select * from deleted where bitfield=1
go
instead of 是最好的解决方法,不过用户可能会反映不直观,有些记录明明删除了,但是还再。