Use trigger to monitor stored procedures in SQL Server to change script instances _mssql

The following trigger are used to monitor changes to stored procedures.

To create a monitoring table:

CREATE TABLE auditstoredprocedures (
 DatabaseName sysname
, objectname sysname
, LoginName sysname
, ChangeDate datetime
, eventtype sysname
, eventdataxml xml
);
 

To create a monitoring trigger:

CREATE TRIGGER dbtauditstoredprocedures
on DATABASE
for Create_procedure, Alter_procedure, drop_procedure
as
 
DECLARE @eventdata XML;
SET @eventdata = EventData ();
 
INSERT intoauditstoredprocedures (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
);