SQL 2005中的触发器 [英] triggers in sql 2005
本文介绍了SQL 2005中的触发器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何为插入的多行激活触发器.
我定义了一个仅可插入1行的触发器,如下所示:
how to activate triggers for multiple rows inserted.
i have defined a trigger which work for only 1 row inserted which is as follows:
create trigger userid
on users
after insert
as
DECLARE @name varchar(50), @id int
begin
select @id= (select Max(id) from users)
select @uname = uname from inserted
update users set id = id +1 where uname = @uname
end
请编辑此触发器,以便它适用于插入的多行.
或举任何合适的例子.
--------------------------------
[后来添加]案例2:
please edit this trigger so that it can work for multiple rows inserted.
or give any suitable example.
--------------------------------
[Later addition] Case 2:
create trigger manage_mail
on mymail
after delete
as
DECLARE @count_references int,@mail_id varchar(20)
begin
select @mail_id=mail_id from deleted
select @count_references=count(*) from mymail where
mail_id=@mail_id
if(@count_references =0)
begin
delete from mails where mail_id = @mail_id
end
end
推荐答案
每个用户自己.不要这样而是,为ID
列定义IDENTITY
.有关该信息的更多信息,请参见: IDENTITY(属性)(Transact-SQL) [ ^ ].
通过使用IDENTITY,管理系统会自动为每个新用户提供唯一的值.您还可以将此值用作表的主键.
应该修改触发器本身,以便例如在插入的表中的每一行使用游标并相应地更新用户表,但是如上所述,这是错误的方法.
It looks like you''re trying to generate a unique id for each user by yourself. Don''t do this. Instead, defineIDENTITY
forID
column. For more info on that, see: IDENTITY (Property) (Transact-SQL)[^].
By using IDENTITY the management system gives automatically a unique value for each new user. You can also use this value as the primary key for the table.
The trigger itself should be modified so that you would for example use a cursor for each row in inserted table and update the users table correspondingly, but as said, this would be a wrong way to go.
好,我要添加一个新的解决方案,因为这是另外一种情况.
无论如何,如果我正确理解这一点,那么如果没有更多的mymails
行引用它,则想删除mails
中的行.而且两个表中的mailid
是相同的,因此它是一个外键.因此,当不再有子级时,您将删除父级行.
如果正确,则代码可能类似于:
Ok, I''m adding a new solution since this is a different situation.
Anyhow, if I understand this correctly you want to delete the row inmails
if no moremymails
rows are referring to it. And themailid
is the same in both tables so it''s a foreign key. So you''re deleting the parent row when no more children exist.
If that is correct, the code could be something like:
create trigger manage_mail
on mymail
after delete
as
begin
delete from mails
where mails.mailid in (select d.mailid
from deleted d)
and not exists (select 1
from mymail mm
where mm.mailid = mails.mailid);
end
在使用它之前,请仔细测试(在事务内).
但是基本上,这应该删除所有ID与已删除行中的ID相同且没有子项的邮件行.
Test this carefully (inside a transaction) before you use it.
But basically this should delete all the mails rows which have the same id as in deleted rows and no more children exist.
这篇关于SQL 2005中的触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文