SQL Server Update Trigger for Bulk Updates

Question

The following is an update trigger for SQL Server 2005. For each update in the email_subscriberList table where the isActive flag changes, we insert an audit record in the email_Events table. This is great for individual updates, but for mass updates, only the last updated row is recorded. How to convert the code below to perform an insert for each updated row?

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

Answer 1

Example

untested

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

Answer 2

, 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

, concurrency. , , .