SQL Server 2005 insert trigger not inserting enough records

Question

I have a table in a SQL Server 2005 database with a trigger that is supposed to add a record to a different table whenever a new record is inserted. It seems to work fine, but if I execute an Insert Into on the master table that uses a subquery as the source of the values, the trigger only inserts one record in the other table, even though multiple records were added to the master. I want the trigger to fire for each new record added to the master table. Is that possible in 2005?

The insert I'm doing is:

INSERT INTO [tblMenuItems] ([ID], [MenuID], [SortOrder], [ItemReference], [MenuReference], [ConcurrencyID]) SELECT [ID], [MenuID], [SortOrder], [ItemReference], [MenuReference], [ConcurrencyID] FROM [IVEEtblMenuItems]

Here is what the trigger looks like:

CREATE TRIGGER [dbo].[tblMenuItemInsertSecurity] ON [dbo].[tblMenuItems] 
FOR INSERT
AS

Declare @iRoleID int
Declare @iMenuItemID int

Select @iMenuItemID = [ID] from Inserted

DECLARE tblUserRoles CURSOR FASTFORWARD FOR SELECT [ID] from tblUserRoles
OPEN tblUserRoles 
FETCH NEXT FROM tblUserRoles INTO @iRoleID 

WHILE (@@FetchStatus = 0)
  BEGIN
    INSERT INTO tblRestrictedMenuItems(
      [RoleID],
      [MenuItemID],
      [RestrictLevel])

      VALUES(
      @iRoleID,
      @iMenuItemID,
      1)    

    FETCH NEXT FROM tblUserRoles INTO @iRoleID 
  END

CLOSE tblUserRoles 
Deallocate tblUserRoles

Answer 1

Your trigger is only using the first row from 'Inserted'. This is a common misunderstanding when dealing with SQL triggers for the first time. The trigger fires per update not per row.

For example if you do the following:-

update products set title = 'geoff de geoff'

this would update all the products, but a trigger on the product table would only fire once.

The Inserted 'table' you get in trigger would contain all the rows. You must either loop through Inserted with a cursor, or better join Inserted to the table you are updating.

Answer 2

Please lookup multi row consideration for triggers What is with the cursor inside a trigger? Learn how to program set based, cursors are Evil in T-SQL and should only be used to defragment/update stats/other maintanance a bunch of tables