用于批量更新的SQL Server更新触发器

问题描述：

以下是SQL Server 2005更新触发器。对于isActive标志更改的email_subscriberList表上的每个更新，我们都会将审计记录插入到email_Events表中。这对于单个更新工作正常，但批量更新只记录最后更新的行。如何转换下面的代码以更新每行的插入内容？用于批量更新的SQL Server更新触发器

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

你是如何进行批量更新的？ – Rup 2010-07-02 14:52:00

多个应用程序将更新此表。 – 2010-07-02 14:54:34

答

例如

未测试

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

不会记录所有更新，不只是那些IsActive更改的更新？ – 2010-07-02 15:11:13

增加了另一个连接 – SQLMenace 2010-07-02 15:38:50

答

左外连接，为万一有在客户或email_Lists没有相关条目（如在当前的代码是可能的） - 使它们内如果您知道会有数据存在（即外键已到位），则加入。

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

测试的很好，特别是对于并发问题。触发器内的这些连接让我感到紧张。

用于批量更新的SQL Server更新触发器

相关推荐