Просмотр исходного текста страницы SQL Server/T-SQL Tutorial/Trigger/Trigger on database

== CREATE TRIGGER prevent_drop_triggers ON DATABASE FOR DROP_TRIGGER==



   
  <!-- start source code -->
   
    <source lang="sql">
CREATE TRIGGER prevent_drop_triggers
       ON DATABASE FOR DROP_TRIGGER
       AS PRINT "You must disable "prevent_drop_triggers" to drop any trigger"
       ROLLBACK</source>
    
   

== Creating a DDL Trigger that Audits Database-Level Events==



   
  <!-- start source code -->
   
    <source lang="sql">
4>
5> CREATE TABLE MyAudit (EventData xml NOT NULL, AttemptDate datetime NOT NULL DEFAULT GETDATE(),
6> DBUser char(50) NOT NULL)
7> GO
1>
2> CREATE TRIGGER db_trg
3> ON DATABASE
4> FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
5> AS
6> SET NOCOUNT ON
7> INSERT dbo.MyAudit
8> (EventData, DBUser)
9> VALUES (EVENTDATA(), USER)
10> GO
1>
2> CREATE NONCLUSTERED INDEX ni_MyAudit_DBUser ON
3> MyAudit(DBUser)
4> GO
1>
2> SELECT EventData
3> FROM MyAudit
4> GO
EventData

------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
----------------
<EVENT_INSTANCE><EventType>CREATE_INDEX</EventType><PostTime>2007-10-20T00:37:42.357</PostTime><SPID>51</SPID><ServerNam
e>BCE67B1242DE45A\SQLEXPRESS</ServerName><LoginName>BCE67B1242DE45A\Administrator</LoginName><UserName>dbo</UserName><Da
tabaseName>maste
1>
2> drop table MyAudit
3> GO
1></source>