C＃中有什么可用作数据库触发器 [英] Is there anything in C# that can be used as database Trigger

问题描述

我的ERP数据库 A仅具有读取权限，因此无法在表上创建触发器。
A是为ERP系统制作的（对我来说Unknown Program）。我有另一个对我的应用程序专用的数据库 B，该应用程序可在两个数据库上工作。我想将A的更改（对于任何插入/更新/删除）立即反映给B。
c＃中是否有任何功能可以完全像触发器在数据库中一样工作？

I have ERP database "A" has only read permission, where i cant create trigger on the table. A is made for ERP system (Unknown Program for me ). I have another Database "B" that is private to my application this application work on both databases. i want to reflect A's changes(for any insert/Update/Delete) instantly to B. Is there any Functionality in c# that can work exactly as trigger works in database???

推荐答案

解决方案很少，最好的解决方案取决于您必须支持哪种数据库。

You have few solutions, best one depends on which kind of database you have to support.

如果您不能更改 master 数据库，并且该数据库必须适用于每种数据库，那么您只有一个选择：轮询。

If you can't change master database and this must work with every kind of database then you have only one option: polling.

您不应该经常检查（以免立即或多或少地进行检查）来​​保存网络流量，最好以不同的方式进行插入/更新/删除。您可以做什么取决于数据库的结构，例如：

You shouldn't check too often (so forget to do it more or less instantly) to save network traffic and it's better to do in in different ways for insert/update/delete. What you can do depends on how database is structured, for example:

插入：捕获插入您可能只是检查最高的行ID（假设您需要监视的列有一个整数列用作键）。

更新：对于更新，您可以检查时间戳列（如果

删除：检测起来可能比较棘手，第一个检查将是对行数进行计数，如果已更改并且没有插入，则您检测到一个

Insert: to catch an insert you may simply check for highest row ID (assuming what you need to monitor has an integer column used as key).
Update: for updates you may check a timestamp column (if it's present).
Delete: this may be more tricky to detect, a first check would be count number of rows, if it's changed and no insert occured then you detected a delete else just subtract the number of inserts.

可以更改原始数据库，您可以使用数据库端的触发器来减少网络流量（和复杂性），当触发器被触发时，只需将记录放在内部 日志表（几列：一列用于更改类型，一列用于受影响的表，一列用于受影响的记录。）

If you can change the original database you can decrease network traffic (and complexity) using triggers on database side, when a trigger is fired just put a record in an internal log table (just few columns: one for the change type, one for affected table, one for affected record).

ed仅在该表上轮询（使用简单查询来检查行数是否增加）。因为操作（插入/更新/删除）存储在表中，所以您只需要打开该列即可执行适当的操作。

You will need to poll only on this table (using a simple query to check if number of rows increased). Because action (insert/update/delete) is stored in the table you just need to switch on that column to execute proper action.

这有一个很大的缺点（在我观点）：它将与您的应用程序相关的逻辑放入master数据库中。这可能是可怕的，也可能不是可怕的，但这取决于许多因素。

This has a big disadvantage (in my point of view): it puts logic related to your application inside the master database. This may be terrible or not but it depends on many many factors.

如果您的应用程序已绑定到Microsoft SQL Server，则可以使用 SqlDependency 类可跟踪所做的更改。它仅适用于SS，但我认为可能会有其他数据库的实现。缺点是，这总是特定于特定供应商的（因此，如果数据库将更改主机...您也必须更改代码）。

If you're application is tied to Microsoft SQL Server you can use SqlDependency class to track changes made. It works for SS only but I think there may be implementations for other databases. Disadvantage is that this will always bee specific to a specific vendor (so if A database will change host...you'll have to change your code too).

来自MSDN：

SqlDependency旨在用于ASP.NET或中间层服务，在这些中间层服务器中，依赖项相对较少对数据库处于活动状态。它不是为在客户端应用程序中使用而设计的，在客户端应用程序中，成百上千的客户端计算机将为单个数据库服务器设置SqlDependency对象。

SqlDependency was designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server.

无论如何，如果您使用的是SQL Server，则还有其他选择，只需点击MSDN文档中的链接即可。

Anyway if you're using SQL Server you have other options, just follow links in MSDN documentation.

附录：如果需要更好的控制，可以检查 TraceServer 和 Object：Altered （和朋友）类。这甚至与Microsoft SQL Server息息相关，但是应该在更广泛的上下文中使用（并且您可能会使应用程序不注意这些事情）。

Addendum: if you need a more fine control you may check TraceServer and Object:Altered (and friends) classes. This is even more tied to Microsoft SQL Server but it should be usable on a more wide context (and you may keep your applications unaware of these things).

这篇关于C＃中有什么可用作数据库触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！