SQL Server/T-SQL Tutorial/System Tables Views/sys.triggers — различия между версиями

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

Текущая версия на 13:24, 26 мая 2010

Viewing DDL Trigger Metadata by joining sys.triggers and sys.sql_modules

   <source lang="sql">

4> 5> SELECT t.name, m.Definition 6> FROM sys.triggers AS t 7> INNER JOIN sys.sql_modules m ON 8> t.object_id = m.object_id 9> WHERE t.parent_class_desc = "DATABASE" 10> GO name

        Definition


---------------------------------------------------------------------------------------------------------------

db_trg CREATE TRIGGER db_trg ON DATABASE FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX AS SET NOCOUNT ON INSERT dbo.MyAudit (EventData, DBUser) VALUES (EVENTDATA(), USER)</source>


Viewing DDL Trigger Metadata by querying sys.triggers

   <source lang="sql">

3> 4> SELECT name TriggerNM, is_disabled 5> FROM sys.triggers 6> WHERE parent_class_desc = "DATABASE" 7> ORDER BY OBJECT_NAME(parent_id), name 8> GO TriggerNM

        is_disabled


-----------

db_trg

                  0

1></source>


Viewing DML Trigger Metadata: Show the DML triggers in the current database

   <source lang="sql">

3> 4> SELECT OBJECT_NAME(parent_id) Table_or_ViewNM, 5> name TriggerNM, is_instead_of_trigger, is_disabled 6> FROM sys.triggers 7> WHERE parent_class_desc = "OBJECT_OR_COLUMN" 8> ORDER BY OBJECT_NAME(parent_id), name 9> GO Table_or_ViewNM

        TriggerNM
                 is_instead_of_trigger is_disabled


---------------------------------------------------------------------------------------------------------------
--------------------- -----------

1></source>