在SQL表中检索新的插入行 [英] Retrieving New Insert Row in SQL table

问题描述

亲爱的朋友



我有一个SQL DB，其中有一个名为event的表。此表在每秒内填充了大量事件。



我需要像文件监视器这样的东西，一旦插入新行（事件）就会看到这个表我想拉它并在我的应用程序中显示它。 br />


我不想用触发器来做，如果我在每秒运行查询。查询在39秒后返回值。所以这没有帮助。



我很感激，如果有人能帮助我的话。



谢谢提前!!!



祝你有个美好的一天。



更新

仍然对此感到震惊....请任何人都可以就此提出建议....

解决方案

你可以这样做：



创建一个存储过程：

 选择  top   1  e.EventID，e.EventName，.... 
 来自 dbo.events e 
 订单 按 e.EventID  desc  

并且每2秒调用一次该存储过程并检查前端是否获取此事件。如果没有，则将其添加到显示中，否则忽略它。



我仍​​然认为这不是一个好主意但是因为你无法控制存储的插入过程，这可能是你能做到的唯一方法。



如果你可以控制插入存储过程，那么只需在insert语句之后插入以下内容：

 声明  @ newEventID   bigint  
  set   @ newEventID  =  @@ IDENTITY  
 
 选择 e.EventID， e.EventName，.... 
 来自 dbo.events e 
 其中 e.EventID =  @ newEventID  

希望这会有所帮助。


问候，

Nayan

你想要的只是： SqlDependency类 [ ^ ]。以下是有趣的实现：使用SqlDependency进行数据更改事件 [ ^ ]

Dear Friends

I have a SQL DB, in which we have 1 table called as events. This table gets populated with lots of events within each sec.

I need something like file-watcher to watch this table as soon as new row(event) is inserted i want to pull it and display it in my application.

I don't want to do it with triggers and if i am running query in each sec. the query is returning the value after 39 sec. so this is not helping.

I appreciate if anyone can help me in this.

Thanks in advance !!!

Have a good day.

updated
Still Struck with this.... Please anyone can advice on this....

解决方案

Hi,

You can do something like this:

Create a stored procedure as:

select     top 1 e.EventID, e.EventName, ....
from       dbo.events e
order by   e.EventID desc

and call this stored procedure every 2 seconds and check in the front end whether this event is fetched. If not, then add it to display otherwise ignore it.

I still don't think this is a good idea but since you do not have control over the insert stored procedure, this could be the only way you can do it.

If you have control over the insert stored procedure, then just insert the following just after the insert statement:

declare @newEventID bigint
set @newEventID = @@IDENTITY

select     e.EventID, e.EventName, ....
from       dbo.events e
where      e.EventID = @newEventID

Hope this helps.

Regards,
Nayan

All what you want is: SqlDependency class[^]. Here is interesting implementation: Using SqlDependency for data change events[^]

这篇关于在SQL表中检索新的插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！