I was just told that SQL Server updates are executed one row at a time. So if 100 rows were updated, a trigger would also be executed 100 times.
Is this accurate? Does this also mean that Inserted pseudo table only holds one row at a time?
Asked
Active
Viewed 758 times
2
-
What is an inserted pseudo table? You might have better luck on dba.stackexchange.com for this question – Zach Smith Mar 25 '19 at 19:31
-
1The only thing I know of that has that limitation is Linq to SQL... – Drunken Code Monkey Mar 25 '19 at 19:33
-
@ZachSmith, [Inserted And Deleted tables](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-2017). – Eric Brandt Mar 25 '19 at 19:47
-
Thank you - I didn't know of those. Can you access those tables during an insertion or update? – Zach Smith Mar 26 '19 at 05:53
1 Answers
8
No this is not correct. If a single UPDATE affects multiple rows, a TRIGGER on that table would only fire once, and the virtual tables inserted and deleted would contain all the rows that were affected by the UPDATE.
Tab Alleman
- 31,483
- 7
- 36
- 52
-
This is how I always though it worked, but someone with more experience told me otherwise. Do you have any documentation so I can mark this as the answer? – AlanPear Mar 25 '19 at 19:33
-
There's a difference between experience and knowledge as I've found... Some of the youngest people I've worked with were the most knowledgeable. – Charleh Mar 25 '19 at 19:35
-
1Not offhand; I am answering from experience. Why don't you create a simple test and run it for the person who told you otherwise? Create a TRIGGER that inserts the rows from `inserted` into an empty table and test a multi-row update, and show him the results. What could be better documentation than that? – Tab Alleman Mar 25 '19 at 19:35
-
-
[Here's the documentation](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers) for triggers on SQL Server. I'm not sure if it explicitly states that triggers fire just once per event. It states that trigger fire on each DML event and that inserted and deleted tables contain all the rows for the event. – Luis Cazares Mar 25 '19 at 19:43
-
1Put a trigger on a table (Table 1) that has several rows of data in it. The trigger will insert all the rows from the `inserted` virtual table into an empty table (Table 2). Then do an UPDATE on Table 1 that will update more than one row, due to the WHERE clause. – Tab Alleman Mar 25 '19 at 19:45
-
@LuisCazares that document says "The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger." I think that answers my question. – AlanPear Mar 25 '19 at 19:50