SQL Server批量更新的触发器

Question

以下是SQL Server 2005的更新触发器。对于email_subscriberList表中isActive标志更改的每个更新，我们都会在email_Events表中插入审核记录。这适用于单个更新，但对于批量更新，只记录最后一个更新的行。如何修改以下代码以便为每个更新的行执行插入操作？

CREATE TRIGGER [dbo].[Email_SubscriberList_UpdateEmailEventsForUpdate_TRG]
ON [dbo].[Email_subscriberList]
FOR UPDATE
AS
DECLARE @CustomerId int
DECLARE @internalId int
DECLARE @oldIsActive bit
DECLARE @newIsActive bit
DECLARE @email_address varchar(255)
DECLARE @mailinglist_name varchar(255)
DECLARE @email_event_type varchar(1)

SELECT @oldIsActive = isActive from Deleted 
SELECT @newIsActive = isActive from Inserted

IF @oldIsActive <> @newIsActive

 BEGIN

 IF @newIsActive = 1
     BEGIN
     SELECT @email_event_type = 'S'
     END
 ELSE
     BEGIN
     SELECT @email_event_type = 'U'
     END


 SELECT @CustomerId = customerid from Inserted
 SELECT @internalId = internalId from Inserted
 SELECT @email_address = (select email from customer where customerid = @CustomerId)
 SELECT @mailinglist_name = (select listDescription from Email_lists where internalId = @internalId)

 INSERT INTO Email_Events
 (mailshot_id, date, email_address, email_event_type, mailinglist_name)
 VALUES
 (@internalId, getDate(), @email_address, @email_event_type,@mailinglist_name)

 END

- Gary Boyle

---

你是如何进行批量更新的？ - Rup

多个应用程序将更新此表。 - Gary Boyle

Answer 1

例子

未经测试的

CREATE TRIGGER [dbo].[Email_SubscriberList_UpdateEmailEventsForUpdate_TRG]
ON [dbo].[Email_subscriberList]
FOR UPDATE
AS


 INSERT INTO Email_Events
 (mailshot_id, date, email_address, email_event_type, mailinglist_name)
 SELECT i.internalId,getDate(),c.email, 
 case i.isActive when 1 then 'S' else 'U' end,e.listDescription
 from Inserted i
 join deleted d on i.customerid = d.customerid
 and i.isActive  <> d.isActive 
 join customer c on i.customerid = c.customerid
 join Email_lists e on e.internalId = i.internalId

- SQLMenace

---

这会记录所有的更新，而不仅仅是 IsActive 改变的吗？ - Philip Kelley

Answer 2

如果客户或电子邮件列表中没有相关条目（在当前代码中可能发生），则左外连接会使用；如果您知道将存在数据（即外键已就位），则使它们成为内连接。

CREATE TRIGGER [dbo].[Email_SubscriberList_UpdateEmailEventsForUpdate_TRG] 
ON [dbo].[Email_subscriberList] 
FOR UPDATE 
AS 

INSERT INTO Email_Events
  (mailshot_id, date, email_address, email_event_type, mailinglist_name) 
 select
    i.InternalId
   ,getdate()
   ,cu.Email
   ,case i.IsaActive
      when 1 then 'S'
      else 'U'
    end
   ,el.ListDescription
  from inserted i
   inner join deleted d
    on i.CustomerId = d.CustomerId
     and i.IsActive <> d.IsActive
   left outer join Customer cu
    on cu.CustomerId = i.CustomerId
   left outer join Email_Lists el
    on el.InternalId = i.InternalId

请仔细测试，特别是针对并发问题。触发器内的联接让我感到不安。

- Philip Kelley