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
Code language: SQL (Structured Query Language) (sql)

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 ;
Code language: SQL (Structured Query Language) (sql)

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 the 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
);
Code language: SQL (Structured Query Language) (sql)

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);
Code language: SQL (Structured Query Language) (sql)

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()
);
Code language: SQL (Structured Query Language) (sql)

Creating BEFORE DELETE trigger example

The following BEFORE DELETE trigger inserts a new row into the SalaryArchives table before a row from the Salaries 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 ;
Code language: SQL (Structured Query Language) (sql)

In this trigger:

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

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

Second, the triggering event is:

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

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

ON Salaries FOR EACH ROW
Code language: SQL (Structured Query Language) (sql)

Finally, inside the trigger body insert a 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;
Code language: SQL (Structured Query Language) (sql)

Second, query data from the SalaryArchives table:

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

The trigger was invoked and inserted a new row into the SalaryArchives table.

Third, delete all rows from the Salaries table:

DELETE FROM salaries;Code language: SQL (Structured Query Language) (sql)

Finally, query data from the SalaryArchives table:

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

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.

Was this tutorial helpful?