DDL 触发器用于一切

Question

我得到了著名的DDLtriger来触发数据库中发生的一切

https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

在示例中，它被设置

FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE

但我假装为每个 DDL 更改扩展，所以我搜索： https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events

超过200个活动！

我们可以使用类似的东西吗？

CREATE TRIGGER DDLTrigger_Sample
ON DATABASE
FOR ABSOLUTELY_EVERYTHING
AS
BEGIN

Answer 1

简短的回答 - 是的。DDL 触发器事件是分层的。您可以在sys.trigger_event_types中看到关系。例如，让我们看一下DROP_PROCEDURE存在的层次结构，我们可以使用此查询：

WITH e AS (
SELECT tet.type ,
tet.type_name ,
tet.parent_type, 1 AS level
FROM sys.trigger_event_types AS tet
WHERE tet.type_name = 'DROP_PROCEDURE'
UNION ALL
SELECT parent.type ,
parent.type_name ,
parent.parent_type, child.level + 1 AS level
FROM e AS child
JOIN sys.trigger_event_types AS parent
ON child.parent_type = parent.type
)
SELECT *
FROM e
ORDER BY level DESC;

由此，我可以看到(在我的服务器上(DROP_PROCEDURE属于DDL_PROCEDURE_EVENTS、、DDL_DATABASE_LEVEL_EVENTS和DDL_EVENTS(按粒度递减的顺序(。假设您选择了DDL_DATABASE_LEVEL_EVENTS，您可以找出以下内容将涵盖的所有内容。

WITH e AS (
SELECT tet.type ,
tet.type_name ,
tet.parent_type, 1 AS level
FROM sys.trigger_event_types AS tet
WHERE tet.type_name = 'DDL_DATABASE_LEVEL_EVENTS'
UNION ALL
SELECT child.type ,
child.type_name ,
child.parent_type ,
parent.level + 1 AS level
FROM e AS parent
JOIN sys.trigger_event_types AS child
ON child.parent_type = parent.type
)
SELECT *
FROM e
ORDER BY level