where are the inserted and deleted pseudo tables stored?

Question

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?

Answer 1

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)

CREATE TABLE T1 (X BINARY(10));

GO

--B = Before
INSERT INTO T1
VALUES     (0xBBBBBBBBBBBBBBBBBBBB); 

GO

CREATE TRIGGER TR ON T1 AFTER UPDATE
AS
    PRINT 'Trigger called'

GO

DECLARE @mtsn INT 

SELECT @mtsn = ISNULL(MAX(transaction_sequence_num), 0)
FROM   sys.dm_tran_version_store
WHERE  database_id = DB_ID();    

UPDATE T1
SET    X = 0xAAAAAAAAAAAAAAAAAAAA;    --A = After

SELECT transaction_sequence_num,
       version_sequence_num,
       record_image_first_part,
       CASE
         WHEN CHARINDEX(0xBBBBBBBBBBBBBBBBBBBB, record_image_first_part) > 0
           THEN 'Before'
         WHEN CHARINDEX(0xAAAAAAAAAAAAAAAAAAAA, record_image_first_part) > 0
           THEN 'After'
       END
FROM   sys.dm_tran_version_store
WHERE  database_id = DB_ID()
       AND transaction_sequence_num > @mtsn;


DROP TABLE T1 

Which returns something like

Answer 2

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?