Triggers, OLD/NEW Rows, Ordering, Auditing, and Hidden Coupling

Learning outcomes

ServiceHub has three writers: the web API, a bulk-import tool and an operations console. All of them must record status changes. A trigger is server-side code that MariaDB executes automatically for each affected row when a configured table event occurs. That centrality is useful for invariants and audit capture, but it creates hidden coupling: an application can issue one UPDATE while triggers perform additional writes, acquire more locks, raise errors or consume substantial time.

1. OLD and NEW are row images, not magic variables

OLD.column represents the row before the triggering operation. NEW.column represents the candidate row after the operation. A BEFORE trigger can validate or normalize the candidate row before storage. An AFTER trigger sees the stored result and is a common place to append an audit record. Keep business rules in explicit constraints when a constraint can express them; use triggers when the behavior truly needs row-event context.

DROP DATABASE IF EXISTS servicehub_programmability_lab;CREATE DATABASE servicehub_programmability_lab;USE servicehub_programmability_lab;CREATE TABLE work_orders (  work_order_id BIGINT PRIMARY KEY,  status ENUM('open','assigned','closed','cancelled') NOT NULL,  priority TINYINT NOT NULL,  assigned_team VARCHAR(40) NULL,  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;CREATE TABLE work_order_audit (  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,  work_order_id BIGINT NOT NULL,  old_status VARCHAR(20) NULL,  new_status VARCHAR(20) NULL,  changed_by VARCHAR(128) NOT NULL,  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  source_tag VARCHAR(40) NOT NULL) ENGINE=InnoDB;INSERT INTO work_orders VALUES(3001,'open',5,'alpha','2026-08-20 09:00:00'),(3002,'assigned',3,'beta','2026-08-20 09:05:00');

2. Use BEFORE for validation/normalization and AFTER for durable audit capture

The first trigger normalizes updated_at and rejects a deliberately forbidden transition from cancelled back to assigned. The second records status changes only when the value actually changes. The application sets an optional session tag so the audit row can distinguish API, migration and lab traffic.

DELIMITER $$CREATE OR REPLACE TRIGGER tr_wo_before_updateBEFORE UPDATE ON work_ordersFOR EACH ROWBEGIN  SET NEW.updated_at=CURRENT_TIMESTAMP;  IF OLD.status='cancelled' AND NEW.status='assigned' THEN    SIGNAL SQLSTATE '45000'      SET MESSAGE_TEXT='cancelled work order cannot be reassigned';  END IF;END$$CREATE OR REPLACE TRIGGER tr_wo_after_update_auditAFTER UPDATE ON work_ordersFOR EACH ROWBEGIN  IF NOT (OLD.status <=> NEW.status) THEN    INSERT INTO work_order_audit(      work_order_id,old_status,new_status,changed_by,source_tag    ) VALUES (      NEW.work_order_id,OLD.status,NEW.status,CURRENT_USER(),      COALESCE(@servicehub_source,'unspecified')    );  END IF;END$$DELIMITER ;SET @servicehub_source='chapter11-lab';UPDATE work_orders SET status='assigned' WHERE work_order_id=3001;SELECT * FROM work_orders WHERE work_order_id=3001;SELECT work_order_id,old_status,new_status,changed_by,source_tagFROM work_order_audit WHERE work_order_id=3001;SHOW CREATE TRIGGER tr_wo_after_update_audit\G

The expected result is one base-row change and one audit row from open to assigned. CURRENT_USER() reflects the security identity used by the stored object, not necessarily an end-user identity from the application. If the business needs a real human/application principal, carry that identity explicitly and validate how it is set rather than confusing database account identity with business actor identity.

3. Multiple triggers make execution order part of the interface

Current MariaDB allows multiple triggers for the same table, event and timing. FOLLOWS and PRECEDES let you position a new trigger relative to an existing one. The server records an ACTION_ORDER value; inspect it instead of trusting creation history from memory.

CREATE TABLE trigger_probe (  probe_id BIGINT AUTO_INCREMENT PRIMARY KEY,  work_order_id BIGINT NOT NULL,  note VARCHAR(100) NOT NULL,  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;DELIMITER $$CREATE OR REPLACE TRIGGER tr_wo_after_update_probeAFTER UPDATE ON work_ordersFOR EACH ROWFOLLOWS tr_wo_after_update_auditBEGIN  INSERT INTO trigger_probe(work_order_id,note)  VALUES(NEW.work_order_id,CONCAT('after audit: ',OLD.status,' -> ',NEW.status));END$$DELIMITER ;SELECT TRIGGER_NAME,ACTION_TIMING,EVENT_MANIPULATION,ACTION_ORDER,DEFINERFROM information_schema.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_programmability_lab'  AND EVENT_OBJECT_TABLE='work_orders'ORDER BY ACTION_TIMING,EVENT_MANIPULATION,ACTION_ORDER;

The metadata should place the probe after the audit trigger for the same AFTER UPDATE event. Ordering is now part of deployment state: capture it in SHOW CREATE TRIGGER/metadata and test it after restore or migration. MariaDB documentation states that trigger order is preserved by dump/restore tooling, but your deployment validation should still verify the target rather than assume it.

4. Deliberately wrong: hide a required application context inside the trigger

A common audit design requires an application to set a session variable such as @app_actor before every write. If the trigger blindly inserts that variable into a NOT NULL column, bulk tools and migration scripts that do not know the convention can fail. This is a hidden contract.

DROP TRIGGER tr_wo_after_update_audit;DELIMITER $$CREATE TRIGGER tr_wo_after_update_auditAFTER UPDATE ON work_ordersFOR EACH ROWBEGIN  IF NOT (OLD.status <=> NEW.status) THEN    INSERT INTO work_order_audit(      work_order_id,old_status,new_status,changed_by,source_tag    ) VALUES (      NEW.work_order_id,OLD.status,NEW.status,@app_actor,'fragile-trigger'    );  END IF;END$$DELIMITER ;SET @app_actor=NULL;UPDATE work_orders SET status='closed' WHERE work_order_id=3002;SELECT work_order_id,status FROM work_orders WHERE work_order_id=3002;SELECT * FROM work_order_audit WHERE work_order_id=3002;

The update should fail because changed_by is NOT NULL. With both tables using InnoDB, the trigger error makes the statement fail and the base-row update is not committed. That rollback is valuable, but the operational surprise is not: the client thought it was updating one row and did not know about a mandatory session variable.

Repair the contract by either making the actor explicit in the write path (for example through a reviewed procedure), or defining a safe fallback that is semantically acceptable. Do not invent a fake human actor merely to satisfy NOT NULL.

DROP TRIGGER tr_wo_after_update_audit;DELIMITER $$CREATE TRIGGER tr_wo_after_update_auditAFTER UPDATE ON work_ordersFOR EACH ROWBEGIN  IF NOT (OLD.status <=> NEW.status) THEN    INSERT INTO work_order_audit(      work_order_id,old_status,new_status,changed_by,source_tag    ) VALUES (      NEW.work_order_id,OLD.status,NEW.status,      COALESCE(@app_actor,CURRENT_USER()),      COALESCE(@servicehub_source,'unspecified')    );  END IF;END$$DELIMITER ;SET @app_actor=NULL;SET @servicehub_source='migration-test';UPDATE work_orders SET status='closed' WHERE work_order_id=3002;SELECT work_order_id,status FROM work_orders WHERE work_order_id=3002;SELECT work_order_id,old_status,new_status,changed_by,source_tagFROM work_order_audit WHERE work_order_id=3002;

This repair removes the hard failure for clients without the session variable, but it changes audit meaning. Production teams must decide whether a database account fallback is acceptable. If not, route status changes through an explicit procedure that requires an actor parameter and make direct table DML unavailable to the application.

5. Hidden coupling appears in performance, migrations, recursion and topology

An audit trigger is also not a tamper-proof compliance ledger. Privileged users who can alter triggers or audit tables can change the mechanism. For stronger audit requirements, combine least privilege, immutable/off-server evidence, database audit facilities where appropriate, and monitored deployment controls.

6. Production judgment, verification, and cleanup

Use triggers for small, data-local invariants or audit capture that truly must follow every SQL writer. Prefer explicit procedures or application workflows when logic needs rich business context, external calls, complex retries or independent deployment cadence. Triggers should not call external services; the database transaction must remain deterministic and observable.

1. Run SHOW TRIGGERS and query INFORMATION_SCHEMA.TRIGGERS including ACTION_ORDER.
2. Test INSERT/UPDATE/DELETE cases relevant to each trigger's OLD/NEW usage.
3. Failure-inject a trigger error and verify expected rollback on InnoDB.
4. Measure bulk operations with triggers enabled; do not extrapolate from one-row latency.
5. Document trigger dependencies in migrations and restore tests.

SHOW TRIGGERS FROM servicehub_programmability_lab;SELECT TRIGGER_NAME,EVENT_MANIPULATION,ACTION_TIMING,ACTION_ORDER,DEFINERFROM information_schema.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_programmability_lab'ORDER BY EVENT_OBJECT_TABLE,EVENT_MANIPULATION,ACTION_TIMING,ACTION_ORDER;DROP DATABASE IF EXISTS servicehub_programmability_lab;

Lesson 4 keeps automatic server-side execution but moves from “when a row changes” to “when time reaches a schedule,” where ownership, overlap, time zones and failure visibility become the main risks.