Geek Answers Handbook

How to check a table for new data?

I need to create a stored procedure that, after checking, checks to see if new rows have been added to the table in the last 12 hours. Otherwise, the recipient must send a warning letter.

I have email sending procedures, but the problem is the request itself. I guess I will have to make a sql command that uses the current date and compares it with the dates in the strings. But I'm a complete newbie to 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 the dates and then return the result based on whether new lines were created in the last 12 hours and use this result to decide whether to send email?

+4
source share
5 answers

Say your date field in the table is "CreateDate" and the DateTime type is. Your time for comparison: GETDATE () (which returns the date + time) To get the datetime value 12 hours before, is done with DATEADD: DATEADD (hour, -12, GETDATE ())

therefore, if we want to add the number of rows in the last 12 hours, we will do the following:

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

in your proc, you must save the result of this request in a variable and check if it is> 0, therefore:

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

and then you check the @amount variable if it is> 0.

+5
source

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 ask additional questions.

Cheers, John

+6
source

You can use a trigger, this link has some 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 can copy the data to another table, then inspect this table every 12 hours, report the rows in it, and then delete them ...

0
source

provided that you have on this table: - either a unique identification auto-increment - or the created_timestamp field containing the timestamp for creating the row

-> have a new table

 reported_rows - report_timestamp - last_id_seen (OR) - last_timestamp_seen

fill in the reported line every time you send your email with the actual value and check the previous values ​​before sending the email so that you know which lines were added.

0
source

If the table has an identification field, you can also save the maximum value (as a bookmark) and next time check if there are any rows with an identifier larger than the saved bookmark. May be faster if the key is a clustered key.

0
source

More articles:


All Articles