<p>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?</p>

<p>The insert I'm doing is:</p>

<pre><code>INSERT INTO [tblMenuItems] ([ID], [MenuID], [SortOrder], [ItemReference], [MenuReference], [ConcurrencyID]) SELECT [ID], [MenuID], [SortOrder], [ItemReference], [MenuReference], [ConcurrencyID] FROM [IVEEtblMenuItems]
</code></pre>

<p>Here is what the trigger looks like:</p>

<pre><code>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
</code></pre>

[{'answer_id': 18229, 'author': 'Andrew Rimmer', 'author_id': 1982, 'author_profile': 'https://Stackoverflow.com/users/1982', 'pm_score': 3, 'selected': True, 'text': "<p>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.</p>\n\n<p>For example if you do the following:-</p>\n\n<p>update products\nset title = 'geoff de geoff'</p>\n\n<p>this would update all the products, but a trigger on the product table would only fire once.</p>\n\n<p>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.</p>\n"}, {'answer_id': 18230, 'author': 'Martin', 'author_id': 770, 'author_profile': 'https://Stackoverflow.com/users/770', 'pm_score': 1, 'selected': False, 'text': "<p>The trigger only fires once for each INSERT statment executed - not once for each record inserted.</p>\n\n<p>In your trigger you can access the 'virtual' table called <strong>inserted</strong> for details of the records inserted.</p>\n\n<p>ie:</p>\n\n<pre><code>SELECT COUNT(*) FROM inserted \n</code></pre>\n\n<p>Will return the number of inserted records.</p>\n"}, {'answer_id': 18235, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 2, 'selected': False, 'text': '<p>Please lookup <a href="http://msdn.microsoft.com/en-us/library/ms190752.aspx" rel="nofollow noreferrer">multi row consideration for triggers</a>\nWhat is with the cursor inside a trigger? Learn how to program set based, cursors are <strong>Evil</strong> in T-SQL and should only be used to defragment/update stats/other maintanance a bunch of tables</p>\n'}, {'answer_id': 223331, 'author': 'MarlonRibunal', 'author_id': 10385, 'author_profile': 'https://Stackoverflow.com/users/10385', 'pm_score': 0, 'selected': False, 'text': '<p>I just want to second @Gordon Bell on his answer...</p>\n\n<p>"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?).</p>\n\n<p>A simple TRIGGER might be all you need:</p>\n\n<p><a href="http://dbalink.wordpress.com/2008/06/20/how-to-sql-server-trigger-101/" rel="nofollow noreferrer">http://dbalink.wordpress.com/2008/06/20/how-to-sql-server-trigger-101/</a> </p>\n'}]
['https://Stackoverflow.com/questions/18223', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/741/']