[在线结贴] 求写个触发器，监视DELETE? 若SQL的删除ID=999，则拒绝或跳过执行该SQL。

[zhangzhe_893]

[在线结贴] 求写个触发器，监视DELETE? 若SQL的删除ID=999，则拒绝或跳过执行该SQL。

[guostong]

CREATE TRIGGER tri_t_test ON dbo.t_test INSTEAD OF DELETE AS BEGIN DELETE t_test FROM DELETED D INNER JOIN t_test T ON T.userid = D.userid and t.userid <> 999 END

[itliyi]

if (object_id('tgr_classes_delete', 'TR') is not null)
    drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on tb
    for delete --删除触发
as
    declare @id int
    select @id=ID from deleted
    if(@id=999)
		insert into tb(ID,...)select ID... from deleted
go

[唐诗三百首]

-- 建测试表
create table zh(ID int)

insert into zh(ID)
 select 1 union all
 select 2 union all
 select 999


-- 建触发器
create trigger tr_zh on zh
for delete
as
begin
 if exists(select 1 from deleted where ID=999)
 begin
   rollback tran
 end
end


-- 测试1
delete from zh where ID=2

select ID from zh
/*
ID
-----------
1
999

(2 row(s) affected)
*/


-- 测试2
delete from zh where ID=999
/*
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/

select ID from zh
/*
ID
-----------
1
999

(2 row(s) affected)
*/

[itliyi]

if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[aa] varchar(1))
insert into tb([id],[aa])
select 111,'a' union all
select 222,'a' union all
select 333,'b' union all
select 444,'b' union all
select 777,'c' union all
select 888,'c' union all
select 999,'c' 



if (object_id('tgr_classes_delete', 'TR') is not null)
    drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on tb
    for delete --删除触发
as
    declare @id int
    select @id=[id] from deleted
    if(@id=999)
		insert into tb([id],[aa])select id,aa from deleted
go

delete tb where id=999

[xdashewan]

楼主，你应用程序能修改吗，虽然触发器可行，但这种用法真心奇葩。如果应用程序能改，建议在数据库增加一列标志位，用来标示是否可以删除（1：可删 0：不可删），在应用程序执行delete的时候加上条件标志位=1，数据999那条的标志位设为0，就永远删不掉了

[xdashewan]

引用 3 楼 ap0405140 的回复:

a版的方法对于单挑删除的确正确，但如果是区间段删除则会导致全部回滚，灵活度不够

[幸运的意外]

create trigger triggername on tablename for delete as begin if exists(select 1 from deleted where ID=999) begin rollback tran end end

[sqlkxr]

create table test2
(id int)

insert into test2 select 1 union all select 2 union all select 3 union all select 999  union all select 999


create trigger trg_999 on test2  instead of delete
as
   
    delete a  from test2 a join deleted b on a.id=b.id where b.id<>999