SQL - 如何检查表中的新数据？

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.

我需要创建一个存储过程,在执行检查时是否在过去12小时内将任何新行添加到表中。如果没有,则必须向收件人发送警告电子邮件。

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.

我有发送电子邮件的程序,但问题是查询本身。我想我必须创建一个使用当前日期的sql命令,并将其与行中的日期进行比较。但我在SQL中是一个完全的初学者,所以我甚至无法使用正确的单词在谷歌上找到任何东西。

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?

使用MS SQL Server 2005,如何检查日期,然后根据是否在过去12小时内创建新行返回结果,并使用该结果来决定是否发送电子邮件?

5 个解决方案

#1

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())

假设表中的日期字段是'CreateDate',它的类型是DateTime。您与之比较的时间是:GETDATE()(返回日期+时间)要在此之前获得12小时的日期时间值,请使用DATEADD完成:DATEADD(小时,-12,GETDATE())

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

因此,如果我们想要在过去12小时内添加的行数,我们会这样做:

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:

在你的proc中,你要将这个查询的结果存储到一个变量中并检查它是否> 0,所以:

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.

然后你会检查@amount变量是否> 0。

#2

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

#3

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

你可以使用一个触发器,这个链接有几个例子: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...

如果您不想为每个插入/更新添加一些内容,您可以将数据复制到另一个表,然后每12小时检查一次该表,报告其中的行,然后删除它们......

#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

假设你有这个表: - 一个唯一的id自动增量 - 或者包含创建行的时间戳的created_timestamp字段

-> 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

每次使用实际值发送电子邮件时填写报告的行,在发送电子邮件之前,请检查以前的值,以便了解添加了哪些行

#5

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.

如果表具有标识字段,您还可以保存最大值(作为书签),并在下次检查是否存在ID大于保存书签的行。如果密钥是群集密钥,则可能更快。

#1