触发器与非正规化存储过程的优缺点 [英] Pros and Cons of Triggers vs. Stored Procedures for Denormalization
问题描述
当涉及对事务数据库中的数据进行非规范化以提高性能时，有（至少）三种不同的方法：
When it comes to denormalizing data in a transactional database for performance, there are (at least) three different approaches:
-
通过存储过程推送更新，该过程将同时更新标准化的交易数据和非标准化的报告/分析数据；
Push updates through stored procedures which update both the normalized transactional data and the denormalized reporting/analysis data;
交易表上的实现触发器可更新辅助表;这几乎总是维护历史记录时要采用的途径；
Implement triggers on the transactional tables that update the secondary tables; this is almost always the route taken when maintaining histories;
将处理推迟到每晚进行批处理，可能需要将ETL放入数据集市/仓库。 / p>
Defer the processing to a nightly batch process, possibly doing an ETL into a data mart/warehouse.
为了这个问题，我们假设选项＃3不可行，因为域需要非规范化数据始终与标准化数据保持一致。我经常处理的层次聚合就是这样的一个例子。
Let's assume for the purposes of this question that option #3 isn't viable, because the domain requires the denormalized data to be consistent with the normalized data at all times. Hierarchical aggregates, which I deal with rather frequently, are one example of this.
我已经使用了前两种方法，并且最近我一直在使用倾向于基于触发器的方法，但是我想知道是否还有我尚未发现的陷阱，并认为值得提出这个问题，因此在制作时我会记住一些想法
I've used both of the first two approaches a fair bit and lately I've been leaning toward the trigger-based approach, but I'm wondering if there are any "gotchas" that I haven't discovered yet, and thought it would be worth asking this question so I'll have some ideas to keep in mind when making long-term decisions in the future.
那么根据您的经验，这两种工具在维护实时非规范化数据方面的利弊是什么？在什么情况下，您会选择一个，为什么？
So in your experience, what are the pros and cons of either tool for the specific purpose of maintaining real-time denormalized data? In what situations would you choose one over the other, and why?
（PS，请不要回答触发器过于复杂或所有更新都应始终通过存储的过程-使它适合于问题的上下文。）
(P.S. Please no answers like "triggers are too complicated" or "all updates should always go through a stored proc" - make it appropriate to the context of the question.)
推荐答案
在多个更新路径上，触发器将非常有用
Triggers are useful where you multiple update paths on a table.
我们使用存储的proc，并且至少具有大约4条路径（添加，更新，停用，复制）
We use stored procs and have about 4 paths at least (Add, Update, Deactivate, Copy)
无论我们执行什么操作或影响多少行，都更容易处理我们刚刚在触发器中插入/更新的数据。
It's easier to work with the data we've just inserted/updated in a trigger no matter what action we do or how many rows we affect.
存储的proc我觉得仅适用于单个更新路径：除非您想重复执行代码...
A stored proc works for a single update path only I feel: unless you want to repeat code...
现在，触发器中的TRY / CATCH意味着正确的，可预测的错误处理： SQL Server 2000和更早版本导致错误/回滚导致批处理中止，这不理想（至少可以说！）。因此，无论如何触发器现在更加可靠。
Now, TRY/CATCH in triggers means correct, predictable error handling: triggers on SQL Server 2000 and earlier caused batch aborts on error/rollback which is not ideal (to say the least!). So, triggers are more reliable now anyway.
这篇关于触发器与非正规化存储过程的优缺点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
