What are the Triggers in MySQL and Explained with code

Triggers in MySQL

A MySQL trigger is a stored procedure that “fires” when a table event occurs. Triggers automate data modification checks to ensure database data integrity. They monitor the database and execute a preset operation when an update fits an event definition like “daemons”.

A trigger description usually has three parts:

Event: A database change (INSERT, UPDATE, DELETE) that initiates the trigger.
Condition: An optional trigger-activated query or test.
Action: The process carried out when the trigger is triggered and its condition (if any) is met.

Instead of being specifically invoked by a user, triggers are “attached” to a database. They might be planned to run either before or after the SQL statement that activates the system. Up to six triggers are allowed per table in MySQL.

Create a Trigger

Create a trigger with CREATE TRIGGER. You must give the trigger’s name, activation time (BEFORE or AFTER), event (INSERT, UPDATE, or DELETE), table, and SQL statements in the BEGIN…END block.

Command-line clients often use DELIMITER before generating triggers or stored procedures. Because semicolons commonly conclude statements, the client perceives these blocks as such. THE DELIMITER momentarily changes the statement terminator to $$ to parse the entire block as a single unit, then resets it to a semicolon.

Here’s the generic structure:

DELIMITER $$
CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger code (SQL statements)
END$$
DELIMITER ;

Inside the trigger code, you can refer to the old and new values of the row that caused the trigger to fire using the OLD and NEW keywords.

NEW.column_name: Refers to the value of a column after the INSERT or UPDATE operation.
OLD.column_name: Refers to the value of a column before the UPDATE or DELETE operation.

Before Insert Trigger

A BEFORE INSERT trigger activates before a new row is inserted into the table. This is useful for validating data before it’s saved or modifying incoming data.

Example: Set creation_date to current timestamp before insert Let’s assume you have a table products with id, name, and creation_date columns.

DELIMITER $$
CREATE TRIGGER tr_set_product_creation_date
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    SET NEW.creation_date = NOW();
END$$
DELIMITER ;
-- Test the trigger
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    creation_date DATETIME
);
INSERT INTO products (name) VALUES ('Laptop');
SELECT * FROM products;

In this example, NEW.creation_date refers to the creation_date column of the row being inserted. The trigger sets its value to the current timestamp.

After Insert Trigger

BEFORE INSERT triggers before placing a new row in the table. This helps validate data before saving or editing incoming data.

Make creation_date current before inserting. Imagine a products table containing id, name, and creation_date columns.

DELIMITER $$
CREATE TRIGGER tr_log_product_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (operation, table_name, record_id, changed_at)
    VALUES ('INSERT', 'products', NEW.id, NOW());
END$$
DELIMITER ;
-- Test the trigger
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation VARCHAR(10),
    table_name VARCHAR(50),
    record_id INT,
    changed_at DATETIME
);
INSERT INTO products (name) VALUES ('Mouse');
SELECT * FROM products;
SELECT * FROM audit_log;

Here, NEW.id is the newly inserted row’s id, which becomes accessible following the completion of the insert process.

Before Update Trigger

Before altering an existing row, a BEFORE UPDATE trigger is triggered. This can be used for auditing, validation, or generating new values depending on previous ones.

For instance, update last_modified_date and make sure the price doesn’t fall below zero. Assume that the products table has a last_modified_date and price column as well.

DELIMITER $$
CREATE TRIGGER tr_before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    SET NEW.last_modified_date = NOW();
    IF NEW.price < 0 THEN
        SET NEW.price = 0; -- Prevent negative prices
    END IF;
END$$
DELIMITER ;
-- Test the trigger
ALTER TABLE products
ADD COLUMN price DECIMAL(10,2) DEFAULT 0.00,
ADD COLUMN last_modified_date DATETIME;
INSERT INTO products (name, price) VALUES ('Keyboard', 100.00);
UPDATE products SET price = -50.00 WHERE name = 'Keyboard'; -- Price will be set to 0
UPDATE products SET price = 120.00 WHERE name = 'Keyboard'; -- Price will be set to 120, last_modified_date updated
SELECT * FROM products;

This trigger will set the column to the values NEW.price and NEW.last_modified_date.

