Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Triggers with Multiple Affected Rows

By: FYIcenter.com

(Continued from previous topic...)

What Happens to a Trigger with Multiple Affected Rows?

If there is only one row affected by a DML statement, we know that the DML trigger will be executed once. But how many times the DML trigger will be executed if the DML statement resulted multiple affected rows? The answer is still one.

In the case of multiple affected rows, both INSERTED and DELETED tables will contain multiple rows.

If you want your trigger to report all affected rows, you need to write a loop

USE FyiCenterData;
GO
select * from fyi_users;

-- reporting the first affected row only
UPDATE fyi_users SET email=name;
GO
Email changed from NULL to John King
(5 row(s) affected)

-- reporting all affected rows
ALTER TRIGGER update_user ON fyi_users
AFTER UPDATE
AS
  SELECT 'Email changed from '
    + ISNULL(d.email,'NULL')
    + ' to '
    + ISNULL(i.email,'NULL')
    FROM INSERTED AS i, DELETED AS d
    WHERE i.id = d.id;
GO

UPDATE fyi_users SET email=REVERSE(name);
GO
------------------------------------------------------
Email changed from Marc Kumar to ramuK craM
Email changed from Roy Bush to hsuB yoR
Email changed from Jack Gate to etaG kcaJ
Email changed from Nancy Greenberg to grebneerG ycnaN
Email changed from John King to gniK nhoJ
(5 row(s) affected)

(5 row(s) affected)

(Continued on next topic...)

  1. What Are Triggers?
  2. What Are the Basic Features of a Trigger?
  3. How To Create a Simple Table to Test Triggers?
  4. How To Create a DML Trigger using CREATE TRIGGER Statements?
  5. How To Test a DML Trigger?
  6. How To List All Triggers in the Database with sys.triggers?
  7. How To Modify Existing Triggers using "ALTER TRIGGER"?
  8. How To Delete Existing Triggers using "DROP TRIGGER"?
  9. How To Get the Definition of a Trigger Back?
  10. How To Disable Triggers using "DISABLE TRIGGER"?
  11. How To Create a Trigger for INSERT Only?
  12. How To See the Event List of an Existing Trigger using sys.trigger_events?
  13. How To Access the Inserted Record of an Event?
  14. How To Access the Deleted Record of an Event?
  15. How To Improve the Trigger to Handle NULL Values?
  16. What Happens to a Trigger with Multiple Affected Rows?
  17. How To Override DML Statements with Triggers?
  18. How To Create a DDL Trigger using "CREATE TRIGGER" Statements?
  19. Can You Roll Back the DDL Statement in a Trigger?
  20. Can You Create a Logon Trigger in SQL Server 2005 Express Edition?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...