Database Reference
In-Depth Information
CONTEXT_INFO
Every session has up to 128 bytes of binary data value associated with it. That value has the session scope and it can
be used when you need to pass some parameters to or from triggers. You can set the value with the
SET CONTEXT_INFO
statement and retrieve it with the
CONTEXT_INFO
function.
As an example, let's modify the DDL trigger
trg_PreventAlterDropTable
to allow table alteration when context
information contains the string:
ALLOW_TABLE_ALTERATION
. The code for doing this is shown in Listing 8-19.
Listing 8-19.
CONTEXT_INFO: Trigger code
create trigger trg_PreventAlterDropTable on database
for alter_table
as
begin
if isnull(convert(varchar(22),context_info()),'') <> 'ALLOW_TABLE_ALTERATION'
begin
print 'Table alteration is not allowed in such context'
rollback
end
end
To be able to alter the table, the session needs to set
context_info
, as shown in Listing 8-20.
Listing 8-20.
CONTEXT_INFO: Setting CONTEXT_INFO value
declare
@CI varbinary(128) = convert(varbinary(22),'ALLOW_TABLE_ALTERATION')
set context_info @CI
alter table Delivery.Addresses add NewColumn int null
Context binary data also exposed through
context_info
column in
sys.dm_exec_request
,
sys.dm_exec_
sessions
and
sys.processes
system views.
Summary
Triggers can help in certain scenarios. DDL triggers can validate and prevent unwanted metadata changes in the
system. Login triggers can help implement custom authentication. DML triggers can help centralize some logic in
the code, especially when there is no dedicated data access tier in the system. One example is the implementation of
an audit trail function when you want to capture the information about users who change data. While there are other
approaches to implement such tasks, trigger-based implementation can be the simplest.
Unfortunately, triggers come at a high cost. AFTER DML triggers introduce overhead related to the maintenance
of
inserted
and
deleted
virtual tables. This leads to extra
tempdb
load and index fragmentation. INSTEAD OF triggers
could lead to system supportability issues. It is easy to forget or overlook the logic implemented in such triggers.
DDL triggers run after schema changes are done. While you can rollback those changes from within the triggers,
such operations can be very expensive in terms of I/O, CPU, and transaction log activity, especially with the large tables.
Finally, LOGON triggers can prevent users from logging into the system when incorrectly implemented due to
bugs in the logic or connection timeouts introduced by long execution times, especially when those triggers access
external resources.
