Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 40 ยท Stored Programs and Triggers

Triggers in MariaDB

Create MariaDB triggers for auditing, validation and derived data: BEFORE and AFTER INSERT, UPDATE, DELETE, the NEW and OLD rows, SIGNAL errors, trigger order and pitfalls.

A trigger is a stored program that MariaDB runs automatically when a row in a table is inserted, updated or deleted. Because it lives in the database, it fires for every client: your web app, an admin script, a data import. That makes triggers a good home for rules that must never be skipped, such as an audit trail, or normalizing a value before it is stored.

They also run invisibly, so overuse makes systems hard to reason about. This lesson shows how to write them, where they help, and where to avoid them. MariaDB trigger syntax is read-only here; the in-browser engine has triggers with slightly different syntax, so the runnable examples demonstrate the same behaviour.

Anatomy

SQL
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
trigger_body;
  • Timing: BEFORE runs before the row is written (you can change NEW values or reject the change); AFTER runs after it succeeded (good for logging and side effects).
  • Event: one of INSERT, UPDATE, DELETE. To react to several events, create several triggers (MariaDB 10.2.3+ allows several triggers per event; older versions allowed one).
  • FOR EACH ROW: MariaDB triggers are row-level. They run once for every affected row, so a statement that updates 10,000 rows runs the body 10,000 times.
  • NEW and OLD: NEW.col is the row being inserted or the new version in an update; OLD.col is the existing row in an update or delete. INSERT has only NEW, DELETE only OLD. In a BEFORE trigger you can assign SET NEW.col = ...; in AFTER triggers NEW is read-only.

Multi-statement bodies use BEGIN ... END and the usual DELIMITER trick in the CLI (see variables and stored functions).

Example 1: audit log with AFTER triggers

SQL
CREATE TABLE salary_audit (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  employee_id  INT NOT NULL,
  old_salary   INT,
  new_salary   INT,
  changed_by   VARCHAR(100),
  changed_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER employees_au_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NOT (OLD.salary <=> NEW.salary) THEN
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_by)
    VALUES (OLD.id, OLD.salary, NEW.salary, USER());
  END IF;
END //
DELIMITER ;

<=> is the NULL-safe equality operator: it is true when both sides are NULL, so the log only records real changes. USER() returns the connecting account, which is what an auditor wants.

Runnable version (SQLite syntax: WHEN replaces the IF, and IS NOT is the null-safe comparison):

SQL Audit trail with a trigger

Press Run (or Ctrl + Enter) to see the result here.

Example 2: validation and normalization with BEFORE triggers

Use BEFORE to clean data before it is written and to reject invalid rows with SIGNAL:

SQL
DELIMITER //
CREATE TRIGGER customers_bi
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));

  IF NEW.email NOT LIKE '%_@_%.__%' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Invalid e-mail address';
  END IF;

  IF NEW.created_at IS NULL THEN
    SET NEW.created_at = NOW();
  END IF;
END //
DELIMITER ;

Where you can, prefer declarative rules. A CHECK constraint (enforced since MariaDB 10.2.1), NOT NULL, DEFAULT, UNIQUE and foreign keys are cheaper, visible in the schema and impossible to disable by accident. Triggers are for what constraints cannot express.

The portable engine's RAISE(ABORT, 'message') rejects a row with an error like SIGNAL does. To let the demo finish, this version uses RAISE(IGNORE), which silently skips the bad row instead (change it to RAISE(ABORT, 'Invalid e-mail address') to see the error):

SQL Reject bad rows in a BEFORE trigger

Press Run (or Ctrl + Enter) to see the result here.

Only the two valid rows are stored. (SQLite triggers cannot assign to NEW, so the lower-casing step is MariaDB-only.)

Example 3: keeping a derived value in step

SQL
CREATE TRIGGER order_lines_ai
AFTER INSERT ON order_lines
FOR EACH ROW
UPDATE orders SET total = total + NEW.quantity * NEW.unit_price WHERE id = NEW.order_id;

A running total stored on the parent row saves an aggregate on every read, but you must also handle UPDATE and DELETE on the lines, or the total drifts. Often a view or a query with SUM is simpler and always right. Denormalize only when measurements show the read is too slow.

Managing triggers

SQL
SHOW TRIGGERS;                                  -- in the current database
SHOW TRIGGERS LIKE 'employees';
SHOW CREATE TRIGGER employees_au_salary;
DROP TRIGGER IF EXISTS employees_au_salary;
CREATE OR REPLACE TRIGGER ... ;                 -- MariaDB 10.1.4+
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = DATABASE();

When several triggers exist for the same event, they run in creation order. Control it with FOLLOWS other_trigger or PRECEDES other_trigger after FOR EACH ROW. You cannot rename a trigger or move it to another table; drop and recreate. Dropping a table drops its triggers.

Rules and limits

  • No triggers on views, temporary tables or system tables.
  • A trigger cannot use COMMIT or ROLLBACK or start a transaction: it runs inside the statement's transaction. If it raises an error, the whole statement is rolled back (on a transactional engine such as InnoDB).
  • It cannot modify the table that fired it by a direct statement (error 1442), except by assigning NEW in BEFORE triggers. This prevents infinite recursion.
  • Cascaded foreign-key actions (ON DELETE CASCADE) do not fire triggers on the child rows; TRUNCATE and INSERT ... ON DUPLICATE KEY have their own rules (REPLACE fires delete and insert triggers).
  • Triggers add latency to every write. Heavy logic inside them slows bulk loads, so some imports disable them by design (there is no DISABLE TRIGGER in MariaDB; load with a different account or drop and recreate).
  • Replication: with statement-based or mixed logging, a non-deterministic trigger may run differently on a replica; use row-based binary logging (see replication).

Triggers versus alternatives

Need Better tool
Required column, range, allowed values NOT NULL, CHECK, ENUM, foreign key
Default timestamp DEFAULT CURRENT_TIMESTAMP, ON UPDATE CURRENT_TIMESTAMP
Full history of every row version system-versioned tables (WITH SYSTEM VERSIONING)
Audit with who/when trigger (as above) or the server audit plugin
Business workflow application code or a stored procedure

Common mistakes

  • Expecting a statement-level trigger: MariaDB only has FOR EACH ROW.
  • Updating the same table from the trigger.
  • Forgetting NEW/OLD direction: OLD does not exist for INSERT.
  • Treating UPDATE triggers as "value changed": they fire even if the new value equals the old. Compare OLD and NEW yourself.
  • Hidden side effects that surprise developers: document every trigger next to the table definition.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which row references exist in a DELETE trigger?

    A deleted row has no new version, so only OLD is available. INSERT has only NEW; UPDATE has both.
  2. 2. Where can you change column values before they are stored?

    Only BEFORE triggers can modify NEW; in AFTER triggers it is read-only.
  3. 3. A statement updates 500 rows. How many times does a FOR EACH ROW trigger run?

    MariaDB triggers are row-level: the body runs for every affected row.
  4. 4. Why prefer a CHECK constraint over a trigger for a simple range rule?

    Declarative constraints are the simplest tool for simple rules; use triggers for what constraints cannot express.

Summary

  • A trigger runs BEFORE or AFTER an INSERT, UPDATE or DELETE, once per affected row, using NEW and OLD.
  • Typical uses: audit logs, normalization, rejecting invalid data with SIGNAL.
  • Prefer constraints and defaults when they are enough; keep trigger bodies short.
  • Triggers cannot commit, cannot change their own table directly, and are not fired by cascades.

Next: Cursors and Events