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

select * from ggl_users;

-- reporting the first affected row only

UPDATE ggl_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 ggl_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 ggl_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)