SQL Server uses Trigger to monitor the stored procedure change script instance, trigger Stored Procedure

SQL Server uses Trigger to monitor the stored procedure change script instance, trigger Stored Procedure

The following triggers are used to monitor changes to stored procedures.
 
Create a monitoring table:
 

Create table AuditStoredProcedures (DatabaseName sysname, ObjectName sysname, LoginName sysname, ChangeDate datetime, EventType sysname, EventDataXml xml );

Create monitoring Trigger:

Create trigger export DATABASEFOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDUREAS DECLARE @ eventdata XML; SET @ eventdata = EVENTDATA (); INSERT into (DatabaseName, ObjectName, LoginName, ChangeDate, EventType, EventDataXml) VALUES (@ eventdata. value ('(/EVENT_INSTANCE/DatabaseName) [1]', 'sysname'), @ eventdata. value ('(/EVENT_INSTANCE/ObjectName) [1]', 'sysname'), @ eventdata. value ('(/EVENT_INSTANCE/LoginName) [1]', 'sysname'), GETDATE (), @ eventdata. value ('(/EVENT_INSTANCE/EventType) [1]', 'sysname'), @ eventdata );