SQL CLR 触发器可以做到这一点吗?或者，还有更好的方法?
我想编写一个监视数据库表的服务(可能是在 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.
相关文章