September 22, 2014 at 10:31 pm
Comments posted to this topic are about the item How many rows will be returned?
September 23, 2014 at 12:19 am
Very nice question. Thanks for share Subhash.
September 23, 2014 at 12:22 am
Great question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 23, 2014 at 1:59 am
Triggers on tables execute only if at least ONE row is affected. So, explanation is wrong.
September 23, 2014 at 2:14 am
Good question , thank you.
Iulian
September 23, 2014 at 2:32 am
Carlo Romagnano (9/23/2014)
Triggers on tables execute only if at least ONE row is affected. So, explanation is wrong.
Hi Carlo,
you are wrong in your assumption. And I guess the idea behind this question was to show that a trigger fires whether or not any rows are affected.
Check e.g. the note on this MSDN page for SQL 2005. And it didn't change since.
These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.This is by design.
September 23, 2014 at 3:27 am
Good Question Subhash.....
September 23, 2014 at 5:19 am
This was removed by the editor as SPAM
September 23, 2014 at 5:24 am
September 23, 2014 at 5:36 am
Mighty (9/23/2014)
Carlo Romagnano (9/23/2014)
Triggers on tables execute only if at least ONE row is affected. So, explanation is wrong.Hi Carlo,
you are wrong in your assumption. And I guess the idea behind this question was to show that a trigger fires whether or not any rows are affected.
Check e.g. the note on this MSDN page for SQL 2005. And it didn't change since.
These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.This is by design.
+1
September 23, 2014 at 6:12 am
Good question.
This is something to consider when using triggers. If you need to execute the code inside the trigger only if it affects some rows, you can use the @@ROWCOUNT variable.
The following code will only insert 1 row in ABC_T1:
USE tempdb
GO
CREATE TABLE ABC_TriggerTest
(
ID INT NOT NULL
IDENTITY(1, 1)
, [Name] NVARCHAR(10)
)
GO
CREATE TABLE ABC_T1 ( ID INT )
GO
CREATE TRIGGER Trg_Test ON ABC_TriggerTest
FOR INSERT, UPDATE
AS
BEGIN
if @@ROWCOUNT > 0
BEGIN
INSERT INTO ABC_T1
VALUES ( '1' )
END
END
GO
INSERT INTO ABC_TriggerTest
SELECT 'First'
GO
INSERT INTO ABC_TriggerTest
SELECT [Name]
FROM ABC_TriggerTest
WHERE 1 = 2
--How many rows will be there for below statement
SELECT *
FROM ABC_T1
---------------
Mel. 😎
September 23, 2014 at 6:16 am
Carlo Romagnano (9/23/2014)
Triggers on tables execute only if at least ONE row is affected. So, explanation is wrong.
Ummm..... Nope, that's not true.
I think the question reflects exactly how that isn't true.
---------------
Mel. 😎
September 23, 2014 at 6:25 am
This was removed by the editor as SPAM
September 23, 2014 at 7:05 am
SqlMel (9/23/2014)
Good question.This is something to consider when using triggers. If you need to execute the code inside the trigger only if it affects some rows, you can use the @@ROWCOUNT variable.
Definitely a good question.
I have always had DML triggers whose action depends on the contents of whichever is appropriate of the inserted and deleted virtual tables, rather than @@ROWCOUNT. Usually I want a trigger to take different actions is no rows are affected from the nomal (at least one row affected) action, but there are exceptions: some tracking triggers just stick for example "n rows deleted from table tablename by login xyz at dtime on date" in a log table and that is the same action whether n is 0 or not.
Tom
September 23, 2014 at 7:24 am
Great question. I pretty much knew what was being tested part way through and skipped the first insert. Guess I should wait until after I finish my first cup of coffee. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply