Introduction

If a MySQL trigger detects an invalid change, it can stop the update by raising an error before the row is written. The standard way to do that in modern MySQL is to use a BEFORE UPDATE trigger and SIGNAL SQLSTATE '45000' with a clear message.

Why the Trigger Must Be BEFORE UPDATE

If the goal is to prevent the row change entirely, the check has to happen before MySQL commits the new values to the table. That is why the trigger belongs in BEFORE UPDATE, not AFTER UPDATE.

Inside the trigger:

• 'OLD.column_name refers to the current persisted value'
• 'NEW.column_name refers to the incoming value being proposed'

That lets you compare old and new states and reject illegal transitions.

Basic Example

Suppose a product price must never become negative:

1CREATE TABLE products (
2    id INT PRIMARY KEY,
3    name VARCHAR(100) NOT NULL,
4    price DECIMAL(10,2) NOT NULL
5);
6
7INSERT INTO products (id, name, price)
8VALUES (1, 'Keyboard', 49.99);

Now create a trigger that blocks invalid updates:

1DELIMITER //
2
3CREATE TRIGGER products_before_update
4BEFORE UPDATE ON products
5FOR EACH ROW
6BEGIN
7    IF NEW.price < 0 THEN
8        SIGNAL SQLSTATE '45000'
9            SET MESSAGE_TEXT = 'Price cannot be negative';
10    END IF;
11END//
12
13DELIMITER ;

Now this update fails:

UPDATE products
SET price = -5
WHERE id = 1;

MySQL raises the user-defined error and the row is not updated.

Why SIGNAL SQLSTATE '45000' Is the Right Tool

45000 is the SQLSTATE used for user-defined exceptions. It exists specifically so your trigger logic can raise a controlled application-level error instead of relying on weird side effects or invalid SQL to fail accidentally.

That makes the intent explicit:

• the failure is deliberate
• the message can be meaningful
• application code can react predictably

Older workaround patterns sometimes tried to force an error indirectly. SIGNAL is much clearer and easier to maintain.

Cross-Column Validation Example

Triggers become especially useful when the rule depends on several columns. For example, suppose an order cannot be marked as shipped unless a tracking code exists:

1CREATE TABLE orders (
2    id INT PRIMARY KEY,
3    status VARCHAR(20) NOT NULL,
4    tracking_code VARCHAR(50) NULL
5);

1DELIMITER //
2
3CREATE TRIGGER orders_before_update
4BEFORE UPDATE ON orders
5FOR EACH ROW
6BEGIN
7    IF NEW.status = 'shipped'
8       AND (NEW.tracking_code IS NULL OR NEW.tracking_code = '') THEN
9        SIGNAL SQLSTATE '45000'
10            SET MESSAGE_TEXT = 'Tracking code is required before shipping';
11    END IF;
12END//
13
14DELIMITER ;

That kind of validation is a good trigger use case because it protects the table no matter which client application performs the update.

Transaction Behavior

When the trigger signals an error, the current statement fails. If the update is part of a larger transaction, the client code has to decide what to do next:

• roll back the transaction
• correct the data and retry
• surface the validation error to the user

So a trigger-based validation rule is not just a warning. It is a hard failure that the application must be prepared to handle.

When Triggers Are a Good Fit

Use trigger-based rejection when the rule is a true data invariant that should hold for every client:

• no negative inventory
• no illegal status transition
• no modification after a record is locked

This is stronger than putting the check only in application code, because it protects the table from every code path.

When Triggers Are a Bad Fit

Triggers are less suitable when the logic:

• depends on external services
• changes frequently at the product-policy level
• is so complex that debugging it inside the database becomes painful

In those cases, you may still want application-level validation first, even if the database also enforces a smaller core invariant.

Common Pitfalls

The most common mistake is using AFTER UPDATE while expecting the trigger to prevent the change. By then the row has already been updated.

Another common mistake is confusing OLD and NEW. The trigger needs NEW for the incoming values and often OLD when validating state transitions.

A third pitfall is writing overly complex trigger logic that becomes hard to test and debug. Keep the rule focused and data-centric.

Finally, remember that the application has to handle the error. A trigger that signals a rejection is only half the solution if the client turns that failure into a cryptic crash.

Summary

• Use a BEFORE UPDATE trigger when you need to stop invalid row changes
• Raise the failure explicitly with SIGNAL SQLSTATE '45000'
• Compare OLD and NEW values to enforce state-transition rules
• Triggers are best for data invariants that should hold across every client application
• Keep trigger logic clear and ensure the calling application handles the resulting error properly