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)
⇒ "INSTEAD OF" - Overriding DML Statements with Triggers in SQL Server
⇐ Improving the Trigger to Handle NULL Values in SQL Server
2016-10-24, 1490🔥, 0💬
Popular Posts:
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL? If you e...
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...
How To List All Login Names on the Server in SQL Server? If you want to see a list of all login name...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...