DB Change Tracking Alert on My Cell

Hello Friends, 

Recently I have been shared My Post on Change Tracking Log (winlinuxhelp.com) described how I am storing day to day Database schema change logs Of all server for Reporting &  System Analysis purpose. I want to continue writing about this Topic .. 
So today I will share about Change Alert which I have been configured into system to alert on every schema changes into my cell.

To Create run below Script

Create TRIGGER safety1

ON DATABASE

FOR

CREATE_TABLE,ALTER_TABLE,DROP_TABLE,

CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE,

CREATE_VIEW,ALTER_VIEW,DROP_VIEW

AS

declare @object int                     

declare @hr int     

declare @rcnum varchar(200)

declare @str varchar(200)   

          

SELECT @str=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

--print @str

exec @hr = sp_OACreate 'jmail.smtpmail', @object OUT                     

exec @hr = sp_OASetProperty @object, 'Sender', 'sumank@tulip.net'                     

exec @hr = sp_OASetProperty @object, 'ServerAddress', 'smtp3.xxxxxxxx.com'

exec @hr = sp_OASetProperty @object, 'HTMLBody', @str

set @rcnum = cast(ltrim(rtrim(9582111165)) as varchar)+'@am.xxxxxxxxx.com'   

exec @hr = sp_OAMethod @object, 'AddRecipient', NULL ,  @rcnum             

exec @hr = sp_OAMethod @object, 'execute', NULL             

exec @hr = sp_OADestroy @object

To Check  run below :

SELECT * FROM sys.triggers

To Disable/Enable  :

DISABLE TRIGGER safety1 DATABASE

ENABLE TRIGGER safety1 DATABASE

To Test :

Now Alter any procedure from current db and wait for the SMS.

You Will  receive -- 'Alter ............................ '

Happy Reading

SumnJha