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

Learning outcomes

Triggers are easy to demonstrate and easy to overuse. An application executes one UPDATE; the database silently performs additional work because a named server object fires for every affected row. That can be exactly what an audit invariant needs—or it can become hidden coupling that surprises every future operator.

A realistic problem: audit every status transition, regardless of client

ServiceHub has a web API, a maintenance script, and an operator console. All three can update work_orders.status. If audit insertion lives only in one application, the history has gaps. A trigger can centralize the rule at the table boundary because every SQL update that activates the trigger executes the same audit logic.

A MySQL trigger is associated with one permanent table and one row event—INSERT, UPDATE, or DELETE—at either BEFORE or AFTER timing. It executes FOR EACH ROW. Triggers do not have an SQL SECURITY clause; their definer is part of their execution context.

Create an AFTER UPDATE audit trigger

Connect as logic_owner and create a trigger that records only real status transitions. NULL-safe comparison with <=> avoids treating equal nulls as a change.

USE servicehub_logic_lab;DROP TRIGGER IF EXISTS trg_work_orders_status_audit;DELIMITER $$CREATE TRIGGER trg_work_orders_status_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,change_source,changed_by)    VALUES      (NEW.work_order_id,OLD.status,NEW.status,'trigger',CURRENT_USER());  END IF;END$$DELIMITER ;SHOW CREATE TRIGGER trg_work_orders_status_audit\GSELECT TRIGGER_NAME,ACTION_TIMING,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,       ACTION_ORDER,DEFINERFROM INFORMATION_SCHEMA.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_logic_lab';

The metadata proves the trigger definition and recorded definer. It does not prove the trigger fires correctly under the application's privileges; test that separately.

Positive authorization test: caller cannot write audit table directly

As administrator, give the application only a narrow update privilege on selected work-order columns plus read access needed for verification. Do not grant it INSERT on the audit table.

GRANT SELECT ON servicehub_logic_lab.work_ordersTO 'logic_app'@'127.0.0.1';GRANT UPDATE(status,closed_at) ON servicehub_logic_lab.work_ordersTO 'logic_app'@'127.0.0.1';GRANT SELECT ON servicehub_logic_lab.work_order_auditTO 'logic_app'@'127.0.0.1';SHOW GRANTS FOR 'logic_app'@'127.0.0.1';

Connect as logic_app and test:

SELECT USER(),CURRENT_USER(),CONNECTION_ID();SHOW SESSION STATUS LIKE 'Ssl_cipher';UPDATE servicehub_logic_lab.work_ordersSET status='waiting'WHERE work_order_id=3;SELECT audit_id,work_order_id,old_status,new_status,change_source,changed_byFROM servicehub_logic_lab.work_order_auditWHERE work_order_id=3 ORDER BY audit_id DESC;-- Negative test: the app cannot create arbitrary audit records.INSERT INTO servicehub_logic_lab.work_order_audit(work_order_id,old_status,new_status,change_source,changed_by)VALUES(3,'x','y','forged',CURRENT_USER());

The update should succeed and create a trigger audit row, while the direct audit insert should be denied. This is a concrete least-privilege property: the application can cause the controlled side effect only through the allowed base-table operation.

BEFORE triggers can validate or normalize NEW values

A BEFORE trigger can inspect and, for eligible columns, assign NEW.column. Keep normalization small and unsurprising:

DROP TRIGGER IF EXISTS trg_work_orders_trim_summary;CREATE TRIGGER trg_work_orders_trim_summaryBEFORE INSERT ON work_ordersFOR EACH ROWSET NEW.summary = TRIM(NEW.summary);INSERT INTO work_orders(site_id,status,priority,summary)VALUES(2,'open',2,'   Hydraulic hose inspection   ');SELECT work_order_id,CONCAT('[',summary,']') AS stored_summaryFROM work_orders ORDER BY work_order_id DESC LIMIT 1;

A trigger that silently rewrites complex business data can be harder to reason about than explicit application validation. Use this capability for rules that truly belong at the data boundary, and document the transformation.

Multiple triggers and explicit ordering

MySQL permits multiple triggers for the same table, timing, and event. When relative order matters, declare it. Do not depend on creation order as undocumented tribal knowledge.

