SQL - how to check table for new data?

Question

Hey,

I need to create a stored procedure that upon exceution checks if any new rows have been added to a table within the past 12 hours. If not, an warning email must be sent to a recipient.

I have the procedures for sending the email, but the problem is the query itself. I imagine I'd have to make an sql command that uses current date and compares that to the dates in the rows. But I'm a complete beginner in SQL so I can't even use the right words to find anything on google.

Short version:

Using MS SQL Server 2005, how can I check against the dates, then return a result based on whether new rows were created within the last 12 hours, and use that result to decide whether or not to send email?

Answer 1

Hi,

Something like this should do what you wish.

Select ID
from TableName
where CreatedDate >= dateadd(hour,-12,getDate())

Hope this is clear but please feel free to pose further questions.

Cheers, John

Answer 2

Say your date field in the table is 'CreateDate' and it's of type DateTime. Your time to compare with is: GETDATE() (which returns date + time) To get the datetime value of 12 hours before that, is done using DATEADD: DATEADD(hour, -12, GETDATE())

so if we want the # of rows added in the last 12 hours, we'll do:

SELECT COUNT(*)
FROM Table
WHERE CreateDate >= DATEADD(hour, -12, GETDATE())

in your proc, you've to store the result of this query into a variable and check if it's > 0, so:

DECLARE @amount int
SELECT @amount=COUNT(*)
FROM Table
WHERE CreateDate >= DATEADD(hour, -12, GETDATE())

and then you'll check the @amount variable if it's > 0.

Answer 3

You could use a trigger, this link has several examples: http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER reminder
GO

CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE 
AS
   EXEC master..xp_sendmail 'MaryM', 
      'Don''t forget to print a report for the distributors.'
GO

If you do not want something for each insert/update, you could copy data to a another table then examine that table every 12 hours, report on the rows in it, then delete them...

Answer 4

assuming you have on this table : - either a unique id autoincrementing - either a created_timestamp field containing the timestamp of creation of the row

-> have a new table

reported_rows
  - report_timestamp
  - last_id_seen
 (OR)
  - last_timestamp_seen

fill the reported row each time you send your email with the actual value and before sending the email, check with the previous values, so you know what rows have been added

Answer 5

Thank you! Exactly what I was looking for. :)

Answer 6

If the table has an identity field, you could also save the max value (as a bookmark) and next time check if there are any rows with an ID greater than your saved bookmark. May be faster if the key is the clustered key.