Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
What Happens to a Trigger with Multiple Affected Rows?
Submitted by: AdministratorIf 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
<pre>------------------------------------------------------
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</pre>
(5 row(s) affected)
Submitted by: Administrator
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
<pre>------------------------------------------------------
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</pre>
(5 row(s) affected)
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.