row-level trigger vs statement-level trigger

by

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

More Related Contents:

  1. SQL Data models
  2. How to randomly select rows in SQL?
  3. What’s the difference between TRUNCATE and DELETE in SQL
  4. SQL best practice to deal with default sort order
  5. Strings as Primary Keys in SQL Database [closed]
  6. How can I get column names from a table in Oracle?
  7. Decision between storing lookup table id’s or pure data
  8. single fixed table with multiple columns vs flexible abstract tables
  9. How can I send email from PostgreSQL trigger?
  10. How to design a database for User Defined Fields?
  11. How to delete Duplicates in MySQL table
  12. Normalization in plain English
  13. Core Data Performance with Single Parent Entity
  14. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  15. Why does VARCHAR need length specification?
  16. How to Optimize Queries in a Database – The Basics
  17. Know relationships between all the tables of database in SQL Server
  18. Bulk/batch update/upsert in PostgreSQL
  19. How to populate calendar table in Oracle?
  20. Create date from day, month, year fields in MySQL
  21. Grant privileges for a particular database in PostgreSQL
  22. The object ‘DF__*’ is dependent on column ‘*’ – Changing int to double
  23. How do I change db schema to dbo
  24. What is the best way to implement soft deletion?
  25. PostgreSQL how to create a copy of a database or schema?
  26. Query a Table’s Foreign Key relationships
  27. Historical / auditable database
  28. Postgres: convert single row to multiple rows (unpivot)
  29. How to store a list in a db column
  30. Copy table structure to new table in sqlite3

Leave a Comment