我应该使用数据库触发器来解决以下问题吗？
我收到了以下任务：
外部源将以随机间隔将名称、日期时间和值写入数据库表（表 A）。我需要将此数据复制到另一个表（表 B）并删除表 A 中的行。如果查找表中不存在该名称，则该行需要保留在表 A 中，直到它出现为止，然后复制到表中B.
上述听起来像是可以有效使用数据库触发器的情况，还是创建一个外部应用程序（可能是 Windows 服务）每 5 分钟检查一次数据库并执行必要的更新会更好？
更新：
这不一定需要是即时的，因此如果我使用 Windows 服务或任务调度程序，它可能会设置为 5 分钟间隔。如果使用触发器，它几乎是即时的，这只是使用触发器的好处，而不是决定因素。
我主要关心的是这是使用触发器的正确方法吗？应该使用触发器来复制数据还是这是一种不好的做法？使用触发器也可能存在问题，例如，如果复制失败，它是否会锁定表以阻止复制后续行？如果一份副本花费的时间太长，它是否不会处理在总线上插入的行？如果我不使用触发器，是否有更好的解决方案，例如使用 Windows 计划的 Windows 服务或控制台应用程序？
谢谢
I've been given the following task:
An external source will write a name, datetime and value to a database table (table A) at random intervals. I need to copy this data to another table (table B) and delete the row in table A. If the name doesn't exist in a lookup table then the row needs to remain in table A until it does and then copied over to table B.
Does the above sound like a situation where a database trigger could be used effectively or would it be better creating an external app (maybe windows service) that checks the db every 5 min and performs the necessary updates?
Update:
This does not necessarily need to be instant thus if I used a windows service or task scheduler it would probably be set at 5 min intervals. It being virtually instant if a trigger is used is just a bonus of using a trigger, not a deciding factor.
My main concern is that is this the correct way of using a trigger? Should triggers be used to copy data or is this bad practise? Also could have problems with using a trigger, for example if a copy fails could it lock the table stopping subsequent rows being copied? If one copy takes too long will it not process the rows that have been inserted while it was bus? If I don't use a trigger is there a better solution e.g. a windows service or console app using windows schedular?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
您可以使用 SQL 作业来完成此操作。编写存储过程来复制数据并放入计划作业中。该作业每 5 分钟运行一次。
step_by_step_guide_to_add_a_sql_job_in_sql_server_2005
sql_server_agent_jobs
You can do this using SQL Job. Write Stored Procedure to copy data and put in schedule job. This job will run after every 5 minutes.
step_by_step_guide_to_add_a_sql_job_in_sql_server_2005
sql_server_agent_jobs
这听起来是一个非常简单的操作。如果是，并且从表 A 中删除该行并将其插入到表 B 中不需要太多时间，我会使用触发器。与定期同步相比，这样做的优点是数据始终是最新的。
我假设您无法控制数据的插入方式，或者我建议让外部源通过存储过程写入您的数据库。这也可以提高安全性。
This sounds like a very simple operation. If it is, and it doesn't take much time to delete the row from table A and insert it into table B, I would go for the trigger. This has the advantage that data is always up-to-date, as opposed to synchronising regularly.
I'm assuming you have no control over how the data is inserted, or I'd suggest having the external source write to your database through a stored procedure. This can also improve security.
如果这是同类任务中唯一的一个，并且您不期望还会有更多任务出现，那么触发器就足够了。这听起来像是一个简单的数据操作。最好将代码放在某个存储过程中。服务就有点矫枉过正了。
此类任务的问题在于它们往往会增长。我已经看到简单的数据操作逻辑成长为巨大的业务逻辑野兽，吞噬了程序员的理智。因此，如果您从一个无辜的触发器/存储过程开始，并且只是感觉到会有更多的触发器/存储过程出现，那么就运行它并从中创建一个服务！
If this is the only task of its kind and you don't expect more to come, then a trigger is sufficient. It sounds like a simple data manipulation. Better put the code in some stored procedure. A service would be the overkill.
The problem with such tasks is that they tend to grow. I have already seen simple data manipulation logic to grow up to enormous business logic beasts which devours the programmer’s sanity. So if you start with an innocent trigger/stored procedure and just sense a little probability that more will come, then run and make a service out of it!
编写一个简单的触发器，然后在事务中执行所需的检查和更新。
Write a simple trigger, and in a transaction do the check and the updates you need.
触发器就是解决方案。而且它也恰好是交易的一部分。所以，整个事情要么发生要么失败。
Trigger is the solution. And it also happens to be a part of transaction. So, whole thing happens or fails.
只要逻辑简单，我就会说继续使用触发器。当触发器实现复杂的逻辑时，问题就开始出现，但如果只是“验证查找表中是否存在数据，以及是否确实将数据从 A 复制到 B 并从 A 中删除”。
您还可以考虑在查找表上使用第二个触发器，以便当将新名称添加到查找表时，它可以执行所需的任何处理，将需要新名称的行从 A 移动到表 B 等。
共享并享受。
As long as the logic is simple I'd say go ahead and use a trigger. Problems begin to arise when triggers implement complex logic, but if it's just "Verify that data exists in the lookup table, and if it does copy the data from A to B and delete from A".
You might also consider a second trigger on the lookup table so that when a new name is added to the lookup table it could do whatever processing is needed to move rows from A that needed the new name to table B, etc.
Share and enjoy.
触发器作为一种简单的修复方法吸引着您，但不久之后，您花在调试触发器上的时间就会比阅读 StackOverflow 的时间还要多。触发器是邪恶的。远离他们。
Triggers lure you in as a simple fix, but before long, you spend more time debugging triggers than reading StackOverflow. Triggers are evil. Stay away from them.
我同意穆罕默德的观点。这应该使用数据库作业来完成。
在这种情况下使用触发器对我来说似乎违反直觉。您使用触发器不是为了修改要在表 A 中插入的数据，而是（可能）在表 B 中执行某些操作并防止将行插入到表 A 中。
此外，在将行插入到表中的情况下A 的键不存在，该行保留在表 A 中。这意味着您的触发器必须是表级别，而不是行级别。
触发器的主要用例是修改插入时的数据，添加修改日期等信息。
您应该编写一个存储过程并每五分钟运行一次。这样就比较简单了，当逻辑发生变化的时候会容易理解很多。
I agree with Muhammad. This should be done using a database job.
The use of a trigger in this situation seems counter intuitive to me. You are using a trigger not to modify data you are inserting in table A, but to (potentially) do something in table B and prevent the row from being inserted in table A.
Also, in the situation where you have a row inserted into table A for which the key does not exist, this row is left in table A. This means your trigger will have to be table level, not row level.
The main use case for triggers is to modify data on insertion, adding information such as modifcation dates.
You should write a stored procedure and run it every five minutes. This is simpler, and when the logic changes will be much easier to understand.