SQL 数据库触发器是邪恶的吗？

Are database triggers evil?

提问by WW.

Are database triggers a bad idea?

数据库触发器是一个坏主意吗？

In my experience they are evil, because they can result in surprising side effects, and are difficult to debug (especially when one trigger fires another). Often developers do not even think of looking if there is a trigger.

根据我的经验，它们是邪恶的，因为它们会导致令人惊讶的副作用，并且难以调试（尤其是当一个触发器触发另一个时）。通常，开发人员甚至不会考虑查看是否有触发器。

On the other hand, it seems like if you have logic that must occur evertime a new FOOis created in the database then the most foolproof place to put it is an insert trigger on the FOO table.

另一方面，似乎如果您的逻辑必须在每次FOO在数据库中创建新的时发生，那么放置它的最简单的地方是 FOO 表上的插入触发器。

The only time we're using triggers is for really simple things like setting the ModifiedDate.

我们使用触发器的唯一时间是非常简单的事情，比如设置ModifiedDate.

采纳答案by dkretz

The main problems with triggers are

触发器的主要问题是

• They are completely Global - they apply no matter what the context of the table activity;
• They are stealthy; it's easy to forget they are there until they hurt you with unintended (and very mysterious) consequences.

• 它们是完全全局的——无论表活动的上下文如何，它们都适用；
• 他们是隐蔽的；很容易忘记它们的存在，直到它们以意外（且非常神秘）的后果伤害了您。

This just means they need to be carefully used for the proper circumstances; which in my experience is limited to relational integrity issues (sometimes with finer granularity than you can get declaratively); and usually not for business or transactional purposes. YMMV.

这只是意味着它们需要在适当的情况下谨慎使用；根据我的经验，这仅限于关系完整性问题（有时比您可以声明的粒度更细）；通常不用于商业或交易目的。天啊。

回答by paxdiablo

No, they're actually a good idea. If there's a problem with your specific triggers, then you're not doing them right, but that usually means there's a problem with your implementation, notthe concept of triggers themselves :-).

不，它们实际上是个好主意。如果您的特定触发器有问题，那么您就没有做对，但这通常意味着您的实现存在问题，而不是触发器本身的概念:-)。

We use triggers a great deal because it places the DBMS-specific activity under the control of the database where it belongs. Users of a DBMS should not have to worry about that sort of stuff. The integrity of data lies with the database itself, notthe applications or users that use it. Without constraints and triggers and other features in the database, it's left to the applications to enforce the rules and it only takes one rogue or buggy application/user to destroy the data.

我们大量使用触发器，因为它将特定于 DBMS 的活动置于其所属数据库的控制之下。DBMS 的用户不必担心这类事情。数据的完整性在于数据库本身，而不在于使用它的应用程序或用户。如果数据库中没有约束和触发器以及其他功能，则由应用程序来强制执行规则，并且只需要一个流氓或有缺陷的应用程序/用户来破坏数据。

For example, without triggers, such wondrous things as auto-generated columns wouldn't exist and you'd have to process a function on each row when selecting them. That's likely to kill DBMS performance, far better to create the auto-generated column at insert/update time since that's the only time it changes.

例如，如果没有触发器，自动生成列之类的奇妙事物将不存在，您必须在选择它们时对每一行处理一个函数。这可能会降低 DBMS 的性能，在插入/更新时创建自动生成的列要好得多，因为这是它唯一更改的时间。

Also, lack of triggers would prevent data rules from being enforced at the DBMS such as pre-triggers to ensure columns have a specific format. Note that this is different from data integrity rules which are generally just foreign key look ups.

此外，缺少触发器会阻止在 DBMS 执行数据规则，例如预触发器，以确保列具有特定格式。请注意，这与通常只是外键查找的数据完整性规则不同。

回答by Andy Webb

Tools are never evil. Applications of those tools can be evil.

工具从来都不是邪恶的。这些工具的应用程序可能是邪恶的。

回答by MatBailie

I agree. The problems with triggers is people, not triggers. Although it's more to look at, more to consider and increases the onus on coders checking things correctly, we don't discard indexes to make our lives simpler. (Bad indexes can be just as bad as bad triggers)

我同意。触发器的问题在于人，而不是触发器。尽管需要更多的观察、更多的考虑并增加编码人员正确检查事物的责任，但我们不会丢弃索引以使我们的生活更简单。（坏索引可能和坏触发器一样坏）

The importance of triggers (in my mind) is that...
- Any system should always be in a valid state
- Code to enforce this valid state should be centralised (not written in every SP)

触发器的重要性（在我看来）是...
- 任何系统都应该始终处于有效状态
- 强制执行此有效状态的代码应该是集中的（不是每个 SP 中都编写）

