MySQL BEFORE INSERT Trigger

Summary: in this tutorial, you will learn how to create a MySQL BEFORE INSERT trigger to maintain a summary table of another table.

Introduction to MySQL BEFORE INSERT triggers

MySQL BEFORE INSERT triggers are automatically fired before an insert event occurs on the table.

The following illustrates the basic syntax of creating a MySQL BEFORE INSERT trigger:

CREATE TRIGGER trigger_name
    BEFORE INSERT
    ON table_name FOR EACH ROW
trigger_body;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the name of the trigger that you want to create in the CREATE TRIGGER clause.

Second, use BEFORE INSERT clause to specify the time to invoke the trigger.

Third, specify the name of the table that the trigger is associated with after the ON keyword.

Finally, specify the trigger body which contains one or more SQL statements that execute when the trigger is invoked.

If you have multiple statements in the trigger_body, you have to use the BEGIN END block and change the default delimiter:

DELIMITER $$

CREATE TRIGGER trigger_name
    BEFORE INSERT
    ON table_name FOR EACH ROW
BEGIN
    -- statements
END$$    

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

Note that in a BEFORE INSERT trigger, you can access and change the NEW values. However, you cannot access the OLD values because OLD values obviously do not exist.

MySQL BEFORE INSERT trigger example

We will create a BEFORE INSERT trigger to maintain a summary table from another table.

Setting up a sample table

First, create a new table called WorkCenters:

DROP TABLE IF EXISTS WorkCenters;

CREATE TABLE WorkCenters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    capacity INT NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, create another table called WorkCenterStats that stores the summary of the capacity of the work centers:

DROP TABLE IF EXISTS WorkCenterStats;

CREATE TABLE WorkCenterStats(
    totalCapacity INT NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Creating BEFORE INSERT trigger example

The following trigger updates the total capacity of the WorkCenterStats table before a new work center is inserted into the WorkCenter table:

DELIMITER $$

CREATE TRIGGER before_workcenters_insert
BEFORE INSERT
ON WorkCenters FOR EACH ROW
BEGIN
    DECLARE rowcount INT;
    
    SELECT COUNT(*) 
    INTO rowcount
    FROM WorkCenterStats;
    
    IF rowcount > 0 THEN
        UPDATE WorkCenterStats
        SET totalCapacity = totalCapacity + new.capacity;
    ELSE
        INSERT INTO WorkCenterStats(totalCapacity)
        VALUES(new.capacity);
    END IF; 

END $$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

In this trigger:

First, the name of the trigger is before_workcenters_insert specified in the CREATE TRIGGER clause:

CREATE TRIGGER before_workcenters_insert
Code language: SQL (Structured Query Language) (sql)

Second, the triggering event is:

BEFORE INSERT
Code language: SQL (Structured Query Language) (sql)

Third, the table that the trigger is associated with is WorkCenters table:

ON WorkCenters FOR EACH ROWCode language: SQL (Structured Query Language) (sql)

Finally, inside the trigger body, we check for any row in the WorkCenterStats table.

If the table WorkCenterStats has a row, the trigger adds the capacity to the totalCapacity column. Otherwise, it inserts a new row into the WorkCenterStats table.

Testing the MySQL BEFORE INSERT trigger

First, insert a new row into the WorkCenter table:

INSERT INTO WorkCenters(name, capacity)
VALUES('Mold Machine',100);
Code language: SQL (Structured Query Language) (sql)

Second, query data from the WorkCenterStats table:

SELECT * FROM WorkCenterStats;    
Code language: SQL (Structured Query Language) (sql)

The trigger has been invoked and inserted a new row into the WorkCenterStats table.

Third, insert a new work center:

INSERT INTO WorkCenters(name, capacity)
VALUES('Packing',200);
Code language: SQL (Structured Query Language) (sql)

Finally, query data from the WorkCenterStats:

SELECT * FROM WorkCenterStats;
Code language: SQL (Structured Query Language) (sql)

The trigger has updated the total capacity from 100 to 200 as expected.

Note that to properly maintain the summary table WorkCenterStats, you should also create triggers to handle update and delete events on the WorkCenters table.

In this tutorial, you have learned how to create a MySQL BEFORE INSERT trigger to maintain a summary table of another table.