以下是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