• subscribe
January 24, 2002 12:00 AM

Firing a Trigger for Each Inserted Row

SQL Server Pro
InstantDoc ID #23619

I'm using an INSERT INTO...SELECT * FROM bulk statement to insert records into a table. If I write a trigger on the target table that executes on INSERT, will the trigger fire for each row inserted? If not, how can I get the trigger to fire for each row?

SQL Server triggers fire only once per statement, not once per affected row. So, you'll have to create the multiple-row updates yourself in your trigger.



ARTICLE TOOLS

Comments
  • Zenon`
    4 years ago
    Jun 07, 2008

    Mialem nadzieje, ze jest jakies inne rozwiazanie tego problemu. I have hope that is other solution for this problem.

  • Dan
    5 years ago
    Apr 11, 2007

    That was the worse answer to someone's problem by Microsoft ever. I can't believe I bought a subsricption to this site just for that. Completely misleading to end the article with a ... one word before it actually ends.

  • Michael
    5 years ago
    Jan 31, 2007

    test

  • Claudia
    6 years ago
    Jun 27, 2006

    (note: I only have a few years experience here)
    I am doing the reverse of a bulk update on the table itself; it may be thought to go to the very table where there is one transaction, that affects the 'bulk insert':
    -- "Othertable" is where I am 'mass' updating, this trigger can be put into something called "ThisTable"

    -- example: a person deletes a record, it tags associated records, if there is no related table, the "IsOrphan" does not fire, the record is deleted.
    -- note: NO RELATIONSHIPS in either table
    INSTEAD of delete
    AS
    Declare @Rcount As int
    Declare @RegID As Int


    SELECT @rcount = @@rowcount
    IF @rcount =0
    return


    SELECT @RegID= (SELECT deleted.RegID
    FROM deleted)

    BEGIN
    SET NOCOUNT off;
    UPDATE OtherTable
    SET IsOrphan = 1
    WHERE (OtherTable.RegID = @RegID)

  • Anonymous User
    7 years ago
    Jun 13, 2005

    How about an answer for how to do this when set based operations are not an option?

You must log on before posting a comment.

Are you a new visitor? Register Here