SQL 行级触发器与语句级触发器

row-level trigger vs statement-level trigger

提问by Gregorio Di Stefano

I am having a hard time understanding the difference between 'row-level triggers' and 'statement-level triggers'.

我很难理解“行级触发器”和“语句级触发器”之间的区别。

From my understanding, in the scenario a statement level trigger is create, the whole table can be modified. A row level trigger would only allow me to modify the tuple being affect by the trigger's specificed event.

根据我的理解，在创建语句级触发器的场景中，可以修改整个表。行级触发器只允许我修改受触发器特定事件影响的元组。

Is this correct? Does anyone have an example of the two?

这样对吗？有人有这两者的例子吗？

Thanks!

谢谢！

回答by ypercube??

The main difference is not what can be modified by the trigger, that depends on the DBMS. A trigger (row or statement level) may modify one or many rows^*, of the same or other tables as well and may have cascading effects (trigger other actions/triggers) but all these depend on the DBMS of course.

主要区别不是触发器可以修改什么，这取决于 DBMS。触发器（行或语句级别）也可以修改同一个或其他表的一个或多个行^*，并且可能具有级联效果（触发其他操作/触发器），但所有这些当然都取决于 DBMS。

The main difference is how many times the trigger is activated. Imagine you have a 1M rows table and you run:

主要区别在于触发器被激活的次数。想象一下你有一个 100 万行的表，你运行：

UPDATE t
SET columnX = columnX + 1

A statement-level trigger will be activated once(and even if no rows are updated). A row-level trigger will be activated a million times, once for every updated row.

语句级触发器将被激活一次（即使没有行被更新）。行级触发器将被激活一百万次，每更新一次行一次。

---

---

Another difference is the order or activation. For example in Oracle the 4 different types of triggers will be activated in the following order:

另一个区别是顺序或激活。例如，在 Oracle 中，将按以下顺序激活 4 种不同类型的触发器：

Before the triggering statement executes
Before each row that the triggering statement affects
After each row that the triggering statement affects
After the triggering statement executes

In the previous example, we'd have something like:

在前面的例子中，我们有类似的东西：

Before statement-level trigger executes

  Before row-level trigger executes
  One row is updated
  After row-level trigger executes

  Before row-level trigger executes
  Second row is updated
  After row-level trigger executes

  ...

  Before row-level trigger executes
  Millionth row is updated
  After row-level trigger executes

After statement-level trigger executes

---

---

Addendum

附录

^*Regarding what rows can be modified by a trigger: Different DBMS have different limitations on this, depending on the specific implementation or triggers in the DBMS. For example, Oracle may show a "mutating table" errors for some cases, e.g. when a row-level trigger selects from the whole table (SELECT MAX(col) FROM tablename) or if it modifies other rows or the whole table and not only the row that is related to / triggered from.

^*关于触发器可以修改哪些行：不同的 DBMS 对此有不同的限制，具体取决于 DBMS 中的具体实现或触发器。例如，在某些情况下，Oracle 可能会显示“变异表”错误，例如，当行级触发器从整个表 ( SELECT MAX(col) FROM tablename) 中进行选择时，或者如果它修改了其他行或整个表，而不仅仅是与 / 相关的行触发自。

It is perfectly valid of course for a row-level trigger (in Oracle or other) to modify the row that its change has triggered it and that is a very common use. Example in dbfiddle.uk.

当然，行级触发器（在 Oracle 或其他中）修改其更改触发它的行是完全有效的，这是一种非常常见的用途。dbfiddle.uk中的示例。

Other DBMS may have different limitations on what any type of trigger can do and even what type of triggers are offered (some do not have BEFOREtriggers for example, some do not have statement level triggers at all, etc).

其他 DBMS 可能对任何类型的触发器可以做什么甚至提供什么类型的触发器有不同的限制（BEFORE例如，有些没有触发器，有些根本没有语句级触发器等）。

回答by Rose Perrone

You may want trigger action to execute once after the client executes a statement that modifies a million rows (statement-level trigger). Or, you may want to trigger the action once for every row that is modified (row-level trigger).

您可能希望触发器操作在客户端执行修改一百万行的语句后执行一次（语句级触发器）。或者，您可能希望为修改的每一行触发一次操作（行级触发器）。

