2

I understand very well the idea behind triggers, how and when to use them.

I also see the practical use of a FOR EACH ROW trigger.

But I lack to find a concrete example of when I would use a FOR STATEMENT trigger. What could I do with it?

Can anyone give me an example of a real life use of that?

SteeveDroz
  • 123
  • 1
  • 5
  • 1
    which database system are you referring here, SQL Server, Oracle, MySQL or something else? – jyao Feb 22 '19 at 16:39
  • @jyao: Probably either Oracle or Postgres because those are the only ones to offer the choice between row level and statement level triggers –  Feb 22 '19 at 17:46
  • Postgres, but I mostly need to understand the concept. – SteeveDroz Feb 23 '19 at 11:46

1 Answers1

6

I cannot speak to all database platforms that support statement level triggers, but DB2 LUW also supports both row level triggers AND statement level triggers.

Referencing the documentation for DB2 LUW (highlighting mine) - Specifying what makes a trigger fire (triggering statement or event)

When a trigger is activated, it runs according to its level of granularity as follows:

FOR EACH ROW

It runs as many times as the number of rows in the set of affected rows.

If you need to refer to the specific rows affected by the triggered action, use FOR EACH ROW granularity. An example of this is the comparison of the new and old values of an updated row in an AFTER UPDATE trigger.

FOR EACH STATEMENT

It runs once for the entire trigger event.

If the set of affected rows is empty (that is, in the case of a searched UPDATE or DELETE in which the WHERE clause did not qualify any rows), a FOR EACH ROW trigger does not run. But a FOR EACH STATEMENT trigger still runs once.

For example, keeping a count of number of employees can be done using FOR EACH ROW.

CREATE TRIGGER NEW_HIRED 
  AFTER INSERT ON EMPLOYEE 
  FOR EACH ROW
  UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

You can achieve the same affect with one update by using a granularity of FOR EACH STATEMENT.

CREATE TRIGGER NEW_HIRED 
  AFTER INSERT ON EMPLOYEE 
  REFERENCING NEW_TABLE AS NEWEMPS 
  FOR EACH STATEMENT
  UPDATE COMPANY_STATS 
  SET NBEMP = NBEMP + (SELECT COUNT(*) FROM NEWEMPS)

Some examples of why you might want to use a statement level trigger over a row level trigger:

  • You want to record how many rows were updated, but don't necessarily care about which rows were updated
  • You want to execute an action when a statement is made against a table, even if no rows are updated
  • Your table has 40 Billion rows and sometime updates are made against 10% of the table. Recording an action for 4 Billion rows, one at a time, is not feasible due to resource constraints
  • You want to record any time any person makes an update to a table, but you don't care what the update was, and you don't care how many rows were affected.
Scott Hodgin - Retired
  • 23,854
  • 2
  • 26
  • 48
  • Tell me if I understand correctly: one can use statement to implement rows their own way... So you imply it's almost useless. – SteeveDroz Feb 23 '19 at 11:49
  • @SteeveDroz - I updated my answer to include some examples of why you might want to use a statement level trigger over a row level trigger. – Scott Hodgin - Retired Feb 23 '19 at 12:44
  • Perfect! Those are the kind of examples I was looking for! Thanks. – SteeveDroz Feb 23 '19 at 12:56
  • @SteeveDroz - Awesome, glad to help :) – Scott Hodgin - Retired Feb 23 '19 at 12:58
  • Another niche use case: statement-level triggers can be used as a workaround to prevent recursive triggers or updating the same tuple twice (which will error). This is done by essentially having a bit of a broader view (both in time and of rows) before actually applying an update as desired. But it takes some care to be done right. – Alexi Theodore May 24 '23 at 22:46