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

The trigger only fires once for each INSERT statment executed - not once for each record inserted.

In your trigger you can access the 'virtual' table called inserted for details of the records inserted.

ie:

SELECT COUNT(*) FROM inserted

Will return the number of inserted records.

Answer 3

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

Answer 4

Thanks for answers, that makes perfect sense.

Answer 5

I just want to second @Gordon Bell on his answer...

"Catch" the values the very moment they are being inserted. You do not really need the cursor in this situation (or maybe you have a reason?).

A simple TRIGGER might be all you need:

http://dbalink.wordpress.com/2008/06/20/how-to-sql-server-trigger-101/