触发器与非规范化存储过程的优缺点
当涉及到对事务数据库中的数据进行非规范化以提高性能时，（至少）有三种不同的方法：
通过更新规范化事务数据和非规范化报告/分析数据的存储过程推送更新；
在更新辅助表的事务表上实现触发器；这几乎总是维护历史记录时所采取的路线；
将处理推迟到夜间批处理，可能会在数据集市/仓库中执行 ETL。
出于此问题的目的，我们假设选项 #3 不可行，因为域要求非规范化数据始终与规范化数据一致。我经常处理的分层聚合就是这样的一个例子。
我已经使用了相当多的前两种方法，最近我一直倾向于基于触发器的方法，但我想知道是否有任何我还没有发现的“陷阱”，并且认为它值得问这个问题，这样我在将来做出长期决策时就会记住一些想法。
那么根据您的经验，对于维护实时非规范化数据的特定目的，这两种工具的优缺点是什么？在什么情况下您会选择其中一种而不是另一种，为什么？
（PS 请不要回答“触发器太复杂”或“所有更新应始终通过存储过程”之类的答案 - 使其适合问题的上下文。）
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;
Defer the processing to a nightly batch process, possibly doing an ETL into a data mart/warehouse.
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?
(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.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
触发器是自动的副作用，当您想做某事但由于触发器的副作用而无法执行时，几乎肯定会咬住您。主要是让您的系统参与某些 XA 之类的事情与其他外部系统的交易。触发器使这一切变得不可能。此外，它是副作用逻辑，只能通过再次执行触发器激活器来激活。如果您想在仓库中重新创建数据，您不能只运行一些过程并重新创建它，您必须执行所有将触发触发器的活动，这是一场噩梦。插入、更新和删除应该是幂等和正交的。触发器不必要地使工作流程复杂化，即使您认为它们正在简化工作流程，但事实并非如此。
Triggers are automatic Side Effects and will almost certainly bite you down the line when you want to do something and can't because of the side effects of the triggers. Mainly things like having your system participate in some XA Transaction with other external systems. Triggers make this IMPOSSIBLE. Also it is Side Effect logic that can ONLY be activated by doing the Trigger activator again. If you want to recreate data in the Warehouse you can't just run some procedure and recreate it, you have to execute all the activities that will fire the Triggers, this is a nightmare. INSERTS, UPDATES and DELETES should be idempotent and orthogonal. Triggers needlessly complicate workflows, even if you think they are simplifying them they aren't.
当表上有多个更新路径时，触发器非常有用。
我们使用存储过程，并且至少有大约 4 个路径（添加、更新、停用、复制），
无论我们执行什么操作或影响多少行，都可以更轻松地处理我们刚刚在触发器中插入/更新的数据。
存储过程仅适用于单个更新路径我觉得：除非您想重复代码...
现在，触发器中的 TRY/CATCH 意味着正确的、可预测的错误处理：SQL Server 2000 及更早版本上的触发器导致错误/回滚时批量中止这并不理想（至少可以说！）。所以，无论如何，触发器现在更加可靠。
Triggers are useful where you multiple update paths on a table.
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.
A stored proc works for a single update path only I feel: unless you want to repeat code...
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.
这取决于您的业务需求以及数据库的使用方式。例如，假设有许多应用程序和许多导入会影响表（我们有数百个因素会影响我们的表）。假设偶尔需要编写从 SSMS 运行的查询（是的，即使是在产品上）来执行诸如将所有价格更新 10% 之类的操作。如果您执行这些类型的操作，那么存储过程是不切实际的，您将永远不会有所有可能的方法来影响所覆盖的数据库。
如果此数据更改对于数据完整性是必要的，或者许多应用程序或进程（导入、SQL Server 作业等）可能影响数据，则它属于触发器。
如果仅有时需要更改数据，或者您可以完全控制如何从一个应用程序更改数据，那么存储过程就可以了。
It depends on your business requirements and how your database is used. For instance, suppose there are many applications and many imports that affect the table (we have hundreds of things that can affect our tables) . Suppose also there is occasionally the need to write queries that are run from SSMS (yes even on prod) to do things like update all prices by 10%. If you do these types of things then a stored proc is impractical, you will never have every possible way to affect the database covered.
If this data change is necessary to data integrity or many applications or processes (imports, SQL Server Jobs, etc.) can affect data, then it belongs in the trigger.
If the data change is needed only sometimes or you have total control of how data is changed from only one application, then a stored proc is fine.