Post reply

Trigger On Update

  • August 30, 2010 at 2:56 pm

    #239698

    How trigger works on update statement affecting more than one row?

  • August 30, 2010 at 3:18 pm

    #1213957

    vrkn82 (8/30/2010)

    How trigger works on update statement affecting more than one row?

    If coded properly as a set-based operation, just fine.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 30, 2010 at 5:30 pm

    #1213999

    inserted and deleted tables hold the changed data as a result set, not a single row.

    Avoid the following code as it won't handle multiple updates.

    DECLARE @insertedValue VARCHAR(10), @insertedID VARCHAR(10), @deletedValue VARCHAR(10), @deletedID VARCHAR(10)

    SELECT @deletedValue = DeletedValue, @deletedID = DeletedID

    FROM deleted

    SELECT @insertedValue = InsertedValue, @insertedID = InsertedID

    FROM inserted

    then use the variable in your insert/delete/update statements.

    Just insert, delete, and/or update as you would with multiple tables, not variables.

  • August 30, 2010 at 8:41 pm

    #1214020

    How do i know that no of records updated without seeing the table ?

    in one transaction 1 record or multiple records can be updated.

    Is there any way finding out this ?

    Thank you

  • August 30, 2010 at 9:34 pm

    #1214026

    You could do a select count(*) from inserted within the trigger.

    Alternatively, in the code running the update statement, look at @@rowcount.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply