Home/Learn/MySQL/Triggers & User-Defined Functions

Triggers & User-Defined Functions

Intermediate

Advanced Queries

BEFORE/AFTER INSERT/UPDATE/DELETE triggers enforce cross-table logic; deterministic scalar UDFs encapsulate reusable computation safe for use in queries.

Overview

Triggers are stored programs that automatically execute in response to INSERT, UPDATE, or DELETE events on a table. BEFORE triggers run before the DML and can modify NEW row values (validation, default setting). AFTER triggers run after the DML and are used for audit logging, denormalisation updates, or cascade logic beyond foreign key capabilities. User-Defined Functions (UDFs) are scalar functions callable in SQL expressions — ideal for encapsulating repetitive computation (tax calculation, distance formula, string manipulation). Both triggers and UDFs are stored in the database, making them available to all applications regardless of language but also harder to test, version, and trace in distributed systems.

BEFORE and AFTER triggers

BEFORE triggers can modify NEW column values before the row is written — use for validation and default-value assignment. AFTER triggers cannot modify the row but have access to both OLD and NEW values — use for audit tables and denormalisation. Triggers cannot COMMIT/ROLLBACK transactions; they inherit the parent transaction. MySQL limits one trigger per event per table in 5.x; multiple triggers per event are supported in 8.x.

SQL — BEFORE INSERT validation trigger and AFTER triggers for audit log

-- BEFORE INSERT trigger: set created_at and validate
DELIMITER $$
CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
    IF NEW.total <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Order total must be positive';
    END IF;
END$$
DELIMITER ;

-- AFTER INSERT trigger: write audit log
DELIMITER $$
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit_log (order_id, action, new_status, changed_at)
    VALUES (NEW.id, 'CREATED', NEW.status, NOW());
END$$
DELIMITER ;

-- AFTER UPDATE trigger: audit status changes
DELIMITER $$
CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status THEN
        INSERT INTO order_audit_log (order_id, action, old_status, new_status, changed_at)
        VALUES (NEW.id, 'STATUS_CHANGE', OLD.status, NEW.status, NOW());
    END IF;
END$$
DELIMITER ;

User-Defined Functions (UDFs)

Scalar UDFs return a single value and can be used anywhere an expression is valid (SELECT list, WHERE, ORDER BY). Mark as DETERMINISTIC when the same inputs always produce the same output — this enables the optimiser to cache results and use the function in indexes (generated column). READS SQL DATA and MODIFIES SQL DATA declare the function's data access level.

SQL — deterministic scalar UDF for distance calculation

-- Deterministic UDF: calculate distance in km between two GPS coordinates
DELIMITER $$
CREATE FUNCTION haversine_km(
    lat1 DECIMAL(9,6), lon1 DECIMAL(9,6),
    lat2 DECIMAL(9,6), lon2 DECIMAL(9,6)
)
RETURNS DECIMAL(10,3)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE R DOUBLE DEFAULT 6371;
    DECLARE dLat DOUBLE;
    DECLARE dLon DOUBLE;
    DECLARE a DOUBLE;
    SET dLat = RADIANS(lat2 - lat1);
    SET dLon = RADIANS(lon2 - lon1);
    SET a = SIN(dLat/2) * SIN(dLat/2) +
            COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
            SIN(dLon/2) * SIN(dLon/2);
    RETURN R * 2 * ATAN2(SQRT(a), SQRT(1-a));
END$$
DELIMITER ;

-- Use in query
SELECT store_name, haversine_km(51.5, -0.1, lat, lon) AS distance_km
FROM stores
WHERE haversine_km(51.5, -0.1, lat, lon) < 10
ORDER BY distance_km;

-- Drop function
DROP FUNCTION IF EXISTS haversine_km;

-- List existing functions
SELECT ROUTINE_NAME, ROUTINE_TYPE, IS_DETERMINISTIC
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'shop' AND ROUTINE_TYPE = 'FUNCTION';

Trigger pitfalls and alternatives

Triggers execute inside the transaction of the DML — a trigger error rolls back the entire INSERT/UPDATE/DELETE. They are invisible to application code, making debugging harder. In high-write environments, triggers add per-row overhead. For most use cases in microservices, application-level logic (service layer or outbox pattern) is preferred over triggers for maintainability and testability.

SQL — trigger inspection, skip pattern, and when to prefer application-level logic

-- Show all triggers on a table
SHOW TRIGGERS FROM shop LIKE 'orders';
-- or
SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING, ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_SCHEMA = 'shop' AND EVENT_OBJECT_TABLE = 'orders';

-- Disable a trigger temporarily (MySQL 8.0.26+)
-- No direct DISABLE TRIGGER — workaround: DROP and recreate, or use a flag
-- Alternative: use a session variable to skip trigger logic
-- In trigger body:
--   IF @skip_audit_trigger IS NULL OR @skip_audit_trigger = 0 THEN
--     (audit logic)
--   END IF;
-- In session:
SET @skip_audit_trigger = 1;
INSERT INTO orders ...;
SET @skip_audit_trigger = 0;

-- PREFER application-level alternatives:
-- ✓ Hibernate @EntityListeners for audit (no DB coupling)
-- ✓ Outbox pattern for reliable event emission (vs AFTER INSERT trigger)
-- ✓ Service layer validation (vs BEFORE INSERT trigger with SIGNAL)
-- Triggers are still useful for: legacy DB shared by multiple apps,
-- enforcing constraints that cannot be expressed as FK/check constraints

Key Points to Remember

1BEFORE triggers can modify NEW row values (validation, defaults); AFTER triggers cannot modify the row but can audit
2Trigger errors roll back the parent DML transaction — never let a trigger silently fail in a AFTER trigger
3DETERMINISTIC UDFs tell the optimiser inputs always produce the same output — required for use in generated column indexes
4Triggers are invisible to application code — document them in schema migrations and test them explicitly
5In microservices, prefer Hibernate @EntityListeners or application-layer logic over triggers for testability
6SHOW TRIGGERS and information_schema.TRIGGERS are the primary tools for discovering existing trigger definitions

Interview Questions

What is the difference between a BEFORE and AFTER trigger and when would you use each?

EasyOracle

Can a trigger issue a COMMIT or ROLLBACK? What happens if a trigger throws an error?

MediumThoughtworks

What does DETERMINISTIC mean on a MySQL function and why does it matter for performance?

MediumAmazon

Why are triggers generally discouraged in microservice architectures?

MediumBooking.com

How would you implement audit logging for an orders table — trigger vs application-layer?

HardNetflix

Ask Aria about Triggers & User-Defined Functions

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Stored Procedures

Views