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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] row-level trigger vs statement-level trigger

Examples related to sql

Examples related to database