Use when defining triggers for BEFORE / AFTER INSERT / UPDATE / DELETE, scheduling recurring tasks via events, creating views including the materialized-view workaround, or controlling DEFINER vs INVOKER security context. Prevents the common mistake of overusing triggers for business logic, forgetting event_scheduler is OFF by default, expecting MariaDB to support materialized views (it does not), or choosing TEMPTABLE algorithm on huge views. Covers CREATE TRIGGER with FOLLOWS / PRECEDES, multi-trigger per event (10.2.3+), CREATE EVENT scheduler, ON COMPLETION PRESERVE / NOT PRESERVE, CREATE VIEW with ALGORITHM (UNDEFINED / MERGE / TEMPTABLE), DEFINER vs INVOKER, WITH CHECK OPTION LOCAL / CASCADED, materialized-view workaround via CREATE TABLE AS SELECT + EVENT. Keywords: CREATE TRIGGER, BEFORE INSERT, AFTER UPDATE, BEFORE DELETE, AFTER DELETE, FOR EACH ROW, FOLLOWS, PRECEDES, NEW.col, OLD.col, NEW pseudo-row, OLD pseudo-row, CREATE EVENT, ON SCHEDULE EVERY, ON SCHEDULE AT, event_scheduler, SET GLOBAL event_
Use when defining triggers for BEFORE / AFTER INSERT / UPDATE / DELETE, scheduling recurring tasks via events, creating views including the materialized-view workaround, or controlling DEFINER vs INVOKER security context. Prevents the common mistake of overusing triggers for business logic, forgetting event_scheduler is OFF by default, expecting MariaDB to support materialized views (it does not), or choosing TEMPTABLE algorithm on huge views. Covers CREATE TRIGGER with FOLLOWS / PRECEDES, multi-trigger per event (10.2.3+), CREATE EVENT scheduler, ON COMPLETION PRESERVE / NOT PRESERVE, CREATE VIEW with ALGORITHM (UNDEFINED / MERGE / TEMPTABLE), DEFINER vs INVOKER, WITH CHECK OPTION LOCAL / CASCADED, materialized-view workaround via CREATE TABLE AS SELECT + EVENT. Keywords: CREATE TRIGGER, BEFORE INSERT, AFTER UPDATE, BEFORE DELETE, AFTER DELETE, FOR EACH ROW, FOLLOWS, PRECEDES, NEW.col, OLD.col, NEW pseudo-row, OLD pseudo-row, CREATE EVENT, ON SCHEDULE EVERY, ON SCHEDULE AT, event_scheduler, SET GLOBAL event_scheduler, ON COMPLETION PRESERVE, ON COMPLETION NOT PRESERVE, DISABLE ON SLAVE, CREATE VIEW, CREATE OR REPLACE VIEW, ALGORITHM, MERGE, TEMPTABLE, UNDEFINED, DEFINER, SQL SECURITY DEFINER, SQL SECURITY INVOKER, WITH CHECK OPTION, LOCAL, CASCADED, materialized view, CREATE TABLE AS SELECT, INFORMATION_SCHEMA.TRIGGERS, INFORMATION_SCHEMA.EVENTS, SHOW TRIGGERS, SHOW CREATE VIEW, trigger limitations, why is my trigger not firing, why is my event not running, my view is slow, my scheduled job never runs, how to refresh a materialized view in MariaDB, MariaDB materialized view alternative, audit trigger, history trigger
license
MIT
compatibility
Designed for Claude Code. Requires MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x.
metadata
{"author":"OpenAEC-Foundation","version":"1.0"}
MariaDB Triggers, Events, and Views
Deterministic guidance for row-level triggers, the event scheduler, and views in MariaDB. Use this skill when wiring side-effects to INSERT / UPDATE / DELETE, scheduling recurring SQL jobs, building updatable views with security boundaries, or simulating materialized views via the table-plus-event refresh pattern.
Quick Reference
ALWAYS turn on the event scheduler before relying on events. The global event_scheduler variable defaults to OFF on most distros. A CREATE EVENT statement succeeds, but the event never fires until SET GLOBAL event_scheduler = ON is executed AND persisted in [mysqld] of my.cnf so it survives restart. Verify with SHOW VARIABLES LIKE 'event_scheduler'. Verified : KB events/.
ALWAYS use FOR EACH ROW. MariaDB has NO statement-level triggers. Every trigger fires once per affected row, so a 10 000-row bulk DML fires the trigger 10 000 times. Keep trigger bodies short ; move heavy logic to stored procedures invoked once by the application. Verified : KB create-trigger/.
NEVER expect materialized views in MariaDB. The keyword MATERIALIZED VIEW does not exist. The supported alternative is CREATE TABLE snapshot AS SELECT ... ; plus an event-scheduled refresh that truncates and reloads the snapshot. The optimizer column select_type = MATERIALIZED refers to derived-table materialization, NOT to materialized-view objects. Verified : KB create-view/.
NEVER use COMMIT, ROLLBACK, SAVEPOINT, or START TRANSACTION inside a trigger. The statement parses at CREATE time but raises Explicit or implicit commit is not allowed in stored function or trigger at runtime. Triggers run inside the calling statement's transaction and cannot manage it. Verified : KB trigger-limitations/.
ALWAYS choose the right view ALGORITHM. UNDEFINED (default) lets the optimizer pick MERGE when possible. MERGE inlines the view body into the outer query, preserves index pushdown, and keeps the view updatable. TEMPTABLE materializes the entire view result into a per-query temp table, breaks index pushdown, and forces a full scan on every reference. NEVER use TEMPTABLE on a multi-million-row base table unless the view body contains a non-mergeable construct (DISTINCT, GROUP BY, aggregate, UNION) that the optimizer cannot push down anyway. Verified : KB .
create-view/
ALWAYS set DEFINER explicitly on security-sensitive objects. Views, triggers, events, and stored routines all carry a DEFINER clause. If the DEFINER account is dropped, the object becomes invalid and access fails with The user specified as a definer ... does not exist. For shared / multi-DBA environments, set DEFINER = 'role_owner'@'localhost' to a stable service account. Verified : KB create-view/, KB create-trigger/.
NEVER use DEFINER + SQL SECURITY DEFINER for code that must respect caller permissions. SQL SECURITY DEFINER (default) means the view / trigger / routine runs with the definer's privileges, bypassing the caller's GRANTs. Use SQL SECURITY INVOKER when the caller's GRANT set must be enforced. Verified : KB create-view/.
Multi-trigger per event requires explicit ordering since 10.2.3+. Use FOLLOWS other_trigger or PRECEDES other_trigger in the CREATE TRIGGER clause. Order is observable in INFORMATION_SCHEMA.TRIGGERS.ACTION_ORDER. Without explicit ordering, the engine assigns the next free ACTION_ORDER value, which is fragile across migrations. Verified : KB create-trigger/.
DISABLE ON SLAVE prevents double-execution of events under replication. Without it, the same event fires on both primary and replica with statement-based or mixed binlog. DISABLE ON SLAVE is the standard safety for any event that already replicates its effects through binlog. Verified : KB events/.
WITH CHECK OPTION rejects INSERT / UPDATE that produces a row outside the view's WHERE predicate. CASCADED (default) walks every view in the chain ; LOCAL checks only the immediate view. Without WITH CHECK OPTION, a row can be inserted through the view that the view itself cannot see. Verified : KB create-view/.
Pseudo-rows are scoped per timing. NEW.col is readable in BEFORE / AFTER INSERT and UPDATE triggers ; it is writable only in BEFORE triggers via SET NEW.col = expr. OLD.col is read-only in UPDATE and DELETE triggers. NEW does not exist in DELETE triggers ; OLD does not exist in INSERT triggers. Verified : KB trigger-overview/.
Decision Trees
Tree 1 : "Should this be a trigger, a CHECK constraint, an event, or application code?"
What is the requirement?
Validate a single-row invariant (range, regex, conditional non-null) on write ?
-> CHECK constraint (since 10.2, see mariadb-syntax-check-constraints).
Triggers are wrong here : CHECK is faster, declarative, and engine-enforced.
Maintain a derived column from other columns in the same row ?
-> Generated column (VIRTUAL or PERSISTENT).
Triggers are wrong here : generated columns are declarative and indexable.
Write an audit / history row to a DIFFERENT table on every change ?
-> AFTER INSERT / UPDATE / DELETE trigger.
This is the canonical valid use case for a trigger.
Block a write that depends on a multi-row condition ?
-> BEFORE INSERT / UPDATE trigger that raises SIGNAL SQLSTATE '45000'.
Note : multi-row conditions create lock contention. Prefer application-level
validation with proper isolation.
Run a job on a schedule ?
-> CREATE EVENT (one-shot AT timestamp, or recurring EVERY interval).
Application cron is acceptable too ; choose EVENT when the job is pure SQL
and you want it co-located with the schema.
Snapshot a slow aggregate query into a fast lookup table ?
-> Materialized-view workaround : CREATE TABLE snapshot AS SELECT ...
plus CREATE EVENT to refresh on schedule. See examples.md recipe.
Tree 2 : "Why is my event not running?"
Created the event, see nothing in the target table ?
Step 1 : SHOW VARIABLES LIKE 'event_scheduler' ;
Value = OFF -> the scheduler thread is not running.
Fix : SET GLOBAL event_scheduler = ON ;
Persist in [mysqld] section : event_scheduler = ON
Value = DISABLED -> server was started with --event-scheduler=DISABLED.
Fix : restart with --event-scheduler=ON, edit [mysqld].
SET GLOBAL alone does NOT work when DISABLED.
Value = ON -> scheduler is running, continue to Step 2.
Step 2 : SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'name' ;
STATUS = DISABLED -> event is disabled.
Fix : ALTER EVENT name ENABLE ;
STATUS = SLAVESIDE_DISABLED -> DISABLE ON SLAVE was set and this server is a replica.
Fix : nothing if intentional ; check the primary executes it.
LAST_EXECUTED IS NULL but ENABLED -> first run not yet due.
Fix : check ON SCHEDULE clause and STARTS timestamp.
LAST_EXECUTED is recent but no rows in target -> the DO body raised an error.
Fix : check error log ; wrap body in DECLARE HANDLER if expected.
Step 3 : ON COMPLETION NOT PRESERVE was set on a one-shot AT event.
Result : the event ran, completed, and was auto-dropped.
Fix : use ON COMPLETION PRESERVE if you need it to remain visible.
Tree 3 : "Why is my view slow?"
EXPLAIN SELECT ... FROM view_name :
type = ALL on a table the view selects from ?
Possible cause 1 : ALGORITHM = TEMPTABLE forces full materialization.
Fix : ALTER VIEW ... ALGORITHM = UNDEFINED ; or MERGE.
Possible cause 2 : view body uses DISTINCT, GROUP BY, aggregate, or UNION.
Result : optimizer is forced to TEMPTABLE because the view is non-mergeable.
Fix : if the aggregate is the requirement, accept the cost ; or use the
materialized-view workaround (snapshot table + event refresh).
Possible cause 3 : missing index on the base table.
Fix : see mariadb-syntax-indexing.
select_type = DERIVED for the view in EXPLAIN ?
-> materialization happened. Same fixes as TEMPTABLE above.
select_type does NOT mention the view ?
-> view was merged into the outer query, the slowness is in the base SQL.
Fix : optimize the base SELECT, see mariadb-impl-query-optimization.
Patterns
Pattern 1 : audit-log trigger (the canonical valid use case)
-- 10.6+CREATEOR REPLACE TABLE accounts (
id BIGINTPRIMARY KEY,
balance DECIMAL(20,2) NOT NULL,
status VARCHAR(20) NOT NULL
);
CREATEOR REPLACE TABLE balance_audit (
audit_id BIGINTPRIMARY KEY AUTO_INCREMENT,
account_id BIGINTNOT NULL,
old_bal DECIMAL(20,2),
new_bal DECIMAL(20,2),
changed_by VARCHAR(128),
changed_at TIMESTAMP(6) DEFAULTCURRENT_TIMESTAMP(6)
);
CREATEOR REPLACE TRIGGER trg_audit_balance
AFTER UPDATEON accounts
FOREACHROWINSERT INTO balance_audit (account_id, old_bal, new_bal, changed_by)
VALUES (NEW.id, OLD.balance, NEW.balance, CURRENT_USER());
Pattern 2 : validation trigger that raises SIGNAL
-- 10.6+CREATEOR REPLACE TRIGGER trg_check_positive_balance
BEFORE INSERTON accounts
FOREACHROWBEGIN
IF NEW.balance <0THEN
SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT ='balance must be non-negative';
END IF;
END;
Prefer a CHECK (balance >= 0) constraint when the rule is single-row and stateless. The trigger pattern is appropriate when the validation needs to consult another table or session context.
Pattern 3 : daily-cleanup event
-- 10.6+-- Step 1 : enable scheduler globally (and persist in [mysqld])SETGLOBAL event_scheduler =ON;
-- Step 2 : create the eventCREATEOR REPLACE EVENT ev_purge_old_audits
ON SCHEDULE EVERY1DAY
STARTS CURRENT_TIMESTAMP+INTERVAL1HOURON COMPLETION PRESERVE
ENABLE
COMMENT 'nightly audit retention'
DO
DELETEFROM balance_audit
WHERE changed_at < NOW() -INTERVAL90DAY;
ON COMPLETION PRESERVE keeps the event visible after its ENDS timestamp (and is harmless on recurring events). NOT PRESERVE on a one-shot AT event auto-drops the event after it fires.
Pattern 4 : view with MERGE algorithm (the fast default)
-- 10.6+CREATEOR REPLACE
ALGORITHM =MERGE
DEFINER ='reporting_owner'@'localhost'SQL SECURITY DEFINER
VIEW v_active_accounts ASSELECT id, balance
FROM accounts
WHERE status ='active'WITHCHECK OPTION;
WITH CHECK OPTION blocks an UPDATE through the view that would set status to anything other than 'active', because the resulting row would be invisible to the view.
-- 10.6+ : MariaDB has NO native materialized views.-- Approach : a real table plus an event that refreshes it.-- Step 1 : snapshot table seeded with the expensive queryCREATEOR REPLACE TABLE mv_daily_revenue ASSELECTDATE(order_at) AS d,
SUM(total) AS revenue,
COUNT(*) AS orders
FROM orders
GROUPBYDATE(order_at);
-- Step 2 : index it like any other tableCREATE INDEX ix_mv_daily_revenue_d ON mv_daily_revenue (d);
-- Step 3 : event-scheduled refreshCREATEOR REPLACE EVENT ev_refresh_mv_daily_revenue
ON SCHEDULE EVERY1HOURON COMPLETION PRESERVE
DISABLE ON SLAVE
DO
BEGINTRUNCATETABLE mv_daily_revenue;
INSERT INTO mv_daily_revenue
SELECTDATE(order_at), SUM(total), COUNT(*)
FROM orders
GROUPBYDATE(order_at);
END;
DISABLE ON SLAVE prevents the replica from refreshing in parallel ; the binlog of the primary replays the TRUNCATE and INSERT on the replica automatically. Use this pattern when the query is too expensive to run on every read but eventual-consistency at the chosen interval is acceptable.
Pattern 6 : multi-trigger ordering with FOLLOWS
-- 10.2.3+CREATEOR REPLACE TRIGGER trg_audit_login
AFTER UPDATEON accounts
FOREACHROWINSERT INTO login_log (account_id, at) VALUES (NEW.id, NOW(6));
CREATEOR REPLACE TRIGGER trg_audit_balance
AFTER UPDATEON accounts
FOREACHROW
FOLLOWS trg_audit_login
INSERT INTO balance_audit (account_id, old_bal, new_bal)
VALUES (NEW.id, OLD.balance, NEW.balance);
-- Verify : ACTION_ORDER column shows the firing sequenceSELECT TRIGGER_NAME, ACTION_ORDER
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE EVENT_OBJECT_TABLE ='accounts'ORDERBY ACTION_ORDER;
Cross-References
mariadb-syntax-check-constraints : prefer CHECK over BEFORE triggers for single-row validation.
mariadb-syntax-procedures-functions : split was made per D-01 ; triggers / events / views split off from stored routines for separation of concerns.
mariadb-syntax-indexing : view performance depends on base-table indexes ; materialized-view snapshots must be indexed explicitly.
mariadb-syntax-sql-dml : DML on views and the WITH CHECK OPTION semantics.
Reference Links
references/methods.md : complete CREATE / ALTER / DROP grammar for TRIGGER, EVENT, VIEW ; INFORMATION_SCHEMA schemata ; system variables ; SHOW commands.
references/examples.md : ten working recipes : audit trigger, SIGNAL validation, multi-trigger FOLLOWS, daily-cleanup event, hourly refresh event, view with MERGE, view with TEMPTABLE (and why to avoid it), view with WITH CHECK OPTION CASCADED, materialized-view workaround, role-scoped DEFINER pattern.
references/anti-patterns.md : eight real anti-patterns with root cause and fix : N+1 trigger lookups, event_scheduler OFF, TEMPTABLE on huge view, materialized-view expectation, dropped DEFINER, replication-format trigger desync, IGNORE-hidden CHECK OPTION violation, transaction control inside trigger.
Sources
All claims verified 2026-05-19 via WebFetch against the MariaDB Knowledge Base.
KB create-trigger/ : trigger grammar, FOLLOWS / PRECEDES, NEW / OLD scoping.
KB trigger-overview/ : BEFORE / AFTER timing for INSERT / UPDATE / DELETE.