After Update Trigger

Following the successful modification of an existing row, an AFTER UPDATE trigger is triggered. This is helpful when recording changes, particularly when comparing the old and new numbers.

Example: Log price changes to an audit table

DELIMITER $$
CREATE TRIGGER tr_log_product_price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO audit_log (operation, table_name, record_id, old_value, new_value, changed_at)
        VALUES ('UPDATE_PRICE', 'products', OLD.id, OLD.price, NEW.price, NOW());
    END IF;
END$$
DELIMITER ;
-- Test the trigger
ALTER TABLE audit_log
ADD COLUMN old_value DECIMAL(10,2) NULL,
ADD COLUMN new_value DECIMAL(10,2) NULL;
UPDATE products SET price = 150.00 WHERE name = 'Keyboard';
SELECT * FROM products;
SELECT * FROM audit_log;

In this case, OLD.price represents the value prior to the update, and NEW.price represents the value subsequent to the update.

Before Delete Trigger

Before deleting a row, a BEFORE DELETE trigger is executed. This can be used for pre-deletion checks or to stop deletion in specific situations.

For instance, avoid deleting products that have ongoing sales (conceptual) This example is predicated on the existence of a sales table and merchandise.Sales.product_id refers to the id.

DELIMITER $$
CREATE TRIGGER tr_prevent_product_deletion
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
    -- This is a conceptual example. Actual prevention might involve SIGNAL SQLSTATE or raising an error.
    -- For simplicity, we'll just show the condition.
    -- If product is in an active sale, do not allow deletion.
    -- SELECT COUNT(*) INTO @active_sales_count FROM sales WHERE product_id = OLD.id AND status = 'active';
    -- IF @active_sales_count > 0 THEN
    --     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete product with active sales.';
    -- END IF;
END$$
DELIMITER ;
-- Test the trigger (conceptual, as the prevention part is commented out)
-- DELETE FROM products WHERE name = 'Laptop';
-- SELECT * FROM products;

Just OLD.column_name, which represents the row that is going to be deleted, is available in a BEFORE DELETE trigger.

After Delete Trigger

After a row has been successfully deleted, an AFTER DELETE trigger is triggered. This is frequently employed to preserve historical data or to archive deleted records.

Deleted employees can be logged into a deleted_employees table. The data provide clear support for this example.

DELIMITER $$
CREATE TABLE deleted_employees(
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    middle_name VARCHAR(20),
    job_title VARCHAR(50),
    department_id INT,
    salary DOUBLE
);
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER tr_deleted_employees
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO deleted_employees
    (first_name,last_name,middle_name,job_title,department_id,salary)
    VALUES(OLD.first_name,OLD.last_name,OLD.middle_name,OLD.job_title,OLD.department_id,OLD.salary);
END$$
DELIMITER ;
-- Test the trigger
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    middle_name VARCHAR(20),
    job_title VARCHAR(50),
    department_id INT,
    salary DOUBLE
);
INSERT INTO employees (first_name, last_name, job_title, department_id, salary)
VALUES ('Guy', 'Gilbert', 'Manager', 101, 75000.00);
DELETE FROM employees WHERE employee_id = 1;
SELECT * FROM employees;
SELECT * FROM deleted_employees;

Only the values of the recently deleted row are represented by OLD.column_name, which is usable in an AFTER DELETE trigger.

Show Trigger

MySQL doesn’t have a SHOW TRIGGER command like SHOW TABLES or SHOW DATABASES. Nonetheless, the capability to “view” triggers is suggested by using programs such as phpMyAdmin or by deducing from SHOW EVENTS for scheduled events. The majority of MySQL interfaces and tools provide trigger viewing. To view triggers programmatically, query the INFORMATION_SCHEMA.TRIGGERS table.

Drop Trigger

Remove a trigger using DROP TRIGGER. The trigger name and, if not selected, the database name must be specified.

DROP TRIGGER IF EXISTS trigger_name;

For example, to drop the tr_deleted_employees trigger created earlier:

DROP TRIGGER IF EXISTS tr_deleted_employees;

Any triggers connected to a table are immediately dropped when the table is dropped.

Page Content

Tutorials