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?
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.
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..
statement level trigger is only once for dml statement row leval trigger is for each row for dml statements
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.
Source: Stackoverflow.com