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 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?

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).

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.

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

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.

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

hope this helps:)

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.

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.

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

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..

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..