Q

SQL Server trigger vs. stored procedure to receive data notification

by:
Roman Rehak

Learn why using a SQL Server stored procedures vs. a trigger can be a preferred method to retrieve data notification from a database field.

I am using SQL Server 2000, and I have a database field for warranty expiration dates. I would like to know 30 days in advance when an item is about to go out of warranty. How would I go about creating a trigger to inform me of this?
A trigger would not work well in this example. A trigger executes when you perform an INSERT/UPDATE/SELECT command on a row or rows of data, and it is typically only used in relation to the data just updated. If the data is not updated, no trigger would fire, either. In your case, it seems that you need something like a notification, instead.

I would develop a stored procedure to return the count of items too close to the warranty expire date. In the query, I would use the DATEDIFF function to find out if there are any matching rows. Then, I would create a scheduled job and have an email sent to me if the count is greater than zero.

This was first published in October 2008

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Related Q&A; from Roman Rehak, Contributor

Tips for tuning SQL Server 2005 to improve reporting performance

There are a few things you can do to tune SQL Server for improved reporting performance. Here are some generic and Reporting Services-specific ...continue reading

Manipulate column names in a SQL Server table

Learn SQL Server commands to manipulate column names in a table and turn column names into variables or rename SQL columns.continue reading

Storing and searching for image files in SQL Server

Learn about working with image files in SQL Server, including advice on using queries to locate them, in this expert answer.continue reading

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Meet all of our SQL Server experts

View all SQL Server questions and answers

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close