sql - 如何从 DDL 触发器获取 ALTER 之前的过程文本

Question

我正在创建一个触发器来跟踪过程文本如何ALTER编。

在数据库 DDL 触发器中，
可以通过 /EVENT_INSTANCE/TSQLCommand 访问当前程序文本.

即使经过调查 EVENTDATA() ，它不包含之前定义过程的值 ALTER .

有没有办法检索以前的文本，例如如何使用 DELETED 访问 DML 触发器中已删除的值？ table ？

create trigger trgDDLAuditQuery
on  database
for     alter_procedure
as 
begin
    set nocount on;

    declare @data xml
    set @data = EVENTDATA()

    insert  dbo.tblQueryAudit(ObjectName, TSQLCommand)
    select  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)'), 
        --; Only gets currently changed procedure text, not previous one
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
end
GO

Answer 1

触发器在进行更改后运行，因此据我所知，无法访问“之前”值。 EVENTDATA() 被定义并且没有“previous”的规定。因此，您只需要在日志中记录当前值。但是，如果您使用以下命令预填充日志:

    INSERT INTO dbo.tblQueryAudit
            (ObjectName, TSQLCommand)
    SELECT
        o.Name,m.definition
        FROM sys.objects                 o
            INNER JOIN sys.sql_modules   m ON o.object_id=m.object_id
        WHERE type='P'

您可以使用触发器，并且仍然可以完整了解所有更改。您的日志将包含所有以前的版本以及每个过程的当前版本。您可以使用我的触发器版本(见下文)，您可以在其中访问来自 sys.objects 和 sys.sql_modules 的其他一些列，例如:

uses_ansi_nulls
uses_quoted_identifier
is_schema_bound
null_on_null_input
principal_id

这也可能便于记录。替代版本:

CREATE trigger trgDDLAuditQuery
on  database
for             alter_procedure
as 
begin
    set nocount on;

    DECLARE @EventData      xml
    SET @EventData=EVENTDATA()

    INSERT INTO dbo.tblQueryAudit
            (ObjectName, TSQLCommand) --hope you have  datetime column that defaults to GETDATE()
        SELECT
            o.Name,m.definition
            FROM sys.objects                 o
                INNER JOIN sys.sql_modules   m ON o.object_id=m.object_id
            WHERE o.Name=@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)')
                --modify as necessary AND type='P'

end
GO