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:

在 Insert 触发器中，将记录的 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屋！