如何在SQL数据库引擎中实现数据库触发器？

How are triggers implemented inside a SQL database engine? I am not referring to the SQL language-level trigger definitions but rather their underlying implementations inside Oracle, SQL Server, MySQL, etc. How can the database engine scalably manage hundreds or thousands of triggers? Do they use a publish-subscribe model like with an observer/listener pattern? Any pointers to relevant literature on the subject would also be appreciated.

如何在SQL数据库引擎中实现触发器？我不是指SQL语言级别的触发器定义，而是指Oracle，SQL Server，MySQL等内部的底层实现。数据库引擎如何可以管理数百或数千个触发器？他们是否使用发布 - 订阅模型，如观察者/侦听器模式？任何关于该主题的相关文献的指针也将受到赞赏。

I did google for "database trigger implementation" but all I found was information on SQL trigger definitions, which again is not want I'm looking for.

我做谷歌的“数据库触发器实现”，但我发现的只是SQL触发器定义的信息，这也不是我想要的。

3 个解决方案

#1

Triggers are callbacks, so the implementation can be as simple as function pointers in C. Normally, a user is not expected writing user-defined procedural code in the RDBMS in C, though. You would need to support some other "higher-level" language. So the relevant programming pattern is DSL. The number of triggers (scalability) itself is not a problem because there is usually only one, max two per table and DML event triggers only these. The implementation challenge is elsewhere: in the areas of consistency, concurrency semantics.

触发器是回调，因此实现可以像C中的函数指针一样简单。但是，通常不希望用户在C语言的RDBMS中编写用户定义的过程代码。您需要支持其他一些“更高级别”的语言。所以相关的编程模式是DSL。触发器的数量（可伸缩性）本身不是问题，因为每个表通常只有一个，最多两个，而DML事件只触发这些。实现挑战在其他地方：在一致性，并发语义方面。

#2

You can explore source codes of open source databases. For example PostreSql's trigger.

您可以浏览开源数据库的源代码。例如PostreSql的触发器。

#3

First off, triggers are pieces of code that are run when a particular event (e.g. INSERT/UPDATE/DELETE on a particular table) occurs in the database. Triggers are executed implicitly BEFORE or AFER the DML statement and triggers cannot be executed explicitly like stored procedures.

首先，触发器是在数据库中发生特定事件（例如，特定表上的INSERT / UPDATE / DELETE）时运行的代码片段。触发器在DML语句之前或之后隐式执行，并且触发器不能像存储过程那样显式执行。

There are also two types of triggers - STATEMENT LEVEL triggers and ROW LEVEL triggers.

还有两种类型的触发器 - STATEMENT LEVEL触发器和ROW LEVEL触发器。

The STATEMENT LEVEL triggers are fired BEFORE or AFTER a statement is executed.

STATEMENT LEVEL触发器在执行语句之前或之后触发。

The ROW LEVEL triggers are fired BEFORE or AFTER an operation is performed on each individual row affected by the operation.

在对受操作影响的每一行执行操作之前或之后，将触发ROW LEVEL触发器。

So we have 12 types of triggers:

所以我们有12种类型的触发器：

 1. BEFORE INSERT STATEMENT
 2. BEFORE INSERT ROW
 3. AFTER INSERT STATEMENT
 4. AFTER INSERT ROW
 5. BEFORE UPDATE STATEMENT
 6. BEFORE UPDATE ROW
 7. AFTER UPDATE STATEMENT
 8. AFTER UPDATE ROW
 9. BEFORE DELETE STATEMENT
 10. BEFORE DELETE ROW
 11. AFTER DELETE STATEMENT
 12. AFTER DELETE ROW

Multiple triggers can be coded for an event with their order of precedence of execution mentioned.

可以为事件编码多个触发器，并提供其执行优先顺序。

Whenever we run a DML query (INSERT/UPDATE/DELETE) on a database, that query is run in a transaction. Hence when a query runs -

每当我们在数据库上运行DML查询（INSERT / UPDATE / DELETE）时，该查询就会在事务中运行。因此，当查询运行时 -

The table is locked
桌子已锁定
The DBMS checks for triggers that run BEFORE the statement is to be executed
DBMS检查在执行语句之前运行的触发器
Execute the actual SQL statement row-by-row.
逐行执行实际的SQL语句。
The BEFORE trigger for EACH ROW is looked for. If found, executed.
寻找EACH ROW的BEFORE触发器。如果找到，执行。
Check for errors. If any, rollback the changes made by the statement or its triggers.
检查错误。如果有，则回滚语句或其触发器所做的更改。
Any AFTER EACH ROW triggers are found and executed.
找到并执行任何AFTER EACH ROW触发器。
Any AFTER STATEMENT triggers are found and executed.
找到并执行任何AFTER STATEMENT触发器。

Different DBMS manage transactions differently. Refer to their documentation for details.

不同的DBMS管理事务的方式不同有关详细信息，请参阅其文档。

Many DBMS keep the triggers in text format only, not like stored procedures that are compiled.

许多DBMS仅以文本格式保留触发器，而不像编译的存储过程。

It is best practice to call stored procedures from inside a trigger body as stored procedures are much faster performers than triggers.

最佳做法是从触发器体内部调用存储过程，因为存储过程的执行器比触发器快得多。

#1

#2