Example Triggers for MySQL: Update Stock Quantity, Log Changes, and Prevent Deletion

Ravindu Weerasooriya -

A MySQL trigger is a cleverly programmed sequence that responds automatically to an event. Typically, this event is a change in data (such as insertion, update, or deletion) for a given table.

The purpose of triggers is to ensure that the data in the table adheres to business rules and data integrity requirements, which involves performing complex calculations, manipulating data, and logging events for auditing purposes.

Each trigger is linked to a specific table and activates when the specified event occurs.
For example, a trigger could execute before a new row is inserted into a table, after an existing row is updated, or when a row is deleted from a table.

While triggers are similar to stored procedures in that they contain SQL statements and control structures, they are automatically executed, unlike stored procedures, which require explicit invocation. Furthermore, triggers can access data from other tables and modify data using

  • INSERT
  • UPDATE
  • DELETE
    statements.

To create a trigger in MySQL, you must use the CREATE TRIGGER statement, which specifies the name of the trigger, the event that activates the trigger, the table that the trigger is associated with, and the trigger action. The trigger action is the stored program that executes automatically when the trigger is activated.

Here is an example of how to create a simple trigger in MySQL:

Example 1 - Update Stock Quantity:

Suppose we have an orders table and a products table. Whenever a new order is inserted into the orders table, we want to update the stock_quantity in the products table by subtracting the quantity ordered from the current stock quantity of the corresponding product.

Here's the SQL code to create a trigger that does this:

CREATE TRIGGER update_stock_quantity
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE products
  SET stock_quantity = stock_quantity - NEW.quantity
  WHERE id = NEW.product_id;
END;

In this example, the update_stock_quantity trigger is created with the CREATE TRIGGER statement. It is set to execute BEFORE INSERT on the orders table, and FOR EACH ROW that is inserted. The UPDATE statement within the trigger subtracts the NEW.quantity ordered from the stock_quantity of the corresponding product in the products table, based on the NEW.product_id value.

Example 2 - Log Changes:

Suppose we have a products table, and we want to log all changes made to this table in a separate audit_log table. For each change made to the products table, we want to record the product_id, old_price, new_price, and updated_at time.

Here's the SQL code to create a trigger that does this:

CREATE TRIGGER log_changes
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (product_id, old_price, new_price, updated_at)
  VALUES (OLD.id, OLD.price, NEW.price, NOW());
END;

In this example, the log_changes trigger is created with the CREATE TRIGGER statement. It is set to execute AFTER UPDATE on the products table, and FOR EACH ROW that is updated. The INSERT INTO statement within the trigger inserts a new row into the audit_log table, with the OLD and NEW values of the id and price columns from the products table, along with the current time obtained from the NOW() function.

Example 3 - Prevent Deletion:

Suppose we have a products table and an orders table. We want to prevent the deletion of any row from the products table if it is referenced by a foreign key in the orders table.

Here's the SQL code to create a trigger that does this:

CREATE TRIGGER prevent_deletion
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
  DECLARE referenced INT DEFAULT 0;
  SELECT COUNT(*) INTO referenced FROM orders WHERE product_id = OLD.id;
  IF referenced > 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot delete product with existing orders';
  END IF;
END;

In this example, the prevent_deletion trigger is created with the CREATE TRIGGER statement. It is set to execute BEFORE DELETE on the products table, and FOR EACH ROW that is deleted. The trigger first declares a variable referenced with a default value of 0. It then selects the count of rows in the orders table where the product_id matches the OLD.id value (the row being deleted). If the referenced value is greater than 0, meaning there are orders that reference the product being deleted, then the trigger raises an error with the

These are just a few examples of the types of triggers you can create in MySQL. You can use triggers for many different purposes, such as enforcing data integrity, performing complex calculations, and logging events. For more information and examples, see the MySQL documentation.