SQL CLR触发器可以做到这一点吗？或者，还有更好的方法？ [英] Can SQL CLR triggers do this? Or is there a better way?

问题描述

我想编写一个监视数据库表的服务（可能在c＃中）。将记录插入表中后，我希望服务获取新插入的数据，并使用它执行一些复杂的业务逻辑（对于TSQL来说太复杂了。）

I want to write a service (probably in c#) that monitors a database table. When a record is inserted into the table I want the service to grab the newly inserted data, and perform some complex business logic with it (too complex for TSQL).

一个选项是让服务定期检查表以查看是否已插入新记录。这样做的问题是我希望服务在插入时立即知道它们，而又不想破坏数据库的性能。

One option is to have the service periodically check the table to see if new records have been inserted. The problem with doing it that way is that I want the service to know about the inserts as soon as they happen, and I don't want to kill the database performance.

进行一些研究，似乎编写CLR触发器可以完成这项工作。我可以用c＃编写触发器，当发生插入操作时触发该触发器，然后将新插入的数据发送到Windows或WCF服务。

Doing a little research, it seems like maybe writing a CLR trigger could do the job. I could write trigger in c# that fires when an insert occurs, and then send the newly inserted data to a Windows or WCF service.

您的想法是很好（甚至可能）使用SQL CLR触发器？

What do you think, is that a good (or even possible) use of SQL CLR triggers?

关于如何完成此操作的任何其他想法？

Any other ideas on how to accomplish this?

推荐答案

可能应该取消后处理与插入的耦合：

Probably you should de-couple postprocessing from inserting:

在插入触发器中，将记录的PK添加到队列表中。

In the Insert trigger, add the record's PK into a queue table.

在单独的服务中，从队列表中读取并进行复杂的操作。完成后，将记录标记为已处理（连同错误/状态信息），或从队列中删除记录。

In a separate service, read from the queue table and do your complex operation. When finished, mark the record as processed (together with error/status info), or delete the record from the queue.

这篇关于SQL CLR触发器可以做到这一点吗？或者，还有更好的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！