Triggers - Pro SQL Server Internals - page 166

Now let's run the MERGE statement, as shown in Listing 8-6.

Listing 8-6. Triggers and MERGE statement:MERGE
merge into dbo.Data as Target
using (select 1 as [Value]) as Source
on Target.Col = Source.Value
when not matched by target then
insert(Col) values(Source.Value)
when not matched by source then
delete
when matched then
update set Col = Source.Value;

Because the dbo.Data table is empty, the merge statement would insert one row there. Let's look at the output
from the triggers, as shown in Figure 8-1 .

Figure 8-1. @@rowcount, inserted and deleted tables with MERGE operator

As you see, all three triggers were fired. In each of them, @@rowcount represented the number of rows affected by
the MERGE . However, with the AFTER UPDATE and AFTER DELETE triggers, the inserted and deleted tables were
empty. You need to check the content of those tables to prevent the code in the trigger from being executed.
As you can guess, there is overhead associated with the triggers. At a bare minimum, SQL Server needs to create
inserted and deleted virtual tables when triggers are present. SQL Server does not analyze if there is any logic that
references those tables within the trigger and always creates them. While the overhead associated with INSTEAD OF
triggers is not particularly large, this is not the case with AFTER triggers. AFTER triggers store the data from those
tables in the special part of tempdb called version store , keeping it until the end of transaction.

■ sQL server uses version store to maintain multiple versions of the rows, and it supports several features, such
as optimistic transaction isolation levels, online indexing, multiple active result sets (Mars), and triggers. We will talk
about version store in greater detail in Chapter 21, “Optimistic isolation Levels.”

Note

While version store usage introduces addition tempdb load, there is another important factor that you need to
keep in mind. In order to maintain the links between the new and old versions of the rows, AFTER UPDATE and
AFTER DELETE triggers add 14 bytes version store pointer to the rows they modified or deleted, which will stay until
the index has been rebuilt. That could increase the row size and introduce the fragmentation similar to the
insert/update pattern discussed in Chapter 5, “Index Fragmentation.” Let's look at the example and create the table
with some data, as shown in Listing 8-7.