SQL Server triggers allow access to two special tables . INSERTED
- with the "after" values and DELETED
with the "before" values.
Where are these stored?. Are they present in tempdb
or in the same database as the table being acted upon?
SQL Server triggers allow access to two special tables . INSERTED
- with the "after" values and DELETED
with the "before" values.
Where are these stored?. Are they present in tempdb
or in the same database as the table being acted upon?
2条答案
按热度按时间w8rqjzmb1#
In versions prior to SQL Server 2005 these tables were read from the transaction log when needed .
Since SQL Server 2005 the rows making up the inserted and deleted pseudo tables for after triggers are both stored in the version store (always in pages in
tempdb
- these pages may or may not exist in memory).You can see this quite clearly from the below (ran in a DB with both snapshot isolation and RCSI off)
Which returns something like
nxowjjhe2#
It's crazy talk. Those tables exists in memory* for the scope of the transaction.
Using triggers is just bad enough don't try to mess with the INSERTED and DELETED tables. If you want to it badly you can, inside the trigger, fill another (temporary?) table with it's data.
Triggers are considered anti-pattern in almost any case it's just not a plain log. In general you can use the data fired the trigger in first place and keep a concise and easy to maintain business logic.
*in memory: Most of times SQL Engine will try to let everything in RAM memory, if for any reason it needs more memory there are avaiable pages it will start to use the disk by the mean of the tempBD but it's entirely transparent and out of your control.
Edit
Martin Smith answer is an excellent one. I know something similar can be done for temp tables but never tried it for triggers tables. I just want to point implementing anything requires direct manipulation of objects like that can raise suspicious of implementantion going in the wrong direction.
Here some sources for my "rant" against (bad use of) triggers
Are database triggers evil?
When are database triggers bad?