使用INSTEAD OF DELETE触发器试试:
create trigger trg_delete_table on table
instead of delete
as
delete table from table as a inner join deleted as d on a.id = d.id where d.id <> 1
GO
使用你的那个触发器:
CREATE trigger tr_delete_Test2
on Test2
for delete
as
set nocount on
if exists (select 1 from deleted where id=0)
begin
RAISERROR ('不能删除id=0的记录', 16, 1)
ROLLBACK TRANSACTION
end
之后在存储过程中这么用:
create proc dbo.deltest2
as
delete from test2
if @@error<>0
delete from test2 where id>0