row-level trigger vs statement-level trigger

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.

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

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

row-level trigger vs statement-level trigger

Tags:

Database

Sql

Related

Recent Posts