How to programatically send alert after every change to database objects in T-SQL

Question

I have a trigger that monitors changes to database objects. Below is the code when I created the trigger:

CREATE TRIGGER db_trg_ObjectChanges
ON DATABASE
FOR ALTER_PROCEDURE, DROP_PROCEDURE,
 ALTER_INDEX, DROP_INDEX,
 ALTER_TABLE, DROP_TABLE, ALTER_TRIGGER, DROP_TRIGGER,
 ALTER_VIEW, DROP_VIEW, ALTER_SCHEMA, DROP_SCHEMA,
 ALTER_ROLE, DROP_ROLE, ALTER_USER, DROP_USER
AS
SET NOCOUNT ON
INSERT dbo.ChangeAttempt
(EventData, DBUser)
VALUES (EVENTDATA(), USER)
GO

The table dbo.ChangeAttempt has the following structure:

Column           Data Type       Constraints        
------           ---------       -----------

EventData        xml             NOT NULL
AttemptDate      datetime        NOT NULL DEFAULT GETDATE()
DBUser           char(50)        NOT NULL

I'd like to be alerted if any entries are found in the table dbo.ChangeAttempt for the current date. The email will just tell me that entries were found in dbo.ChangeAttempt on the current date. I am using blat or bmail in sending most of my monitoring alerts. I do select statements and output it to text file and then email the output to me.

My question is: How do I programatically send alert if there has been entries found in the dbo.ChangeAttemptfor the current date(an indication of database change attempt)?

Answer 1

Rather than deal with dates, you could just set a bit field to indicate that you've acknowledged the change - the trigger sets it to one, and the e-mail notifier resets it to 0. Add an index to the bit field; job done... (an index on a bit field is effective as long as the data is heavily skewed, i.e. relatively few set bits).

You would probably do this in combination of setting an audit date, and/or a separate audit table.

You may also choose to look at service-broker for async processing at the server.

Answer 2

create a nightly SQL job (run just after midnight) to query the ChangeAttempt table for any AttempDate values of the previous day, use xp_cmdshell to run a command line e-mail program or just use xp_sendmail.

if you want it in real time, add a trigger to ChangeAttempt and issue the xp_cmdshell or xp_sendmail from there.