From a maintenance point of view, a trigger is very useful to competant coders and problems for more junior/amateur ones. Yet, these people need to learn and grow somehow.

从维护的角度来看，触发器对于有能力的编码人员和更多初级/业余爱好者的问题非常有用。然而，这些人需要以某种方式学习和成长。

I guess it comes down to your working environment. Do you have reliable people who learn well and can be trusted to be methodical? If not you seemingly have two choices:
- Accept that you'll have to lose functionality to compensate
- Accept that you need different people or better training and management

我想这归结于你的工作环境。你有可靠的人，他们学习很好，可以相信他们有条不紊吗？如果不是，您似乎有两个选择：
- 接受您将不得不失去功能以进行补偿
- 接受您需要不同的人或更好的培训和管理

They sound harsh, and I guess that they are. But it's the basic truth, in my mind...

它们听起来很刺耳，我想它们确实如此。但这是基本的事实，在我看来......

回答by HLGEM

I think triggers are not only not evil, but necessary to good database design. Application programmers think that databases are only affected by their application. They are often wrong. If data integrity is to be maintained no matter where the data change came from, triggers are a requirement and it is foolish to avoid them because some programmers are too ethnocentric to consider that something other than their prized application may be affecting things. It isn't hard to design or test or troubleshoot a trigger if you are a competent database developer. Nor it is difficult to determine that a trigger is causing an unexpected result if it occurs to you (as it does to me) to look there. If I get an error saying a table that I'm not referencing in my sp has an FK error, I know without even thinking about it that trigger is causing the problem and so should any competent database developer. Putting business rules only in the application is the number one cause I have found of bad data as others have no idea that rule even exists and violate it in their processes. Data-centric rules belong in the database and triggers are key to enforcing the more complex ones.

我认为触发器不仅不是邪恶的，而且是良好数据库设计所必需的。应用程序程序员认为数据库只受其应用程序的影响。他们经常是错的。如果无论数据更改来自何处，都必须保持数据完整性，那么触发器是必需的，避免它们是愚蠢的，因为一些程序员过于以种族为中心，认为除了他们珍贵的应用程序之外的其他东西可能会影响事物。如果您是一名称职的数据库开发人员，那么设计、测试或排除触发器故障并不难。如果您（就像我一样）在那里查看，也很难确定触发器是否会导致意外结果。如果我收到一个错误，说我没有在我的 sp 中引用的表有一个 FK 错误，我什至没有考虑就知道是触发器导致了问题，任何有能力的数据库开发人员也应该如此。仅将业务规则放在应用程序中是我发现不良数据的第一大原因，因为其他人甚至不知道该规则存在并在他们的流程中违反它。以数据为中心的规则属于数据库，触发器是执行更复杂规则的关键。

回答by MarkR

Mostly, yes.

大多数情况下，是的。

The difficulty with a trigger is that it does stuff "behind your back"; the developer maintaining the application could easily not realise it's there and make changes which screw things up without even noticing.

触发器的困难在于它会“在你背后”做一些事情；维护应用程序的开发人员很容易没有意识到它的存在并进行更改，甚至在没有注意到的情况下把事情搞砸了。

It creates a layer of complexity which just adds maintenance work.

它创建了一层复杂性，只会增加维护工作。

Rather than using a trigger, a stored procedure / routine, can generally be made to do the same thing, but in a clear and maintainable manner - calling a stored routine means the developer can look at its source code and see exactly what's happening.

与使用触发器不同，存储过程/例程通常可以用来做同样的事情，但是以一种清晰且可维护的方式——调用存储例程意味着开发人员可以查看其源代码并确切了解发生了什么。

回答by Robin Day

Triggers are extremely powerful and useful, there are any number of scenarios where a trigger is the best solution to a problem.

触发器非常强大和有用，在许多场景中触发器是解决问题的最佳方法。

They are also a very good "hack" tool. There are often situations where you are not in immediate control of both the code and the database. If you have to wait 2 months for the next major release of your code, yet you can apply a patch to your database immediately then you can put a trigger on a table to perform some additional functionality. Then when the code release is possible you can replace this trigger with your coded version of the same functionality if desired.

它们也是一个非常好的“黑客”工具。通常情况下，您无法立即控制代码和数据库。如果您必须等待 2 个月才能发布代码的下一个主要版本，但您可以立即对数据库应用补丁，那么您可以在表上放置触发器以执行一些附加功能。然后，当可以发布代码时，如果需要，您可以使用相同功能的编码版本替换此触发器。

At the end of the day, everything is "evil" if you don't know what it's doing. Deciding that triggers are because there are developers that don't understand them is the same as arguing that cars are evil because some people can't drive...

归根结底，如果您不知道它在做什么，那么一切都是“邪恶的”。决定触发器是因为有些开发人员不了解它们，这与认为汽车是邪恶的因为有些人不会开车是一样的......

