BEFORE DELETE Trigger in MySQL is invoked automatically whenever a delete operation is fired on the table. In this article, we are going to learn how to create a before delete trigger with its syntax and example.
Syntax
The following is the syntax to create a BEFORE DELETE trigger in MySQL:
CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
Trigger_body ; The BEFORE DELETE trigger syntax parameter can be explained as below:
- First, we will specify the name of the trigger that we want to create. It should be unique within the schema.
- Second, we will specify the trigger action time, which should be BEFORE DELETE. This trigger will be invoked before each row of alterations occurs on the table.
- Third, we will specify the name of a table to which the trigger is associated. It must be written after the ON keyword. If we did not specify the table name, a trigger would not exist.
- Finally, we will specify the statement for execution when the trigger is activated.
If we want to execute multiple statements, we will use the BEGIN END block that contains a set of queries to define the logic for the trigger. See the below syntax:
DELIMITER $$
CREATE TRIGGER trigger_name BEFORE DELETE
ON table_name FOR EACH ROW
BEGIN
variable declarations
trigger code
END$$
DELIMITER ; Restrictions
- We can access the OLD rows but cannot update them in a BEFORE DELETE trigger.
- We cannot access the NEW rows. It is because there are no new row exists.
- We cannot create a BEFORE DELETE trigger on a VIEW.
BEFORE DELETE Trigger Example
Let us understand how to create a BEFORE DELETE trigger using the CREATE TRIGGER statement in MySQL with an example.
Suppose we have created a table named salaries to store the salary information of an employee as follows:
CREATE TABLE salaries (
emp_num INT PRIMARY KEY,
valid_from DATE NOT NULL,
amount DEC(8 , 2 ) NOT NULL DEFAULT 0
); Next, we will insert some records into this table using the below statement:
INSERT INTO salaries (emp_num, valid_from, amount)
VALUES
(102, '2020-01-10', 45000),
(103, '2020-01-10', 65000),
(105, '2020-01-10', 55000),
(107, '2020-01-10', 70000),
(109, '2020-01-10', 40000); Execute the SELECT query to see the table data.
Third, we will create another table named salary_archives that keeps the information of deleted salary.
CREATE TABLE salary_archives (
id INT PRIMARY KEY AUTO_INCREMENT,
emp_num INT,
valid_from DATE NOT NULL,
amount DEC(18 , 2 ) NOT NULL DEFAULT 0,
deleted_time TIMESTAMP DEFAULT NOW()
); We will then create a BEFORE DELETE trigger that inserts a new record into the salary_archives table before a row is deleted from the salaries table.
DELIMITER $$
CREATE TRIGGER before_delete_salaries
BEFORE DELETE
ON salaries FOR EACH ROW
BEGIN
INSERT INTO salary_archives (emp_num, valid_from, amount)
VALUES(OLD. emp_num, OLD.valid_from, OLD.amount);
END$$
DELIMITER ; 
In this trigger, we have first specified the trigger name before_delete_salaries. Then, specify the triggering event. Third, we have specified the table name on which the trigger is associated. Finally, we have written the trigger logic inside the trigger body that insert the deleted row into the salary_archives table.
How to call the BEFORE DELETE trigger?
Let us test the above created BEFORE DELETE trigger and how we can call them. So first, we will remove a row from the salaries table:
mysql> DELETE FROM salaries WHERE emp_num = 105; Second, we will query data from the salary_archives table to verify the above-created trigger is invoked or not by using the select statement:
mysql> SELECT * FROM salary_archives; After executing a statement, we can see that the trigger was invoked successfully and inserted a new record into the salary_archives table.
Third, we will remove all rows from the salaries table:
mysql> DELETE FROM salaries; Finally, we will query data from the salary_archives table again. The trigger was called four times because the DELETE statement removed four records from the salaries table. See the below output:
How to create BEFORE DELETE Trigger in MySQL workbench?
To create a BEFORE DELETE trigger using MySQL workbench, we first need to launch it and then log in using the username and password we created earlier. We will get the screen as follows:
Now do the following steps for creating BEFORE DELETE trigger:
1. Go to the Navigation tab and click on the Schema menu that contains all the databases available in the MySQL server.
2. Select the database (for example, employeedb), double click on it. It will show the sub-menu that contains Tables, Views, Functions, and Stored Procedures. See the below screen.
3. Expand the Tables sub-menu and select the table on which you want to create a trigger. After selecting a table, right-click on the selected table (for example, salaries), and then click on the Alter Table option. See the below image:
4. Clicking on the Alter Table option gives the screen as below:
5. Now, click on the Trigger tab shown in the red rectangular box of the previous section, then select the Timing/Event BEFORE DELETE. We will notice that there is a (+) icon button to add a trigger. Clicking on that button, we will get a default code on trigger based on choosing Timing/Event:
6. Now, complete the trigger code, review them once again, and if no error is found, click on the Apply button.
7. After clicking on the Apply button, click on the Finish button for completion.
8. If we take at the schema menu, we will see the trigger salaries_before_trigger under the salaries table as follows:
Leave a Reply