MySQL BEFORE INSERT Trigger

 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;

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 ;

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
);

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
);

Creating BEFORE INSERT trigger example

The following trigger updates the total capacity in the WorkCenterStats the 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 ;

In this trigger:

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

CREATE TRIGGER before_workcenters_insert

Second, the triggering event is:

BEFORE INSERT

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

ON WorkCenters FOR EACH ROW

Finally, inside the trigger body, we check if there is 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);

Second, query data from the WorkCenterStats table:

SELECT * FROM WorkCenterStats;    

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);

Finally, query data from the WorkCenterStats:

SELECT * FROM WorkCenterStats;

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 updates 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.