Database Reference
In-Depth Information
Figure 8-3. Clustered index physical statistics after DELETE statement with AFTER DELETE trigger
Version store pointers increased the size of the rows and led to the massive page splits and fragmentation during
DELETE operations. Moreover, in the end, we almost doubled the number of pages in the index.
in some cases, when there is only iN_rOW allocation involved (for example, when a table does not have LOB
columns nor variable width columns, which can potentially require it to store data in the rOW_OVerFLOW pages),
sQL server optimizes that behavior and does not add 14 bytes version store pointers to the rows.
Note
Triggers are always running in the same transaction as the statement that fired them. We need to make trigger
execution time as short as possible to minimize the duration of locks being held.
We will talk about different lock types, their behavior, and their lifetime in part 3, “Locking, Blocking,
and Concurrency.”
Note
DDL Triggers
DDL triggers allow you to define the code that executes in response to various DDL events, such as creation,
alteration, or deletion of database objects, changing permissions, and updating statistics. You can use these triggers
for audit purposes as well as to restrict some operations on database schema. For example, the trigger shown in
Listing 8-11 would prevent accidental altering or dropping of a table, and it could be used as the safety feature in
production environment.
Listing 8-11. DDL Triggers: Preventing altering and dropping tables in production
create trigger trg_PreventAlterDropTable on database
for alter_table, drop_table
as
begin
print 'Table cannot be altered or dropped with trgPreventAlterDropTable trigger enabled'
rollback
end
While this approach helps in keeping tables and their schemas intact, it introduces one potential problem. DDL
triggers fire after an operation is completed. As a result, using our example, if you have the session altering the table,
SQL Server would perform the alteration before the trigger fires and then rollback all of the changes.
 
 
Search WWH ::




Custom Search