SQL Server/T-SQL Tutorial/Trigger/Trigger on database

Материал из SQL эксперт
Перейти к: навигация, поиск

CREATE TRIGGER prevent_drop_triggers ON DATABASE FOR DROP_TRIGGER

   <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

   <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>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Trigger/Trigger_on_database&oldid=6813»