Collections:
Triggers with Multiple Affected Rows in SQL Server
What Happens to a Trigger with Multiple Affected Rows in SQL Server?
✍: FYIcenter.com
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)
2016-10-24, 677👍, 0💬
Popular Posts:
Where to find answers to frequently asked questions on INSERT, UPDATE and DELETE Statements in MySQL...
How To List All User Names in a Database in SQL Server? If you want to see a list of all user names ...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...
Where to find answers to frequently asked questions on Downloading and Installing SQL Server 2005 Ex...
How To Provide Values to Stored Procedure Parameters in SQL Server Transact-SQL? If a stored procedu...