> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL Triggers

> How MySQL triggers work: CREATE TRIGGER syntax, BEFORE vs AFTER, NEW and OLD row references, multiple triggers per event, and when triggers help vs hurt maintainability.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

A trigger is SQL that runs automatically when a row is inserted, updated, or deleted. MySQL fires triggers before or after the data change, giving you access to the row values being written.

## Creating a Trigger

```sql theme={null}
CREATE TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON table_name
    FOR EACH ROW
BEGIN
    -- trigger body
END;
```

A practical example — log every price change:

```sql theme={null}
CREATE TABLE price_history (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    product_id  INT,
    old_price   DECIMAL(10,2),
    new_price   DECIMAL(10,2),
    changed_at  DATETIME DEFAULT NOW()
);

DELIMITER $$

CREATE TRIGGER log_price_change
    AFTER UPDATE ON products
    FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price)
        VALUES (NEW.id, OLD.price, NEW.price);
    END IF;
END$$

DELIMITER ;
```

Drop a trigger:

```sql theme={null}
DROP TRIGGER IF EXISTS log_price_change;
```

List all triggers in the current database:

```sql theme={null}
SHOW TRIGGERS\G
-- or
SELECT trigger_name, event_manipulation, event_object_table, action_timing
FROM information_schema.triggers
WHERE trigger_schema = DATABASE();
```

## BEFORE vs AFTER

| | BEFORE trigger | AFTER trigger |
| :- | :- | :- |
| Runs | Before the row is written | After the row is written |
| Can modify `NEW` values | Yes — changes apply to the row | No — row already written |
| Can see `OLD` values | Yes (UPDATE/DELETE) | Yes (UPDATE/DELETE) |
| Use for | Validation, defaulting values, data normalization | Audit logging, cascading to other tables, notifications |

A `BEFORE INSERT` trigger can set default values or reject bad data. An `AFTER INSERT` trigger can write audit records after the insert succeeds.

## NEW and OLD References

| Trigger type | `NEW` | `OLD` |
| :- | :- | :- |
| `INSERT` | New row values | Not available |
| `UPDATE` | New values after update | Original values before update |
| `DELETE` | Not available | Values of the deleted row |

Modify `NEW` values in a `BEFORE` trigger to change what gets written:

```sql theme={null}
CREATE TRIGGER normalize_email
    BEFORE INSERT ON users
    FOR EACH ROW
BEGIN
    SET NEW.email = LOWER(TRIM(NEW.email));
END;
```

## Multiple Triggers Per Event

MySQL supports multiple triggers on the same table and event. The `FOLLOWS` and `PRECEDES` keywords control execution order:

```sql theme={null}
CREATE TRIGGER audit_insert
    AFTER INSERT ON orders
    FOR EACH ROW
    FOLLOWS another_trigger
BEGIN
    INSERT INTO audit_log (action, table_name, row_id, happened_at)
    VALUES ('INSERT', 'orders', NEW.id, NOW());
END;
```

Without `FOLLOWS`/`PRECEDES`, MySQL assigns execution order based on creation time.

## Raising Errors in Triggers

Use `SIGNAL` to abort the operation and return an error to the caller:

```sql theme={null}
CREATE TRIGGER enforce_minimum_price
    BEFORE INSERT ON products
    FOR EACH ROW
BEGIN
    IF NEW.price < 0.01 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Price must be at least $0.01';
    END IF;
END;
```

When a `BEFORE` trigger signals an error, the insert/update/delete is aborted — nothing is written.

## Trigger Limitations

MySQL triggers have several restrictions worth knowing:

* Triggers cannot call stored procedures that return result sets
* Triggers cannot use `COMMIT`, `ROLLBACK`, or `SAVEPOINT` directly (they participate in the surrounding transaction)
* Triggers cannot reference the table they're defined on (no recursive trigger calls via direct queries on the same table in most contexts)
* `AFTER` triggers cannot modify `NEW` values — the row is already written
* MySQL does not support statement-level triggers (every trigger is `FOR EACH ROW`)

## When to Use Triggers

Triggers work well for:

* Audit logging — capturing who changed what and when, regardless of application
* Enforcing complex constraints that CHECK constraints can't express
* Keeping derived data in sync (denormalized summary columns, audit timestamps)

Avoid triggers when:

* The logic is complex enough that debugging it requires tracing hidden side effects
* Performance is critical — every trigger fires per row, on every affected statement
* The trigger modifies data in a way that surprises application developers unaware of its existence

Hidden triggers are a common source of "why did this row change?" confusion. If a trigger exists, document it prominently at the application level, not just in the database.

## Frequently Asked Questions

#### Do triggers fire during LOAD DATA INFILE?

Yes. `INSERT` triggers fire for each row loaded by `LOAD DATA INFILE`. If your trigger does significant work, bulk-loading through LOAD DATA will be much slower than loading into a staging table first, then copying with INSERT...SELECT (which also fires triggers, but you can temporarily disable them with `SET @disable_trigger = TRUE` and a conditional check inside the trigger).

#### Can triggers call stored procedures?

Yes, with one exception: the called procedure cannot return a result set (a `SELECT` without `INTO`). If it does, MySQL returns an error. Procedures called from triggers can use `OUT` parameters and `SELECT...INTO`.

#### Do triggers fire on REPLACE INTO?

`REPLACE` executes as a DELETE + INSERT when there's a conflict. On conflict all four fire on the target table, in this order: `BEFORE INSERT` (the duplicate-key check), then `BEFORE DELETE` and `AFTER DELETE` for the conflicting row, then `AFTER INSERT`. Because the delete is a real row delete, `ON DELETE CASCADE` rules on referencing tables DO fire.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| Trigger not firing | Confirm the trigger exists: `SHOW TRIGGERS LIKE 'table_name'` — also verify the event (INSERT/UPDATE/DELETE) and timing (BEFORE/AFTER) match what you expect |
| `ERROR 1442: Can't update table in stored function/trigger` | A trigger can't directly INSERT/UPDATE/DELETE the same table it's defined on in most contexts — write to a different table or use a stored procedure called after the DML |
| AFTER trigger changes not persisting | `AFTER` triggers cannot modify `NEW` — switch to `BEFORE` if you need to alter the row being written |
| Trigger slowing down bulk insert | Triggers fire per row — for bulk loads, consider disabling the trigger temporarily or loading into a staging table |
| `SIGNAL` in trigger not surfacing to app | Some drivers swallow errors from triggers — check `SHOW WARNINGS` and verify your driver's error handling configuration |

## See also

* [Stored Procedures in MySQL](https://villagesql.com/docs/en/guides/stored-procedures.md) — logic that triggers can call
* [Sending Webhooks from Triggers](https://villagesql.com/docs/en/guides/http-webhooks.md) — HTTP calls from trigger bodies
* [CHECK Constraints in MySQL](https://villagesql.com/docs/en/guides/check-constraints.md) — a simpler alternative for validation rules that triggers often implement


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.