Statement-Level Triggers - Oracle to MariaDB Migration

In Oracle, if you omit the FOR EACH ROW clause in CREATE TRIGGER, then the trigger is a statement trigger which is executed once per triggering statement.

MariaDB does not support statement-level triggers, and the FOR EACH ROW clause is mandatory.

Let's see how we can impelement statement-level triggers in MariaDB.

Oracle:

  -- Sample table and data
  CREATE TABLE colors (name VARCHAR2(30));
  INSERT INTO colors VALUES ('Orange');
  INSERT INTO colors VALUES ('Blue');
  INSERT INTO colors VALUES ('Red');
 
  -- Audit table
  CREATE TABLE audit_data (message VARCHAR2(30), dt DATE);
 
  -- Statement-level trigger
  CREATE TRIGGER update_colors
    BEFORE UPDATE ON colors
  BEGIN
    INSERT INTO audit_data VALUES ('Updated', SYSDATE());
  END;
  /

Now let's update rows and check the audit table:

Oracle

  UPDATE colors SET name = UPPER(name);
  /* 3 rows updated. */
 
  -- Just 1 row added to audit table
  SELECT * FROM audit_data;
  /* Updated    2025-12-16 20:49:37 */

You can see that the UPDATE statement changed 3 rows, but only 1 row was written to the audit table, since the statement-level trigger fired once.

In MariaDB, the NOW() function returns a constant time that indicates the time at which the triggering statement began to execute.

In a FOR EACH ROW trigger, NOW() returns the same value for all triggering rows, and we can use this behavior to ensure that the trigger's logic runs only once.

MariaDB:

  DELIMITER //
 
  // Emulating statement-level trigger
  CREATE TRIGGER update_colors
    BEFORE UPDATE ON colors
    FOR EACH ROW
  BEGIN
    -- Executed once per triggering statement
    IF @update_colors_stmt_time IS NULL OR @update_colors_stmt_time < NOW(6)
    THEN
      INSERT INTO audit_data VALUES ('Updated', NOW());
      SET @update_colors_stmt_time := NOW(6);
    END IF;
  END;
  //
 
  DELIMITER ;

In this example, we use the session variable @update_colors_stmt_time to ensure that the trigger is executed only once per specified time, with microsecond precision.

MariaDB:

  -- Before running any updates
  SELECT @update_colors_stmt_time;
  /* NULL */
 
  UPDATE colors SET name = UPPER(name);
  /* Query OK, 3 rows affected 
      Rows matched: 3  Changed: 3  Warnings: 0 */
 
  -- Just 1 row added to audit table    
  SELECT * FROM audit_data;
  /* Updated   2025-12-16 21:07:03 */
 
  -- After updating rows
  SELECT @update_colors_stmt_time;
  /* 2025-12-16 21:07:03.561742 */

This approach cannot be used:

• If the statement-level trigger logic needs to execute only after all triggering rows have been processed.

• In the unlikely scenario where a single session executes multiple UPDATE statements within the same microsecond (it is ok for multiple different sessions to run concurrently as the trigger uses a session variable).

For more information, see Oracle to MariaDB Migration.