SQL 2005中的触发器 [英] triggers in 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, define IDENTITY for ID 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 in mails if no more mymails rows are referring to it. And the mailid 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屋！