SQL-Server schema audit trigger - possible to get old view/sp/function definition?

I'm using the below database-level trigger for schema-change-auditing.
It works fine, but if I do (drop view - create view), it only stores the sql that created the view, not the one that is dropped. Say on drop procedure, is there a universal way to save the procedure definition of the old version of the procedure as well ? IF NOT EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Schema_Change_Audit_Trigger') EXECUTE('CREATE TRIGGER [Schema_Change_Audit_Trigger] ON DATABASE FOR CREATE_USER AS BEGIN SELECT 123 AS abc END;'); GO /* IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Schema_Change_Audit_Trigger') DROP TRIGGER Schema_Change_Audit_Trigger ON DATABASE GO */ ALTER TRIGGER Schema_Change_Audit_Trigger ON DATABASE FOR -- https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events?view=sql-server-2017 -- https://stackoverflow.com/questions/1255947/determine-what-user-created-objects-in-sql-server -- https://blog.sqlauthority.com/2015/09/12/sql-server-who-dropped-table-or-database/ -- https://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio CREATE_USER, ALTER_USER, DROP_USER ,CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA ,CREATE_TABLE, ALTER_TABLE, DROP_TABLE ,CREATE_VIEW, ALTER_VIEW, DROP_VIEW ,CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE ,CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION ,CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER ,CREATE_TYPE, DROP_TYPE ,CREATE_INDEX, ALTER_INDEX, DROP_INDEX ,CREATE_QUEUE, ALTER_QUEUE, DROP_QUEUE ,RENAME -- https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/ AS BEGIN SET NOCOUNT ON; DECLARE @EventData XML; DECLARE @operation_ip varchar(48); DECLARE @operation_principals AS xml; DECLARE @operation_first_principal AS national character varying(256); SET @EventData = EVENTDATA(); SET @operation_ip = CONVERT(varchar(48), CONNECTIONPROPERTY('client_net_address')); SET @operation_principals = ( SELECT lt.name FROM sys.login_token AS lt INNER JOIN sys.server_principals AS sp ON lt.principal_id = sp.principal_id WHERE (1=1) AND lt.name COLLATE Latin1_General_CI_AS NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R' AND name IS NOT NULL) AND lt.name COLLATE Latin1_General_CI_AS NOT IN (SELECT name FROM sys.server_principals WHERE type = 'R' AND name IS NOT NULL ) AND lt.name IS NOT NULL GROUP BY lt.name ORDER BY name FOR XML PATH(''), ROOT ('names') ); SET @operation_first_principal = ( SELECT TOP 1 p.c.value('.', 'nvarchar(256)') AS names FROM @operation_principals.nodes('//name') AS p(c) ); IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'schema_audit' AND TABLE_NAME = 'ddl_events') RETURN; INSERT schema_audit.ddl_events ( audit_event_type ,audit_schema_name ,audit_object_name ,audit_sql_command ,audit_event_xml ,audit_ip_address ,audit_host_name ,audit_app_name ,audit_principal ,audit_principals ,audit_user ,audit_current_user ,audit_session_user ,audit_system_user ,audit_user_name -- same as user ,audit_suser_name ,audit_suser_sname ,audit_original_login ,audit_is_sysadmin ,audit_is_db_owner ,audit_is_ddl_admin ,audit_is_db_datareader ,audit_machine_name ,audit_instance_name ,audit_server_name ,audit_netbios_name ,audit_database_name ,audit_net_transport ,audit_protocol_type ,audit_auth_scheme ,audit_local_net_address ,audit_local_tcp_port ,audit_client_net_address ,audit_physical_net_transport ) SELECT -- Did what ? @EventData.value('(/EVENT_INSTANCE/EventType)', 'NVARCHAR(100)') AS audit_event_type ,@EventData.value('(/EVENT_INSTANCE/SchemaName)', 'NVARCHAR(256)') AS audit_schema_name ,@EventData.value('(/EVENT_INSTANCE/ObjectName)', 'NVARCHAR(256)') AS audit_object_name ,@EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'NVARCHAR(MAX)') AS audit_sql_command ,@EventData AS audit_event_xml -- Where from ? ,@operation_ip AS audit_ip_address ,HOST_NAME() AS audit_host_name ,APP_NAME() AS audit_app_name -- Who did it ? ,@operation_first_principal AS audit_principal ,@operation_principals AS audit_principals ,CAST(USER AS nvarchar(256)) AS audit_user ,CAST(CURRENT_USER AS nvarchar(256)) AS audit_current_user ,CAST(SESSION_USER AS nvarchar(256)) AS audit_session_user ,CAST(SYSTEM_USER AS nvarchar(256)) AS audit_system_user ,CAST(USER_NAME() AS nvarchar(256)) AS audit_user_name -- same as user ,CAST(SUSER_NAME() AS nvarchar(256)) AS audit_suser_name ,CAST(SUSER_SNAME() AS nvarchar(256)) AS audit_suser_sname ,CAST(ORIGINAL_LOGIN() AS nvarchar(256)) AS audit_original_login -- What rights did said person have ? ,IS_SRVROLEMEMBER('sysadmin') AS audit_is_sysadmin ,IS_MEMBER('db_owner') AS audit_is_db_owner ,IS_MEMBER('db_ddladmin') AS audit_is_ddl_admin ,IS_MEMBER('db_datareader') AS audit_is_db_datareader -- On which server was this done ? ,CAST(SERVERPROPERTY(N'MachineName') AS nvarchar(255)) AS audit_machine_name ,CAST(SERVERPROPERTY(N'InstanceName') AS nvarchar(255)) AS audit_instance_name ,CAST(SERVERPROPERTY(N'ServerName') AS nvarchar(255)) AS audit_server_name ,CAST(SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS nvarchar(255)) AS audit_netbios_name ,DB_NAME() AS audit_database_name -- Connection information ,CAST(ConnectionProperty('net_transport') AS nvarchar(255)) AS audit_net_transport ,CAST(ConnectionProperty('protocol_type') AS nvarchar(255)) AS audit_protocol_type ,CAST(ConnectionProperty('auth_scheme') AS nvarchar(255)) AS audit_auth_scheme ,CAST(ConnectionProperty('local_net_address') AS nvarchar(255)) AS audit_local_net_address ,CAST(ConnectionProperty('local_tcp_port') AS nvarchar(255)) AS audit_local_tcp_port ,CAST(ConnectionProperty('client_net_address') AS nvarchar(255)) AS audit_client_net_address ,CAST(ConnectionProperty('physical_net_transport') AS nvarchar(255)) AS audit_physical_net_transport WHERE (1=1) -- every morning, index compression of all indices at 05:15 -- we don't want to to log that AND ( @EventData.value('(/EVENT_INSTANCE/EventType)', 'NVARCHAR(100)') IS NULL OR @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'NVARCHAR(MAX)') IS NULL OR NOT ( @EventData.value('(/EVENT_INSTANCE/EventType)', 'NVARCHAR(100)') = 'ALTER_INDEX' AND ( @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'NVARCHAR(MAX)') LIKE '%REORGANIZE%' AND @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'NVARCHAR(MAX)') LIKE '%LOB_COMPACTION%' ) ) ); END GO Log table: IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'schema_audit' ) EXECUTE('CREATE SCHEMA schema_audit; '); GO -- IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'schema_audit' ) EXECUTE('DROP TABLE schema_audit.ddl_events; '); IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'schema_audit' AND TABLE_NAME = 'ddl_events' ) BEGIN CREATE TABLE schema_audit.ddl_events ( audit_uid uniqueidentifier NOT NULL DEFAULT NEWID() ,audit_event_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,audit_event_type national character varying(64) ,audit_schema_name national character varying(256) ,audit_object_name national character varying(256) ,audit_sql_command national character varying(MAX) ,audit_event_xml xml ,audit_ip_address character varying(48) ,audit_host_name national character varying(256) ,audit_app_name national character varying(256) ,audit_principal national character varying(256) ,audit_principals xml ,audit_user national character varying(256) ,audit_current_user national character varying(256) ,audit_session_user national character varying(256) ,audit_system_user national character varying(256) ,audit_user_name national character varying(256) -- same user ,audit_suser_name national character varying(256) ,audit_suser_sname national character varying(256) ,audit_original_login national character varying(256) ,audit_is_sysadmin int ,audit_is_db_owner int ,audit_is_ddl_admin int ,audit_is_db_datareader int ,audit_machine_name national character varying(256) ,audit_instance_name national character varying(256) ,audit_server_name national character varying(256) ,audit_netbios_name national character varying(256) ,audit_database_name national character varying(256) ,audit_net_transport national character varying(256) ,audit_protocol_type national character varying(256) ,audit_auth_scheme national character varying(256) ,audit_local_net_address national character varying(256) ,audit_local_tcp_port national character varying(256) ,audit_client_net_address national character varying(256) ,audit_physical_net_transport national character varying(256) ,CONSTRAINT PK_ddl_events PRIMARY KEY( audit_uid ) ); END