我需要列出SQL Server数据库中的所有触发器表名和表的模式。
我马上就要讲到这个了:
SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj),
isupdate = OBJECTPROPERTY( id, 'ExecIsUpdateTrigger'), isdelete = OBJECTPROPERTY( id, 'ExecIsDeleteTrigger'),
isinsert = OBJECTPROPERTY( id, 'ExecIsInsertTrigger'), isafter = OBJECTPROPERTY( id, 'ExecIsAfterTrigger'),
isinsteadof = OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger'),
[disabled] = OBJECTPROPERTY(id, 'ExecIsTriggerDisabled')
FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE type = 'TR'
我还需要得到表的模式。
Necromancing。
只是发布,因为到目前为止所有的解决方案都不够完整。
SELECT
sch.name AS trigger_table_schema
,systbl.name AS trigger_table_name
,systrg.name AS trigger_name
,sysm.definition AS trigger_definition
,systrg.is_instead_of_trigger
-- https://stackoverflow.com/questions/5340638/difference-between-a-for-and-after-triggers
-- Difference between a FOR and AFTER triggers?
-- CREATE TRIGGER trgTable on dbo.Table FOR INSERT,UPDATE,DELETE
-- Is the same as
-- CREATE TRIGGER trgTable on dbo.Table AFTER INSERT,UPDATE,DELETE
-- An INSTEAD OF trigger is different, and fires before and instead of the insert
-- and can be used on views, in order to insert the appropriate values into the underlying tables.
-- AFTER specifies that the DML trigger is fired only when all operations
-- specified in the triggering SQL statement have executed successfully.
-- All referential cascade actions and constraint checks also must succeed before this trigger fires.
-- AFTER is the default when FOR is the only keyword specified.
,CASE WHEN systrg.is_instead_of_trigger = 1 THEN 0 ELSE 1 END AS is_after_trigger
,systrg.is_not_for_replication
,systrg.is_disabled
,systrg.create_date
,systrg.modify_date
,CASE WHEN systrg.parent_class = 1 THEN 'TABLE' WHEN systrg.parent_class = 0 THEN 'DATABASE' END trigger_class
,CASE
WHEN systrg.[type] = 'TA' then 'Assembly (CLR) trigger'
WHEN systrg.[type] = 'TR' then 'SQL trigger'
ELSE ''
END AS trigger_type
-- https://dataedo.com/kb/query/sql-server/list-triggers
-- ,(CASE WHEN objectproperty(systrg.object_id, 'ExecIsUpdateTrigger') = 1
-- THEN 'UPDATE ' ELSE '' END
-- + CASE WHEN objectproperty(systrg.object_id, 'ExecIsDeleteTrigger') = 1
-- THEN 'DELETE ' ELSE '' END
-- + CASE WHEN objectproperty(systrg.object_id, 'ExecIsInsertTrigger') = 1
-- THEN 'INSERT' ELSE '' END
-- ) AS trigger_event
,
(
STUFF
(
(
SELECT
', ' + type_desc AS [text()]
-- STRING_AGG(type_desc, ', ') AS foo
FROM sys.events AS syse
WHERE syse.object_id = systrg.object_id
FOR XML PATH(''), TYPE
-- GROUP BY syse.object_id
).value('.[1]', 'nvarchar(MAX)')
, 1, 2, ''
)
) AS trigger_event_groups
-- ,CASE WHEN systrg.parent_class = 1 THEN 'TABLE' WHEN systrg.parent_class = 0 THEN 'DATABASE' END trigger_class
,'DROP TRIGGER "' + sch.name + '"."' + systrg.name + '"; ' AS sql
-- ,systrg.*
FROM sys.triggers AS systrg
LEFT JOIN sys.sql_modules AS sysm
ON sysm.object_id = systrg.object_id
-- sys.objects for view triggers
-- LEFT JOIN sys.objects AS systbl ON systbl.object_id = systrg.object_id
-- inner join if you only want table-triggers
LEFT JOIN sys.tables AS systbl ON systbl.object_id = systrg.parent_id
LEFT JOIN sys.schemas AS sch
ON sch.schema_id = systbl.schema_id
WHERE (1=1)
-- AND sch.name IS NOT NULL
-- AND sch.name IS NULL
-- AND sch.name = 'dbo'
-- And here, exclude some triggers with a certain naming schema
/*
AND
(
-- systbl.name IS NULL
-- OR
NOT
(
systrg.name = 'TRG_' + systbl.name + '_INSERT_History'
OR
systrg.name = 'TRG_' + systbl.name + '_UPDATE_History'
OR
systrg.name = 'TRG_' + systbl.name + '_DELETE_History'
)
)
*/
ORDER BY
sch.name
,systbl.name
,systrg.name
Necromancing。
只是发布,因为到目前为止所有的解决方案都不够完整。
SELECT
sch.name AS trigger_table_schema
,systbl.name AS trigger_table_name
,systrg.name AS trigger_name
,sysm.definition AS trigger_definition
,systrg.is_instead_of_trigger
-- https://stackoverflow.com/questions/5340638/difference-between-a-for-and-after-triggers
-- Difference between a FOR and AFTER triggers?
-- CREATE TRIGGER trgTable on dbo.Table FOR INSERT,UPDATE,DELETE
-- Is the same as
-- CREATE TRIGGER trgTable on dbo.Table AFTER INSERT,UPDATE,DELETE
-- An INSTEAD OF trigger is different, and fires before and instead of the insert
-- and can be used on views, in order to insert the appropriate values into the underlying tables.
-- AFTER specifies that the DML trigger is fired only when all operations
-- specified in the triggering SQL statement have executed successfully.
-- All referential cascade actions and constraint checks also must succeed before this trigger fires.
-- AFTER is the default when FOR is the only keyword specified.
,CASE WHEN systrg.is_instead_of_trigger = 1 THEN 0 ELSE 1 END AS is_after_trigger
,systrg.is_not_for_replication
,systrg.is_disabled
,systrg.create_date
,systrg.modify_date
,CASE WHEN systrg.parent_class = 1 THEN 'TABLE' WHEN systrg.parent_class = 0 THEN 'DATABASE' END trigger_class
,CASE
WHEN systrg.[type] = 'TA' then 'Assembly (CLR) trigger'
WHEN systrg.[type] = 'TR' then 'SQL trigger'
ELSE ''
END AS trigger_type
-- https://dataedo.com/kb/query/sql-server/list-triggers
-- ,(CASE WHEN objectproperty(systrg.object_id, 'ExecIsUpdateTrigger') = 1
-- THEN 'UPDATE ' ELSE '' END
-- + CASE WHEN objectproperty(systrg.object_id, 'ExecIsDeleteTrigger') = 1
-- THEN 'DELETE ' ELSE '' END
-- + CASE WHEN objectproperty(systrg.object_id, 'ExecIsInsertTrigger') = 1
-- THEN 'INSERT' ELSE '' END
-- ) AS trigger_event
,
(
STUFF
(
(
SELECT
', ' + type_desc AS [text()]
-- STRING_AGG(type_desc, ', ') AS foo
FROM sys.events AS syse
WHERE syse.object_id = systrg.object_id
FOR XML PATH(''), TYPE
-- GROUP BY syse.object_id
).value('.[1]', 'nvarchar(MAX)')
, 1, 2, ''
)
) AS trigger_event_groups
-- ,CASE WHEN systrg.parent_class = 1 THEN 'TABLE' WHEN systrg.parent_class = 0 THEN 'DATABASE' END trigger_class
,'DROP TRIGGER "' + sch.name + '"."' + systrg.name + '"; ' AS sql
-- ,systrg.*
FROM sys.triggers AS systrg
LEFT JOIN sys.sql_modules AS sysm
ON sysm.object_id = systrg.object_id
-- sys.objects for view triggers
-- LEFT JOIN sys.objects AS systbl ON systbl.object_id = systrg.object_id
-- inner join if you only want table-triggers
LEFT JOIN sys.tables AS systbl ON systbl.object_id = systrg.parent_id
LEFT JOIN sys.schemas AS sch
ON sch.schema_id = systbl.schema_id
WHERE (1=1)
-- AND sch.name IS NOT NULL
-- AND sch.name IS NULL
-- AND sch.name = 'dbo'
-- And here, exclude some triggers with a certain naming schema
/*
AND
(
-- systbl.name IS NULL
-- OR
NOT
(
systrg.name = 'TRG_' + systbl.name + '_INSERT_History'
OR
systrg.name = 'TRG_' + systbl.name + '_UPDATE_History'
OR
systrg.name = 'TRG_' + systbl.name + '_DELETE_History'
)
)
*/
ORDER BY
sch.name
,systbl.name
,systrg.name
这是我使用的(通常包装在模型中):
Select
[Parent] = Left((Case When Tr.Parent_Class = 0 Then '(Database)' Else Object_Name(Tr.Parent_ID) End), 32),
[Schema] = Left(Coalesce(Object_Schema_Name(Tr.Object_ID), '(None)'), 16),
[Trigger name] = Left(Tr.Name, 32),
[Type] = Left(Tr.Type_Desc, 3), -- SQL or CLR
[MS?] = (Case When Tr.Is_MS_Shipped = 1 Then 'X' Else ' ' End),
[On?] = (Case When Tr.Is_Disabled = 0 Then 'X' Else ' ' End),
[Repl?] = (Case When Tr.Is_Not_For_Replication = 0 Then 'X' Else ' ' End),
[Event] = Left((Case When Tr.Parent_Class = 0
Then (Select Top 1 Left(Te.Event_Group_Type_Desc, 40)
From Sys.Trigger_Events As Te
Where Te.Object_ID = Tr.Object_ID)
Else ((Case When Tr.Is_Instead_Of_Trigger = 1 Then 'Instead Of ' Else 'After ' End)) +
SubString(Cast((Select [text()] = ', ' + Left(Te.Type_Desc, 1) + Lower(SubString(Te.Type_Desc, 2, 32)) +
(Case When Te.Is_First = 1 Then ' (First)' When Te.Is_Last = 1 Then ' (Last)' Else '' End)
From Sys.Trigger_Events As Te
Where Te.Object_ID = Tr.Object_ID
Order By Te.[Type]
For Xml Path ('')) As Character Varying), 3, 60) End), 60)
-- If you like:
-- , [Get text with] = 'Select Object_Definition(' + Cast(Tr.Object_ID As Character Varying) + ')'
From
Sys.Triggers As Tr
Order By
Tr.Parent_Class, -- database triggers first
Parent -- alphabetically by parent
正如你所看到的,这是一个McGyver,但我认为这是值得的:
Parent Schema Trigger name Type MS? On? Repl? Event
-------------------------------- ---------------- -------------------------------- ---- ---- ---- ----- -----------------------------------------
(Database) (None) ddlDatabaseTriggerLog SQL X DDL_DATABASE_LEVEL_EVENTS
Employee HumanResources dEmployee SQL X Instead Of Delete
Person Person iuPerson SQL X After Insert, Update
PurchaseOrderDetail Purchasing iPurchaseOrderDetail SQL X X After Insert
PurchaseOrderDetail Purchasing uPurchaseOrderDetail SQL X X After Update
PurchaseOrderHeader Purchasing uPurchaseOrderHeader SQL X X After Update
SalesOrderDetail Sales iduSalesOrderDetail SQL X X After Insert, Update, Delete
SalesOrderHeader Sales uSalesOrderHeader SQL X After Update (First)
Vendor Purchasing dVendor SQL X Instead Of Delete
WorkOrder Production iWorkOrder SQL X X After Insert
WorkOrder Production uWorkOrder SQL X X After Update
(向右滚动可以看到最后一列,也是最有用的一列)