回答by Dave Sherohman

Triggers have their uses - logging/auditing and maintaining a "last modified" date are two very good uses which have been mentioned in previous replies.

触发器有其用途 - 记录/审计和维护“上次修改”日期是先前答复中提到的两个非常好的用途。

However, one of the core tenets of good design is that business rules/business logic/whatever you want to call it should be concentrated in a single place. Putting some of the logic in the database (via triggers or stored procs) and some in the application violates that principle. Duplicating the logic in both places is even worse, as they will invariably get out of sync with each other.

然而，良好设计的核心原则之一是业务规则/业务逻辑/任何你想调用的东西都应该集中在一个地方。将一些逻辑放在数据库中（通过触发器或存储过程）和应用程序中的一些违反了该原则。在两个地方复制逻辑甚至更糟，因为它们总是会彼此不同步。

There is also the "principle of least surprise" issue which has already been mentioned.

还有已经提到的“最小意外原则”问题。

回答by tmeisenh

Triggers are a good tool when used properly. Expecially for things like auditing changes, populating summarization tables, etc.

如果使用得当，触发器是一个很好的工具。尤其是审计变更、填充汇总表等。

Now they can be "evil" if you end up in "trigger hell" with one trigger that kicks off other triggers. I once worked on a COTS product where they had what they called "flex triggers." These triggers were stored in a table as dynamic sql stings are were compiled everytime they were executed. Compiled triggers would do a look up and see if that table had any flex triggers to run and then compile and run the "flex" trigger. In theory this sounded like a really cool idea because the product was easily customized but the reality was the database pretty much exploded due to all the compiles it had to do...

现在，如果您以一个触发其他触发器的触发器结束“触发器地狱”，则它们可能是“邪恶的”。我曾经在 COTS 产品上工作过，在那里他们有所谓的“弹性触发器”。这些触发器存储在一个表中，因为每次执行时都会编译动态 sql 字符串。编译的触发器会查找并查看该表是否有任何要运行的 flex 触发器，然后编译并运行“flex”触发器。从理论上讲，这听起来是一个非常酷的主意，因为该产品很容易定制，但实际情况是，由于必须进行所有编译，数据库几乎爆炸了……

So yeah, they're great if you keep what you're doing in perspective. If it is something pretty simple like auditing, summarizing, auto-sequencing, etc, no prob. Just keep in mind the growth rate of the table and how the trigger will impact performance.

所以是的，如果你正确看待你正在做的事情，他们会很棒。如果它是非常简单的事情，例如审计、汇总、自动排序等，则没有问题。请记住表的增长率以及触发器将如何影响性能。

回答by Toon Koppelaars

At a high level there are two use-cases for triggers1

在高层次上，触发器有两个用例1

1) To make stuff "automagically" happen. In this case triggers cause a side-effect, they change data in ways that were not expected given the (primitive) operator insert, update or delete that was executed and caused the trigger to fire.

1）让事情“自动”发生。在这种情况下，触发器会产生副作用，它们会以出乎意料的方式更改数据，因为执行并导致触发器触发的（原始）操作符插入、更新或删除。

The general consensus here is that triggers are indeed harmful. Because they change the well known semantics of an INSERT, UPDATE or DELETE statement. Changing the semantics of these three primitive SQL operators will bite other developers who later in the future need to work on your database tables that do not behave in expected ways anymore when operated upon them with the SQL primitives.

这里的普遍共识是触发器确实是有害的。因为它们更改了 INSERT、UPDATE 或 DELETE 语句的众所周知的语义。改变这三个原始 SQL 操作符的语义会伤害其他开发人员，他们以后需要处理您的数据库表，当使用 SQL 原语操作它们时，这些表不再以预期的方式运行。

2) To enforce data integrity rules, other than the ones we can deal with declaratively (using CHECK, PRIMARY KEY, UNIQUE KEY and FOREIGN KEY). In this use-case all the triggers do is QUERY (SELECT) data to verify if the change that is being made by the INSERT/UPDATE/DELETE is allowed or not. Just like declarative constraints do for us. Only in this case we (the developers) have programmed the enforcement.

2) 强制执行数据完整性规则，除了我们可以声明性处理的规则（使用 CHECK、PRIMARY KEY、UNIQUE KEY 和 FOREIGN KEY）。在此用例中，所有触发器所做的都是查询 (SELECT) 数据，以验证是否允许 INSERT/UPDATE/DELETE 所做的更改。就像声明式约束对我们所做的一样。只有在这种情况下，我们（开发人员）才对执行进行了编程。

Using triggers for the latter use-case is not harmful.

为后一个用例使用触发器是无害的。

I'm blogging on that at: http://harmfultriggers.blogspot.com

我正在写博客：http: //harmfultriggers.blogspot.com