触发器的问题

[ukyo88888]

例如我有一个表test

id
name
num

我想在删除某条记录的时候进行一定规则的验证或者计算过后,看是否符合某种条件,再决定是否可以删除,我应该怎么做!!

请给出一些实例

[jiangsmile]

楼主，上面的一段改一下。再试试：
begin
select @cun=count(*) from test where id=@sn_del
if @cun>0
begin
delete test where operatorid=@sn_del
end


end

[jiangsmile]

不好意思，不是operatorid ,应该是你自己的号
begin
select @cun=count(*) from test where id=@sn_del
if @cun>0
begin
delete test where id=@sn_del
end

end

[ukyo88888]

还是不行

触发器是不是删除了数据过后再触发的触发器中的事件哦!!

能不能给一个完整的数据库结构和一个触发器的实例哦!!

万分感谢!!

[pbsql]

--删除触发器例子：
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
go

create 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

[janmie]

AFTER 触发器在触发操作（INSERT、UPDATE 或 DELETE）后和处理完任何约束后激发。可通过指定 AFTER 或 FOR 关键字来请求 AFTER 触发器。因为 FOR 关键字与 AFTER 的效果相同，所以具有 FOR 关键字的触发器也归类为 AFTER 触发器。


INSTEAD OF 触发器代替触发动作进行激发，并在处理约束之前激发。

有个疑问，既然是要验证是否满足条件才觉得是否删除，那为什么不直接用select？

如果真要用触发器，那不是要建instead of trigger，然后在判断是否要删除，不是多此一举了？

菜鸟说的话要是错了请见谅！

[janmie]

学习！

[jiangsmile]

给你个事例，将满足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=''
end

else


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
end

end

end

























GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[ukyo88888]

我说的好像不是这个意思
我重新表达一下

id name num
1 ukyo 32
2 xh 43
3 ux 52
我想在删除某条记录的时候进行验证,如果num>40就可以删除,小于就不能删除

[jiangsmile]

一样的啊，你把判断条件变为num>40就好了啊.
select @sn_del=id from deleted del where num>40
select @sn_ins=id from inserted ins where num>40

[jiangsmile]

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 num>40
select @sn_ins=id from inserted ins where num>40





if (@sn_del='' and @sn_ins='' )
begin
set @temp=''
end

else


begin
select @cun=count(*) from test where id=@sn_ins
if @cun>0
begin
delete test where operatorid=@sn_ins

end


end

[ukyo88888]

好像不行,我刚才试了一下.

[yown]

用替代触发器

CREATE TRIGGER [test_trigger] ON [dbo].[test]
INSTEAD OF DELETE
as
...

[jiangsmile]

风云的简洁，应该可以了。我这里测试通过了。