Database Reference
In-Depth Information
Let's prove it now. As a first step, let's alter the trigger to capture the information about table structure during
execution. You can see the code that does this in Listing 8-12.
Listing 8-12.
DDL Triggers: Trigger code
alter trigger trg_PreventAlterDropTable on database
for alter_table
as
begin
declare
@objName nvarchar(257) =
eventdata().value('/EVENT_INSTANCE[1]/SchemaName[1]','nvarchar(128)') +
'.' + eventdata().value('/EVENT_INSTANCE[1]/ObjectName[1]','nvarchar(128)')
select column_id, name
from sys.columns
where object_id = object_id(@objName)
print ' Table cannot be altered or dropped with trgPreventAlterDropTable trigger enabled'
rollback
end
This trigger returns the list of columns in the table at the moment the trigger fires.
Now let's run the
ALTER TABLE
statement that adds a persistent computed column to the table, capturing I/O
statistics during the execution. You can see the code for doing this in Listing 8-13.
Listing 8-13.
DDL Triggers: ALTER TABLE statement
set statistics io on;
alter table Delivery.Addresses
add NewColumn as AddressId persisted;
This alteration adds another column to every data row in the table. We can see the results in Figure
8-4
.
Figure 8-4.
Table structure in DDL trigger with I/O statistics of the operation
