Triggers, Handlers, Signaling and Audit Rules

1. Triggers

A trigger is a stored program that the DBMS activates automatically when a declared data-change event occurs on its table. MySQL table triggers respond to INSERT, UPDATE or DELETE and run BEFORE or AFTER each affected row.

Unlike a procedure, a trigger is not invoked with CALL. Its execution is an implicit part of the statement that caused the event.

Triggers can enforce specialized rules, normalize incoming values, maintain tightly related derived data and record audits. Their implicit nature also makes overuse difficult to understand and debug.

2. Trigger Definition

DELIMITER //

CREATE TRIGGER bi_product_validate
BEFORE INSERT ON product
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Price must be nonnegative';
    END IF;
END//

DELIMITER ;

The name identifies the trigger. BEFORE INSERT defines timing and event. FOR EACH ROW means a multirow INSERT activates it separately for every affected row.

3. BEFORE Triggers

A BEFORE trigger runs before the row change is applied. It can validate proposed data and, for permitted events and columns, assign NEW values:

CREATE TRIGGER bi_customer_normalize
BEFORE INSERT ON customer
FOR EACH ROW
SET NEW.email = LOWER(TRIM(NEW.email));

Use such normalization only when it represents a universal database rule. Hidden modification can surprise clients that expect the exact supplied value to be stored.

Prefer column defaults, generated columns and CHECK constraints when they express the rule directly.

4. AFTER Triggers

An AFTER trigger runs after the row change has succeeded within the current statement:

CREATE TRIGGER au_product_audit
AFTER UPDATE ON product
FOR EACH ROW
INSERT INTO product_audit(
    product_id,
    old_price,
    new_price,
    changed_at,
    changed_by
)
VALUES(
    NEW.product_id,
    OLD.price,
    NEW.price,
    CURRENT_TIMESTAMP,
    CURRENT_USER()
);

The audit insertion remains part of the same transaction for transactional tables. A later rollback removes both the original update and its audit row.

5. OLD and NEW Row Images

Trigger row references depend on event:

Event	OLD	NEW
INSERT	unavailable	proposed inserted row
UPDATE	row before change	row after change
DELETE	deleted row	unavailable

OLD values are read-only. NEW can be assigned in an appropriate BEFORE trigger, subject to MySQL restrictions. In an AFTER trigger, the change has already occurred and NEW is used for observation.

Qualify every row value with OLD or NEW so its time perspective is unmistakable.

6. Multirow Statements

MySQL triggers are row-level. One UPDATE affecting 10,000 rows activates its matching trigger 10,000 times.

Trigger logic must therefore be efficient and correct for every row. Per-row queries can turn one set-based statement into thousands of hidden operations and increase locking, logging and deadlock risk.

Do not assume row processing order unless the database explicitly provides a relevant guarantee; business correctness should not depend on it.

7. Events That Do Not Fire a Trigger

An ordinary SELECT does not fire INSERT, UPDATE or DELETE triggers. TRUNCATE TABLE is DDL-like and does not activate row DELETE triggers as a DELETE statement would.

Referential cascades and other internal actions have product-specific trigger behavior and should be verified rather than inferred.

If auditing must cover administrative DDL, reads or every possible privileged access path, table DML triggers alone are insufficient.

8. Constraints Before Triggers

Use declarative constraints for rules they can express:

price DECIMAL(12, 2) NOT NULL
    CHECK (price >= 0)

Constraints are visible in metadata, understood by tools, uniformly enforced and often easier to optimize and maintain.

Triggers are appropriate for rules involving change history, coordinated side effects inside the database or logic unavailable in a constraint. Do not reimplement primary keys, foreign keys, uniqueness or simple checks in triggers.

9. Audit Tables

A useful audit record can include:

• target entity identifier;
• operation type;
• old and new relevant values;
• database account;
• application actor identifier supplied through a controlled context;
• transaction or request identifier;
• timestamp;
• reason or source.

Store only what is needed for accountability and investigation. Audit tables can contain sensitive history and need their own access control, retention, indexing and backup policies.

CURRENT_USER may identify the routine definer or authenticated database context differently from an application end user. Application identity must be propagated securely.

10. Audit Integrity and Transactions

An audit row written by a trigger to a transactional table rolls back with the business change. This keeps the audit consistent with committed state.

It does not record attempted changes that failed or were rolled back. Capturing attempts requires logging outside the rolled-back transaction, application audit or database/server auditing facilities.

Writing audit data to a nontransactional table to survive rollback can break atomic behavior and create inconsistent recovery. Choose audit semantics explicitly.

11. Derived Data

A trigger can maintain a stored total or summary, but concurrency and every write path must be considered. Updating an order total after each line change may work only if INSERT, UPDATE, DELETE, bulk loads and correction processes all activate complete logic.

Derived data adds write contention and the possibility of drift. Recompute from source rows when cost permits. If materialization is necessary, define one authoritative source and a repair process.

Generated columns or scheduled summary refreshes may be clearer alternatives.

12. Trigger Limitations

