January 14, 2013 at 3:47 pm
I have a user table I want to monitor. I would like to know anytime a row is added. Does anyone know how to create something to do this? Thanks in advance!
January 14, 2013 at 8:23 pm
Something like this would work, just tweak it to suit your needs...I do something similar to monitor the SQL Developers when they change SQL Agent Jobs 😀
CREATE TRIGGER [dbo].[tr_NewUserRecordAdded] ON [dbo].[UserTable]
AFTER INSERT
AS
DECLARE @ErrNo varchar(255),
@ErrMsg varchar(255),
@Body varchar(max),
@Subject varchar(255) = '',
@To varchar(255) = '',
@Bcc varchar(255) = '',
@UserName VARCHAR (50),
@HostName VARCHAR(50)
SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME(), @To = 'you@company.com'
BEGIN TRY
IF (SELECT COUNT(1) FROM INSERTED) > 0
BEGIN
/* You could also get the values from the INSERTED table here and assign them so they can be embedded in te body of the email */
SET @Body = 'A new record has been added by ' + @UserName + CHAR(13)
END
EXEC msdb .dbo. sp_send_dbmail
@recipients = @To,
@body = @Body,
@subject = @Subject
END TRY
BEGIN CATCH
SELECT
@ErrNo = ERROR_NUMBER(),
@ErrMsg = ERROR_MESSAGE()
SET @ErrMsg = ' :: ERROR :: ' + @ErrNo + ' <<Add Custom Msg here>>'
SET @Subject = CAST(@@SERVERNAME AS varchar) + @ErrMsg
SET @Body = '----------------------------------------------------------------------------------------------------'
+ CHAR(13) + @ErrMsg + CHAR(13)
+ '----------------------------------------------------------------------------------------------------'
+ CHAR(13) + '<<Enter additional your blurb here>>' + CHAR(13) + CHAR(13)
+ 'The DBA''s have been notified about the cause of this error and can assure you that the public flogging of the developer responsible for this error will be severe.'
EXEC msdb.dbo.sp_send_dbmail @recipients = @To, @blind_copy_recipients = @Bcc, @subject = @Subject, @body = @Body, @importance = 'High'
END CATCH
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply