902
Part VII: Monitoring and Auditing
Using Management Studio, database triggers are listed under the database’s Programmability
node in Object Explorer. Server triggers are listed under Server Objects in Object Explorer.
Database triggers can be scripted using Object Explorer but not modifi ed as easily as other
programmability objects such as stored procedures. The context menu for DDL triggers
does not offer the modify or script to alter options that a stored procedure’s context menu
includes.To list the database DDL triggers using code, query the sys.triggers and sys
.events catalog views. Server triggers are found at sys.server_triggers and sys
.server_trigger_events.Trigger Scope
There are dozens of events that can potentially fi re a DDL trigger — one for every DDL type
of action that can be executed on the server or database. These events are categorized into
a hierarchy using event groups. Creating a DDL trigger for an event group causes the DDL
trigger to fi re for every event in that group. The DDL Event Groups page in Books Online
details the whole hierarchy.The top, or root, of the hierarchy is the ddl_events group, which includes every possible
event. The next level has the ddl_server_level_events and ddl_database_level_
events groups. Each of these groups includes several subgroups and events. Chances are
there’s a group that matches exactly with the types of events you want to handle.DDL triggers can also be fi red by specifi c events, such as create_table, create_login,
or alter_view. The full list is in Books Online under “DDL Events.”The following code creates three DDL triggers to demonstrate DDL trigger scope. The fi rst
DDL trigger handles all server-level events:CREATE TRIGGER DDL_Server_Level_Sample
ON ALL SERVER
FOR DDL_SERVER_LEVEL_EVENTS
AS
Set NoCount ON
Print 'DDL_Server_Level_Sample DDL Trigger'The second DDL trigger fi res for all database-level events:USE tempdb
GO
CREATE TRIGGER DDL_Database_Sample
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
Set NoCount ON
Print 'DDL_Database_Sample DDL Trigger'c36.indd 902c36.indd 902 7/31/2012 10:03:10 AM7/31/2012 10:03:10 AM
http://www.it-ebooks.info