How to get the text of a procedure before ALTER from a DDL trigger

Question

I am creating a trigger to track how the procedure text was ALTER ed.

Inside a DDL database trigger, it is possible to access the current Text procedure through /EVENT_INSTANCE/TSQLCommand .

Even after examining EVENTDATA() it did not contain a value for the previous procedure definition before ALTER .

Is there a way to get the previous text, for example, how can I access remote values ​​in DML triggers using the DELETED 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

The trigger starts after making a change, as far as I can tell, there is no access to the values ​​"earlier". EVENTDATA () is defined and the previous condition is not provided. As a result, you will just need to write the current value in the log. However, if you pre-populated your journal with this command:

  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' 

you can use your trigger and still have a complete picture of all the changes. Your log will contain all previous versions, as well as the current version of each procedure. You can use my trigger version (see below), where you will have access to some other columns from sys.objects and sys.sql_modules, for example:

 uses_ansi_nulls uses_quoted_identifier is_schema_bound null_on_null_input principal_id 

which may also be useful for registration. alternative version:

 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