row-level trigger vs statement-level trigger

Question

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 created, the whole table can be modified. A row level trigger would only allow me to modify the tuple being affect by the trigger's specified event.

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

Answer 1

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.

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.

---

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 

---

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.

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.

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 BEFORE triggers for example, some do not have statement level triggers at all, etc).

Answer 2

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.

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.

For a row-level trigger, you'd say, after every row that is updated, update the new row's grade to NULL if it is 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 ROW is optional.