EXAMPLE: Let's say you have a trigger that will make sure all high school seniors graduate. That is, when a senior's grade is 12, and we increase it to 13, we want to set the grade to NULL.

示例：假设您有一个触发器，可以确保所有高中毕业生都能毕业。也就是说，当一个大四的年级是 12 级时，我们将其增加到 13 级，我们希望将其设置为NULL。

For a statement level trigger, you'd say, after the increase-grade statement runs, check the whole table once to update any nows with grade 13 to NULL.

对于语句级触发器，您会说，在增加等级语句运行后，检查整个表一次以将等级 13 的任何 now 更新为NULL。

For a row-level trigger, you'd say, after every row that is updated, update the new row's grade to NULLif it is 13.

对于行级触发器，您会说，在更新每一行之后，将新行的等级更新NULL为 13。

A statement-level trigger would look like this:

语句级触发器如下所示：

create trigger stmt_level_trigger
after update on Highschooler
begin
    update Highschooler
    set grade = NULL
    where grade = 13;
end;

and a row-level trigger would look like this:

行级触发器如下所示：

create trigger row_level_trigger
after update on Highschooler
for each row
when New.grade = 13
begin
    update Highschooler
    set grade = NULL
    where New.ID = Highschooler.ID;
end;

Note that SQLite doesn't support statement-level triggers, so in SQLite, the FOR EACH ROWis optional.

请注意，SQLite 不支持语句级触发器，因此在 SQLite 中，FOR EACH ROW是可选的。

回答by pinaki

The main difference between statement level trigger is below :

语句级触发器之间的主要区别如下：

statement level trigger : based on name it works if any statement is executed. Does not depends on how many rows or any rows effected.It executes only once. Exp : if you want to update salary of every employee from department HR and at the end you want to know how many rows get effected means how many got salary increased then use statement level trigger. please note that trigger will execute even if zero rows get updated because it is statement level trigger is called if any statement has been executed. No matters if it is affecting any rows or not.

语句级触发器：基于名称，如果执行任何语句，它就会起作用。不取决于受影响的行数或任何行数。它只执行一次。Exp ：如果你想从部门 HR 更新每个员工的工资，最后你想知道有多少行受到影响意味着有多少工资增加了，然后使用语句级触发器。请注意，即使更新了零行，触发器也会执行，因为如果执行了任何语句，则会调用语句级触发器。不管它是否影响任何行。

Row level trigger : executes each time when an row is affected. if zero rows affected.no row level trigger will execute.suppose if u want to delete one employye from emp table whose department is HR and u want as soon as employee deleted from emp table the count in dept table from HR section should be reduce by 1.then you should opt for row level trigger.

行级触发器：每次当一行受到影响时执行。如果受影响的行为零。没有行级触发器将执行。假设如果您想从部门为 HR 的 emp 表中删除一名员工，并且您希望从 emp 表中删除员工后，HR 部分的部门表中的计数应减少1.那么你应该选择行级触发器。

回答by Sajal Agarwal

1)row level trigger is used to perform action on set of rows as insert , update or delete

1）行级触发器用于对行集执行操作，如插入、更新或删除

example:-you have to delete a set of rows and simultaneously that deleted rows must also inserted in new table for audit purpose;

例如：-您必须删除一组行，同时为了审计目的，删除的行也必须插入新表中；

2)statement level trigger:- it generally used to imposed restriction on the event you are performing.

2）语句级触发器：-通常用于对您正在执行的事件施加限制。

example:- restriction to delete the data between 10 pm and 6 am;

例如：- 限制在晚上 10 点到早上 6 点之间删除数据；

hope this helps:)

希望这可以帮助：）

回答by A rajasekhar

statement level trigger is only once for dml statement row leval trigger is for each row for dml statements

dml 语句行级别触发器仅针对 dml 语句的每一行的语句级触发器

回答by ananth

if you want to execute the statement when number of rows are modified then it can be possible by statement level triggers.. viseversa... when you want to execute your statement each modification on your number of rows then you need to go for row level triggers..

如果您想在修改行数时执行该语句，则可以通过语句级触发器.. viseversa ...当您想执行您的语句时，每次修改您的行数时，您都需要进行行级别触发..

for example: statement level triggers works for when table is modified..then more number of records are effected. and row level triggers works for when each row updation or modification..

例如：语句级触发器适用于修改表时..然后会影响更多的记录。和行级触发器适用于每行更新或修改时..