Mariadb Create Trigger

CREATE TRIGGER in MariaDB

Last updated: 2026-07-20

This skill covers the delta between standard SQL triggers and MariaDB's. It assumes the agent already knows the standard trigger model (fires BEFORE/AFTER a row-level DML event, OLD/NEW row aliases). Everything below documents MariaDB-specific grammar, ordering of multiple triggers, restrictions, and the most common LLM traps.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

Syntax

CREATE [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    TRIGGER [IF NOT EXISTS]
            trigger_name trigger_time {trigger_event [OR trigger_event] ...}
    ON tbl_name FOR EACH ROW
    [{ FOLLOWS | PRECEDES } other_trigger_name]
    trigger_stmt

trigger_time: BEFORE | AFTER
trigger_event: INSERT | UPDATE [OF column_name [, column_name] ...] | DELETE

• tbl_name must be a permanent base table — not a view, not a TEMPORARY table.
• Requires the TRIGGER privilege on tbl_name (plus SET USER/SUPER to use a DEFINER other than the current user).
• DEFINER defaults to CURRENT_USER when omitted.
• Multi-statement bodies need BEGIN … END; use the DELIMITER client command to type them interactively.
• There is no ALTER TRIGGER. Modify via CREATE OR REPLACE TRIGGER, or DROP TRIGGER + recreate.

Multiple Trigger Events in One Statement (since 12.0)

A single CREATE TRIGGER can react to more than one DML event by chaining trigger_event with OR:

CREATE TRIGGER t1_b_any BEFORE INSERT OR UPDATE OR DELETE ON t1 FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO t2 VALUES (NEW.a, 'INSERTING');
  ELSEIF UPDATING THEN
    INSERT INTO t2 VALUES (NEW.a, 'UPDATING');
  ELSEIF DELETING THEN
    INSERT INTO t2 VALUES (OLD.a, 'DELETING');
  END IF;
END;

INSERTING, UPDATING, DELETING are boolean predicates usable inside the body to branch on which event fired. On MariaDB versions before 12.0 (including all current 10.x/11.x LTS lines), only a single trigger_event is allowed per trigger — write three separate triggers instead, one per event.

Multiple Triggers per Action Time + Event — FOLLOWS/PRECEDES

Unlike the single-event-per-trigger constraint above, having more than one trigger for the same trigger_time + trigger_event combination on a table has always been supported in MariaDB (this predates the 10.6 baseline — no version gate applies). Use FOLLOWS/PRECEDES to control firing order:

CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a := 1;
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a := 2;
CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi SET @a := 0;

• FOLLOWS other_trigger places the new trigger immediately after other_trigger in the firing order.

• PRECEDES other_trigger places it immediately before.

• Omitting both appends the new trigger last for that action time + event.

• Verify the actual order via ACTION_ORDER in INFORMATION_SCHEMA.TRIGGERS:

  SELECT trigger_name, action_order FROM information_schema.triggers
    WHERE event_object_table = 't1';
  

OLD / NEW Row References

• OLD.col is always read-only.
• NEW.col is readable everywhere, and in a BEFORE trigger it can be assigned with SET NEW.col = value to change what actually gets written. In an AFTER trigger, assigning NEW.col is a no-op — the row is already committed to storage.
• Neither OLD nor NEW can reference generated columns.
• OLD/NEW are MariaDB extensions to standard SQL trigger syntax, and are not case-sensitive.

Restrictions

Triggers inherit both the stored-routine and stored-function limitation sets, plus their own:

• Cannot be defined on a TEMPORARY table or a view — permanent base tables only.
• Cannot return a result set (a bare SELECT is rejected; SELECT ... INTO is fine).
• No explicit or implicit COMMIT/ROLLBACK/START TRANSACTION inside the body.
• Cannot modify a table that the invoking statement has already opened for read or write (ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG).
• No PREPARE/EXECUTE/DEALLOCATE PREPARE (no dynamic SQL).
• Not activated by foreign-key cascade actions (ON DELETE CASCADE, etc.) or by TRUNCATE TABLE.
• Standard FOR EACH STATEMENT triggers are not supported — MariaDB triggers are always row-level (FOR EACH ROW is mandatory in the grammar).
• If called indirectly (a trigger calling a stored procedure via CALL), the called routine inherits the trigger's restrictions — e.g. it still can't COMMIT or return a result set.

Trigger Firing on Compound Statements

• LOAD DATA INFILE / LOAD XML fire INSERT triggers per row.
• REPLACE fires, in order: BEFORE INSERT, then (only if a row is actually deleted) BEFORE DELETE/AFTER DELETE, then AFTER INSERT.
• INSERT ... ON DUPLICATE KEY UPDATE fires BEFORE INSERT, then (if the row already exists) BEFORE UPDATE/AFTER UPDATE instead of the insert path.
• DROP TABLE, TRUNCATE TABLE, and dropping a partition do not fire DELETE triggers.

Examples

-- OR REPLACE avoids the "trigger already exists" error on redeploy
CREATE OR REPLACE DEFINER=`app_user`@`%` TRIGGER trg_limit_population
  BEFORE UPDATE ON country_stats FOR EACH ROW
BEGIN
  IF NEW.population < 1 THEN
    SET NEW.population = 1;
  ELSEIF NEW.population > 2000000000 THEN
    SET NEW.population = 2000000000;
  END IF;
END;

-- IF NOT EXISTS: idempotent create, warns instead of erroring if present
CREATE TRIGGER IF NOT EXISTS increment_animal
  AFTER INSERT ON animals FOR EACH ROW
  UPDATE animal_count SET animal_count.animals = animal_count.animals + 1;

See Also

• mariadb-create-procedure / mariadb-create-function — shared stored-routine restriction surface (no result sets from functions, no dynamic SQL, DELIMITER gotcha)
• Canonical references on mariadb.com/docs:
  • https://mariadb.com/docs/server/server-usage/triggers-events/triggers/create-trigger
  • https://mariadb.com/docs/server/server-usage/triggers-events/triggers/trigger-overview
  • https://mariadb.com/docs/server/server-usage/triggers-events/triggers/trigger-limitations
  • https://mariadb.com/docs/server/server-usage/triggers-events/triggers/triggers-and-implicit-locks
  • https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-trigger

This page is: Copyright © 2026 MariaDB. All rights reserved.