MySQL BEFORE DELETE Trigger
Summary: in this tutorial, you will learn how to create a MySQL BEFORE DELETE
trigger to add deleted rows into an archive table.
Introduction to MySQL BEFORE DELETE
triggers
MySQL BEFORE DELETE
triggers are fired automatically before a delete event occurs in a table.
Here is the basic syntax of creating a MySQL BEFORE DELETE
trigger:
CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
trigger_body
In this syntax:
First, specify the name of the trigger which you want to create after the CREATE TRIGGER
keywords.
Second, use BEFORE DELETE
clause to specify that the trigger is invoked right before a delete event.
Third, specify the name of the table that the trigger is associated with after the ON
keyword.
Finally, specify the trigger body which consists of one or more statements that execute when the trigger is fired.
Notice that if you have multiple statements in the trigger_body
, you need to use the BEGIN END
block to wrap these statements and temporarily change the default delimiter as follows:
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
BEGIN
-- statements
END$$
DELIMITER ;
In a BEFORE DELETE
trigger, you can access the OLD
row but cannot update it. Also, there is no NEW
row in the BEFORE DELETE
trigger.
MySQL BEFORE DELETE
trigger example
Let’s see the following BEFORE DELETE
trigger example.
Setting up a sample table
First, create a new table called Salaries
that stores salary information of employees
DROP TABLE IF EXISTS Salaries;
CREATE TABLE Salaries (
employeeNumber INT PRIMARY KEY,
validFrom DATE NOT NULL,
amount DEC(12 , 2 ) NOT NULL DEFAULT 0
);
Second, insert some rows into the Salaries
table:
INSERT INTO salaries(employeeNumber,validFrom,amount)
VALUES
(1002,'2000-01-01',50000),
(1056,'2000-01-01',60000),
(1076,'2000-01-01',70000);
Third, create a table that stores the deleted salary:
DROP TABLE IF EXISTS SalaryArchives;
CREATE TABLE SalaryArchives (
id INT PRIMARY KEY AUTO_INCREMENT,
employeeNumber INT PRIMARY KEY,
validFrom DATE NOT NULL,
amount DEC(12 , 2 ) NOT NULL DEFAULT 0,
deletedAt TIMESTAMP DEFAULT NOW()
);
Creating BEFORE DELETE
trigger example
The following BEFORE DELETE
trigger inserts a new row into the SalaryArchives
the table before a row from the Salaries
the table is deleted.
DELIMITER $$
CREATE TRIGGER before_salaries_delete
BEFORE DELETE
ON salaries FOR EACH ROW
BEGIN
INSERT INTO SalaryArchives(employeeNumber,validFrom,amount)
VALUES(OLD.employeeNumber,OLD.validFrom,OLD.amount);
END$$
DELIMITER ;
In this trigger:
First, the name of the trigger is before_salaries_delete
specified in the CREATE TRIGGER
clause:
CREATE TRIGGER before_salaries_delete
Second, the triggering event is:
BEFORE DELETE
Third, the table that the trigger is associated with is Salaries
table:
ON Salaries FOR EACH ROW
Finally, inside the trigger body insert the deleted row into the SalaryArchives
table.
Testing the MySQL BEFORE DELETE
trigger
First, delete a row from the Salaries
table:
DELETE FROM salaries
WHERE employeeNumber = 1002;
Second, query data from the SalaryArchives
table:
SELECT * FROM SalaryArchives;
The trigger was invoked and inserted a new row into the SalaryArchives
table.
Third, delete all rows from the Salaries table:
DELETE FROM salaries;
Finally, query data from the SalaryArchives
table:
SELECT * FROM SalaryArchives;
The trigger was triggered twice because the DELETE
statement deleted two rows from the Salaries
table.
In this tutorial, you have learned how to create a MySQL BEFORE DELETE
trigger to add deleted rows into an archive table.
0 Comments
CAN FEEDBACK
Emoji