MySQL restricts operations a trigger can perform, including problematic modification of the table already being changed. Triggers cannot control transactions with COMMIT or ROLLBACK.

Trigger chains across related tables can create recursion-like cycles, lock-order problems and diagnostics far from the initiating statement.

Keep trigger logic small, deterministic where practical and documented beside the table contract.

13. Condition Handling

Stored procedures, functions and triggers can declare handlers for conditions:

DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_done = TRUE;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

A handler definition states the action and condition category. Scope begins in its declaring block after declaration rules are satisfied.

14. CONTINUE Handlers

A CONTINUE handler performs its body and resumes control according to MySQL block semantics after the statement that raised the condition.

It is suitable for expected conditions such as cursor exhaustion:

DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_done = TRUE;

The following code must inspect the flag before using fetch variables.

Do not continue after an error unless subsequent state is well defined. Ignoring a duplicate key or arithmetic exception can create misleading partial results.

15. EXIT Handlers

An EXIT handler runs and then leaves the block in which it was declared, even when the error arose in a nested block.

It is suitable for failure cleanup:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

Transaction ownership must still be correct. A low-level routine should not roll back a transaction it does not own unless its contract states that behavior.

16. Handler Conditions

Handlers can target:

• a specific numeric MySQL error code;
• a specific SQLSTATE value;
• a named condition;
• SQLWARNING, SQLSTATE class 01;
• NOT FOUND, SQLSTATE class 02;
• SQLEXCEPTION, other exception classes.

Specific conditions generally take precedence according to MySQL rules. A named condition gives domain meaning:

DECLARE duplicate_customer CONDITION
    FOR SQLSTATE '23000';

Broad handlers should not hide precise failures that need different treatment.

17. SQLSTATE

SQLSTATE is a five-character standard condition code. Its first two characters identify a class. Class 00 means successful completion, 01 indicates warnings, 02 indicates no data and other classes represent exceptions.

MySQL error numbers provide vendor detail, while SQLSTATE offers portable categorization. Client contracts can expose a stable domain error instead of depending on message text.

Messages help people but are poor programmatic identifiers because wording may change.

18. SIGNAL

SIGNAL raises an explicit condition:

IF p_quantity <= 0 THEN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Quantity must be positive',
            MYSQL_ERRNO = 30001;
END IF;

SQLSTATE 45000 is commonly used for an unhandled user-defined exception. A clear message and documented application error mapping make the failure actionable.

SIGNAL stops normal execution unless an applicable handler catches it.

19. RESIGNAL

RESIGNAL propagates the current handled condition:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

Rollback restores transactional state, while RESIGNAL informs the caller that the operation failed. Without propagation, a client may report success.

RESIGNAL can preserve the condition or change selected diagnostic attributes to add abstraction-level context. Do not destroy useful underlying information without a stable replacement.

20. Diagnostics

GET DIAGNOSTICS retrieves condition information where supported:

GET DIAGNOSTICS CONDITION 1
    v_state = RETURNED_SQLSTATE,
    v_message = MESSAGE_TEXT;

A handler can log or translate structured information. Diagnostic logging must avoid leaking secrets, personal data or full dynamic statements containing credentials.

Logging inside the same transaction rolls back with it. Decide whether diagnostics need application logging or server facilities outside that transaction.

21. Handler Scope

Handlers belong to blocks. A nested block can handle a condition locally while an outer handler covers the larger operation.

This structure is useful when one optional lookup may be absent but any modification error must abort:

BEGIN
    BEGIN
        DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET v_label = NULL;
        SELECT label INTO v_label
        FROM lookup
        WHERE lookup_id = p_lookup_id;
    END;

    -- other operations use v_label
END;

Narrow scope prevents a NOT FOUND handler intended for one statement from accidentally catching cursor exhaustion elsewhere.

22. Trigger Security

Triggers execute under a defined security context and can access data the invoking account may not access directly. This supports central rules but increases review requirements.

Control who can create or alter triggers. Record definitions in migrations, review their definer accounts and avoid dynamic SQL or untrusted context where applicable.

Privileges on base statements do not make hidden trigger work harmless. A user permitted to update one table may indirectly cause changes to an audit or summary table through its trigger.

23. Testing Triggers and Handlers

Test each event and timing with:

• valid and invalid single-row changes;
• multirow statements;
• NULL transitions;
• unchanged values in UPDATE;
• foreign-key cascades as applicable;
• statement failure midway through multiple rows;
• explicit rollback;
• concurrent changes and lock conflicts;
• missing privileges and changed definer accounts.

Verify both business tables and audit or derived tables after commit and rollback.

24. Trigger Design Method

First ask whether a constraint, generated column, procedure or application transaction expresses the rule more visibly. If a trigger remains appropriate, define its event, timing, row images, transaction behavior and performance at multirow scale.

Keep the body focused. Signal invalid state with a documented condition. Use handlers only when the routine can truly recover or must clean up before propagating failure.

Triggers and handlers are reliable when their implicit effects are small, discoverable, transactional and tested. They become dangerous when they hide broad workflows or convert failures into silent success.