DROP TRIGGER IF EXISTS trg_work_orders_status_metric;DELIMITER $$CREATE TRIGGER trg_work_orders_status_metricAFTER UPDATE ON work_ordersFOR EACH ROWFOLLOWS trg_work_orders_status_auditBEGIN  IF NOT (OLD.status <=> NEW.status) THEN    INSERT INTO maintenance_runs(job_name,executed_at,execution_user,note)    VALUES('status_metric',CURRENT_TIMESTAMP(6),CURRENT_USER(),           CONCAT('work_order=',NEW.work_order_id));  END IF;END$$DELIMITER ;SELECT TRIGGER_NAME,ACTION_TIMING,EVENT_MANIPULATION,ACTION_ORDERFROM INFORMATION_SCHEMA.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub_logic_lab'  AND EVENT_OBJECT_TABLE='work_orders'ORDER BY ACTION_TIMING,EVENT_MANIPULATION,ACTION_ORDER;

FOLLOWS makes the relationship reviewable in source. If the two triggers actually require a complicated chain of ordering assumptions, consider whether one explicit routine or application operation would be easier to test.

Transaction participation: trigger side effects roll back with InnoDB work

Start a transaction, change a status, observe the audit row inside your session, then roll back.

START TRANSACTION;UPDATE work_orders SET status='open' WHERE work_order_id=2;SELECT work_order_id,status FROM work_orders WHERE work_order_id=2;SELECT audit_id,old_status,new_status,change_sourceFROM work_order_audit WHERE work_order_id=2 ORDER BY audit_id DESC LIMIT 2;ROLLBACK;SELECT work_order_id,status FROM work_orders WHERE work_order_id=2;SELECT audit_id,old_status,new_status,change_sourceFROM work_order_audit WHERE work_order_id=2 ORDER BY audit_id DESC LIMIT 2;

After rollback, both the base-row change and trigger-created audit row from that transaction should be gone. The trigger did not run as an independent background job.

Intentional failure: hidden trigger logic can break an innocent UPDATE

Create a disposable trigger that signals an error for priority 4. This illustrates error propagation without corrupting data.

DROP TRIGGER IF EXISTS trg_work_orders_demo_block;DELIMITER $$CREATE TRIGGER trg_work_orders_demo_blockBEFORE UPDATE ON work_ordersFOR EACH ROWBEGIN  IF NEW.priority = 4 THEN    SIGNAL SQLSTATE '45000'      SET MYSQL_ERRNO=32001,          MESSAGE_TEXT='Demo trigger blocks priority 4';  END IF;END$$DELIMITER ;UPDATE work_ordersSET priority=4,status='waiting'WHERE work_order_id=2;-- The statement fails; verify neither requested change became durable.SELECT work_order_id,status,priority FROM work_orders WHERE work_order_id=2;DROP TRIGGER trg_work_orders_demo_block;

This is why trigger inventories matter during incident diagnosis. The application SQL can be syntactically correct and properly authorized yet fail because a stored object added another rule.

Auditing: useful record, not complete security telemetry

A trigger-based audit table can record old/new business values for SQL row changes. It does not automatically capture every connection/authentication event, failed authorization attempt, statement text, external identity, or operational context. Enterprise audit capabilities, application telemetry, proxy logs, and server logs solve different problems. Label the trigger table as a business-change history, not a universal forensic audit trail.

Hands-on lab acceptance checklist

• You can state the OLD/NEW availability for INSERT, UPDATE, and DELETE.
• The app can update an allowed work-order column and a trigger writes the audit row without direct app INSERT privilege on the audit table.
• The app's forged direct audit insert is denied.
• INFORMATION_SCHEMA.TRIGGERS shows both trigger definitions/order metadata.
• A transaction rollback removes both base and trigger side effects.
• The intentional SIGNAL trigger causes the invoking UPDATE to fail and is then removed.

Production judgment and next step

Triggers fit narrow, table-local invariants or business-change auditing that truly must apply regardless of client. They fit poorly when they orchestrate workflows, call complex chains of logic, duplicate application behavior, or become the only place business rules are documented. Inventory triggers during migrations, review definer accounts, test multi-row behavior, and include trigger side effects in transaction/latency analysis.

Lesson 4 moves from row-driven automatic logic to time-driven logic: the MySQL Event Scheduler.

Authoritative references

• MySQL 8.4 — CREATE TRIGGER
• MySQL 8.4 — Using Triggers
• MySQL 8.4 — Trigger Metadata
• MySQL 8.4 — Stored Object Access Control
• MySQL 8.4 — Restrictions on Stored Programs