行级触发器vs语句级触发器 [英] row-level trigger vs statement-level trigger

问题描述

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

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?

谢谢！

推荐答案

主要区别不是可以通过触发器修改，这取决于DBMS。触发器（行或语句级）可以修改相同或其他表中的一行或多行，并且可以具有级联效应（触发其他动作/触发器），但是这些都取决于DBMS。

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.

主要区别是触发器被激活的次数。假设您有一个1M行表，并运行：

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

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.

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

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

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

这篇关于行级触发器vs语句级触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！