How to get procedure text before ALTER from DDL trigger

Question

I am creating a trigger to track how procedure text has been ALTERed.

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

Even after investigating EVENTDATA(), it did not contain values for previous definition of procedure before ALTER.

Is there a way to retrieve previous text like how it's possible to access deleted values in DML triggers using 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 runs after the change was made, so as far as I can tell there is no access to the "before" values. The EVENTDATA() is defined and there is no provision for "previous". As a result, you'll have to just record the current value in the log. However, if you prepopulate your log 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 could use your trigger and still have a complete picture of all the changes. Your log would have all previous versions as well as the current version of each procedure. You could use my version of the trigger (see below), where you would have access to some of the other columns from sys.objects and sys.sql_modules, like:

uses_ansi_nulls
uses_quoted_identifier
is_schema_bound
null_on_null_input
principal_id

which might be handy to log as well. 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 [email protected]('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)')
                --modify as necessary AND type='P'

end
GO

Answer 2

procedure how to repair mmc corrupted?