SQL Server/T-SQL Tutorial/System Tables Views/sys.triggers — различия между версиями
Admin (обсуждение | вклад) м (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>