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

Posted on 2011-05-10
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.
Question by:armgon
    LVL 22

    Accepted Solution

    You can create a trigger and when the field is updated to 0, you can send mail.

    Sending SMTP Mail using a Stored Procedure

    Database Mail in SQL Server 2005

    LVL 21

    Expert Comment

    by:Alpesh Patel
    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
    If QTYAvail.<=0
    exec sp_senddbmail '', ''

    LVL 22

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now