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 $$BEFORE INSERT
ON table_name FOR EACH ROW
BEGIN
— statements
END$$
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;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;totalCapacity INT NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
Creating BEFORE INSERT trigger example
The following trigger updates the total capacity in the WorkCenterStats table before a new work center is inserted into the WorkCenter table:
DELIMITER $$BEFORE INSERT
ON WorkCenters FOR EACH ROW
BEGIN
DECLARE rowcount INT;
INTO rowcount
FROM WorkCenterStats;
UPDATE WorkCenterStats
SET totalCapacity = totalCapacity + new.capacity;
ELSE
INSERT INTO WorkCenterStats(totalCapacity)
VALUES(new.capacity);
END IF;
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 associated with is WorkCenters table:
ON WorkCenters FOR EACH ROW
Code language: SQL (Structured Query Language) (sql)
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);
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.