Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    23

    Unanswered: Trigger not firing on update in Query Analyzer

    I have a trigger which works fine when updating a row using Enterprise Manager, but it is not firing when I do the exact same update from Query Analyzer. The trigger is updating a column within the same table and row being updated.
    It makes no sense.
    Any suggestions?

    We are running SQL Server 2000, Service Pack 3

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Run PROFILER and capture the activity.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    May 2003
    Posts
    23
    Satya,
    When I execute SQL to update more than one row, the trigger only fires for the last row updated. It works fine when I update 1 row at a time.
    Is there a setting which affects this, or some statement needed in the trigger?

  4. #4
    Join Date
    Apr 2003
    Location
    UK
    Posts
    11

    Cool

    Originally posted by ts555
    Satya,
    When I execute SQL to update more than one row, the trigger only fires for the last row updated. It works fine when I update 1 row at a time.
    Is there a setting which affects this, or some statement needed in the trigger?

    When several records are inserted as a set, the inserted pseudo table in your trigger contains the complete set of inserted records. Your trigger needs to handle more than one row in the pseudo table. this is why your trigger seems to work with the last record only.

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Well referenced by Spudly, refer to books online for Using the inserted and deleted Tables.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    May 2003
    Posts
    23

    Smile

    Spudly,
    I thank you VERY much.
    I had to open a cursor on the "inserted" table, and 'loop' thru it updating row by row.
    I never would have figured that out.
    I had used the inserted and deleted tables before, but thought a trigger fired every time a row was updated, not just once for a transaction.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •