给你个事例,将满足name='jz'的记录删除
CREATE TRIGGER [test_trigger] ON [dbo].[test]
FOR DELETE
AS
begin
set XACT_ABORT on
declare @sn_del varchar(20),@sn_ins varchar(20),@cun as int,@temp varchar(20)
set @sn_del=''
set @sn_ins=''select @sn_del=id from deleted del where name='jz'
select @sn_ins=id from inserted ins where name='jz'if (@sn_del='' and @sn_ins='' )
begin
set @temp=''
endelse
begin
select @cun=count(*) from test where id=@sn_ins
if @cun>0
begin
delete test where operatorid=@sn_ins
end
else
begin
delete test where operatorid=@sn_del
endend endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [test_trigger] ON [dbo].[test]
FOR DELETE
AS
begin
set XACT_ABORT on
declare @sn_del varchar(20),@sn_ins varchar(20),@cun as int,@temp varchar(20)
set @sn_del=''
set @sn_ins=''select @sn_del=id from deleted del where name='jz'
select @sn_ins=id from inserted ins where name='jz'if (@sn_del='' and @sn_ins='' )
begin
set @temp=''
endelse
begin
select @cun=count(*) from test where id=@sn_ins
if @cun>0
begin
delete test where operatorid=@sn_ins
end
else
begin
delete test where operatorid=@sn_del
endend endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
我重新表达一下id name num
1 ukyo 32
2 xh 43
3 ux 52
我想在删除某条记录的时候进行验证,如果num>40就可以删除,小于就不能删除
select @sn_del=id from deleted del where num>40
select @sn_ins=id from inserted ins where num>40
FOR DELETE
AS
begin
set XACT_ABORT on
declare @sn_del varchar(20),@sn_ins varchar(20),@cun as int,@temp varchar(20)
set @sn_del=''
set @sn_ins=''select @sn_del=id from deleted del where num>40
select @sn_ins=id from inserted ins where num>40if (@sn_del='' and @sn_ins='' )
begin
set @temp=''
endelse
begin
select @cun=count(*) from test where id=@sn_ins
if @cun>0
begin
delete test where operatorid=@sn_ins
end
end
INSTEAD OF DELETE
as
...
begin
select @cun=count(*) from test where id=@sn_del
if @cun>0
begin
delete test where operatorid=@sn_del
end
end
begin
select @cun=count(*) from test where id=@sn_del
if @cun>0
begin
delete test where id=@sn_del
end end
create table t(id int,name varchar(10),num int)
insert into t(id,name,num)
select 1, 'ukyo', 32 union all
select 2, 'xh', 43 union all
select 3, 'ux', 52
gocreate trigger [tr_delete] on t
for delete
AS
if exists(select * from deleted where num<=40)
begin
rollback transaction
raiserror('num<=40,不允许删除!!!',16,1)
end
INSTEAD OF 触发器代替触发动作进行激发,并在处理约束之前激发。 有个疑问,既然是要验证是否满足条件才觉得是否删除,那为什么不直接用select?如果真要用触发器,那不是要建instead of trigger,然后在判断是否要删除,不是多此一举了?菜鸟说的话要是错了请见谅!