[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I trigger an alert/notification when a certain database field changes to a value of "0"

Posted on 2011-05-10
3
Medium Priority
?
563 Views
Last Modified: 2012-05-11
I have an inventory database in SQL2008. Database Mail is fully configured. I would like to set up a notification/alert email when the value in the Inventory database field called “QtyAvail” changes to zero or below.

What is the must efficient and less complicated way of accomplishing this.

Database: SolomonAPP
Table: Itemsite
Field: QtyAvail

Your guidance is greatly appreciated.
0
Comment
Question by:armgon
3 Comments
 
LVL 22

Accepted Solution

by:
Om Prakash earned 2000 total points
ID: 35735165
You can create a trigger and when the field is updated to 0, you can send mail.

Sending SMTP Mail using a Stored Procedure
http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure

Database Mail in SQL Server 2005
http://www.databasejournal.com/features/mssql/article.php/3626056

example:
http://www.sqlservercentral.com/Forums/Topic749474-1291-1.aspx
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35736077
Create TRigger Trigger name on TAble for Update

Select Id, QTYAvail from updated
Select QTYAvail from Table where ID in (Select Idfrom updated)

IF QTYAvail.update <> QTYAvail.Table
begin
If QTYAvail.<=0
begin
exec sp_senddbmail '', ''
end
end
end
 

0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35736446
@PatelAlpesh, How is a piece of code with 5 syntax errors in it supposed to help in answering this question?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

834 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