How many rows will be returned?

Question

How many rows will be returned?

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 1

Excellent question, Subhash. It's good to cover a topic which, based upon the comments so far, confuses some people. Not everyone uses triggers on a regular basis (I know I don't), so some people will have some incorrect assumptions in their minds based upon their interpretation of what they have read and not upon direct experience.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Dana Medley SSCertifiable Points: 6764 More actions · Answer 3

Thanks for the question. I don't use triggers day to day so this was a nice learning experience.

Everything is awesome!

Marcia J SSCertifiable Points: 5649 More actions · Answer 4

I have a question about this. If the trigger executes once even if no rows are inserted, what happens if 5 rows are inserted at a time. Does it execute once for the insert or 5 times (once for each row inserted)?

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

marcia.j.wilson (9/23/2014)
I have a question about this. If the trigger executes once even if no rows are inserted, what happens if 5 rows are inserted at a time. Does it execute once for the insert or 5 times (once for each row inserted)?

Triggers in sql server fire ONCE per operation. This is why in triggers we need to utilize the inserted and deleted virtual tables and avoid scalar variables. Trigger code needs to be set based.

_______________________________________________________________

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/

Marcia J SSCertifiable Points: 5649 More actions · Answer 6

Sean Lange (9/23/2014)
marcia.j.wilson (9/23/2014)
I have a question about this. If the trigger executes once even if no rows are inserted, what happens if 5 rows are inserted at a time. Does it execute once for the insert or 5 times (once for each row inserted)?
Triggers in sql server fire ONCE per operation. This is why in triggers we need to utilize the inserted and deleted virtual tables and avoid scalar variables. Trigger code needs to be set based.

Thanks.

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 7

I haven't worked with triggers in a while and wondered why someone would create a trigger without utilizing the inserted and deleted virtual tables. I guess someone needs that functionality?

Aigle de Guerre!

Ken SSCoach Points: 17191 More actions · Answer 8

I had seen this same behavior firsthand when I was testing out some triggers before I utilized the inserted table values to limit how the trigger fired. A good question about a topic that can be a bit confusing. Thank you Subhash.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Meow Now (9/23/2014)
I haven't worked with triggers in a while and wondered why someone would create a trigger without utilizing the inserted and deleted virtual tables. I guess someone needs that functionality?

Maybe somebody only wants to track the number of insert statements being executed and isn't concerned with the number of rows. Not sure how useful it would be but nice to know the flexibility is there for this type of thing.

_______________________________________________________________

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/

Hany Helmy SSChampion Points: 13921 More actions · Answer 10

Good to now that, agree with most of you here, it`s a good question.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Subhash Kr Singh SSC-Addicted Points: 481 More actions · Answer 11

Subhash Kr Singh

SSC-Addicted

Points: 481

September 24, 2014 at 12:01 am

#1747721

Good explanation. Thanks

Subhash

Gary Varga SSC Guru Points: 82166 More actions · Answer 12

This is a great example of a QOTD describing a "gotcha". If anyone knows any others which they discovered or just know about then I, for one, would appreciate it.

So thanks Subhash!!!

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

Carlo Romagnano (9/23/2014)
Triggers on tables execute only if at least ONE row is affected. So, explanation is wrong.

Select code from question

Copy

Paste

Execute

Insert foot into mouth and chew.

Sorry, Carlo, but you are mistaken. Triggers fire once per executed DML statement, regardless of number of rows affected - even if it's zero.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

Gary Varga (9/24/2014)
This is a great example of a QOTD describing a "gotcha". If anyone knows any others which they discovered or just know about then I, for one, would appreciate it.
So thanks Subhash!!!

I would not call this a "gotcha".

Triggers are designed to fire once per execution of the DML statement. Regardless of the number of rows affected. Since all affected rows are available in the inserted and/or deleted pseudo-tables, it is very easy to determine how many rows were actually affected.

Oracle has syntax to specify whether a trigger should fire once per statement execution or once per affected row. SQL Server offers only the first option.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Gary Varga SSC Guru Points: 82166 More actions · Answer 15

Hugo Kornelis (9/24/2014)
Gary Varga (9/24/2014)
This is a great example of a QOTD describing a "gotcha". If anyone knows any others which they discovered or just know about then I, for one, would appreciate it.
So thanks Subhash!!!
I would not call this a "gotcha".
Triggers are designed to fire once per execution of the DML statement. Regardless of the number of rows affected. Since all affected rows are available in the inserted and/or deleted pseudo-tables, it is very easy to determine how many rows were actually affected.
Oracle has syntax to specify whether a trigger should fire once per statement execution or once per affected row. SQL Server offers only the first option.

I called it that because it is not totally intuitive hence a good number of knowledgeable people did not know this. It is also the sort of behavior that could show itself only rarely in a system an thus be an elusive defect.

BTW I got this correct so I am not complaining about the question at all (which I thought was rather good).

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!