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