Solved

Email alert when data is written to a table

Posted on 2013-10-25
4
252 Views
Last Modified: 2013-10-25
Trying to reduce expensive developer time on our DotNetNuke website. I don't think I need to go in to the backstory so I will simply ask - is there a way I can set up an alert (email, preferably) so that I am notified whenever anything is written to a specific SQL Server database table?
0
Comment
Question by:K_Deutsch
  • 2
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
strivoli earned 500 total points
ID: 39600714
Yes. Use a trigger on INSERT or UPDATE that sends a mail to an operator using SQL's built in mail system.
0
 

Author Comment

by:K_Deutsch
ID: 39600881
I set up an email account in SQL Server Database Mail and sent a successful test email, but I need a little more direction on building the alert, as this is new to me.

Database: ABC
Table: sp_ABC_DB_OnlineFormsConfirm
0
 

Author Comment

by:K_Deutsch
ID: 39601016
Actually I am almost there. The below was successfully tested, but is there a way that in the email generated I can insert the unique ConfirmationCode that is captured so the alerted person knows what exactly to follow up on?

CREATE TRIGGER SendEmail
ON [ABC].[dbo].[ABC_DB_OnlineFormsConfirm]
AFTER INSERT
AS
exec msdb.dbo.sp_send_dbmail @profile_name = 'Gmail',
            @recipients = 'ABC@ABC-inc.com',
            @subject='An ABC Online App Has Been Completed',
            @body=''
0
 
LVL 19

Expert Comment

by:strivoli
ID: 39601353
You should be able to add any data to the mail. Even the single field values.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help how to find where my error is in UFD 6 47
SQL Recursion 6 34
SQL Recursion schedule 13 35
SQL- GROUP BY 4 25
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question