为插入/更新/删除设置单个触发器还是多个触发器更好? [英] Is it better to have single trigger for Insert/Update/Delete, or multiple?

问题描述

我需要将插入/更新/删除操作从一个表镜像到另一个表. 例如，必须将在tableA上的插入复制到tableB中，将其更新到应用于tableB的tableA，并从tableA删除，然后将其应用于tableB. 就这么简单，除了tableB为常数值增加了1列之外，因此需要非常简单的触发器.

I have a requirement to mirror insert/update/delete operations from one table to another. For example insert on tableA has to be copied into tableB, update to tableA applied to tableB, and delete from tableA be applied to tableB. It's as simple as that, except tableB has 1 additional column for a constant value, so very simple triggers are needed.

我不确定是否最好编写3个单独的触发器，或者让一个触发器执行所有操作.

I'm not sure if it is better to write 3 separate triggers, or have one trigger that does all of the operations.

这适用于3个数据库:Sybase ASE，MSSQL和Oracle，我想使它的解决方案保持相似(因此，对于所有数据库，应为3，对于所有数据库，应为1).

This is for 3 databases: Sybase ASE, MSSQL and Oracle, and I'd like to keep it the solution similar (so either 3 for all databases or 1 for all of them).

有3个触发器与1个触发器只是一个偏爱的问题，还是这两种解决方案都有实际的好处?

Is it just a matter of preference, to have 3 triggers vs. 1, or are there actual benefits to either solution?

推荐答案

假定您确实需要触发器，并且不能将表B简单地定义为表A之上的视图，或者不能仅使用表a定义表B.外键引用A中的行以及常量，不能重新定义A以添加附加列(可能具有常量的默认值)，一个触发器至少可以让您将所有相关逻辑保留在一个位置，而不是当您执行类似的操作时，需要在多个地方进行更新，例如，向A添加新列.但是，对于任何包含两个不同的表，它们在两个表中都反映相同数据的体系结构，我将格外警惕.这违反了规范化，增加了系统的I/O工作量，并使整个系统更加复杂.

Assuming that you actually need a trigger and that table B cannot simply be defined as a view on top of table A or that table B cannot just be defined with a foreign key that references a row in A along with the constant, that A cannot be redefined to add the additional column (potentially with a default value of the constant), one trigger at least lets you keep all the related logic in one place rather than having multiple places that need to be updated when you do something like add a new column to A. But I would be extremely wary of any architecture that involved having two different tables reflecting essentially the same data in both. That violates normalization, it adds to the system's I/O workload, and it makes the whole system more complex.

这篇关于为插入/更新/删除设置单个触发器还是多个触发器更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！