MySQL DELETE Trigger

Introduction to MySQL DELETE Trigger

MySQL DELETE Trigger is performed as a stored program that is invoked to call the events before or after any specific query execution on a particular database table associated to it. This triggers created helps to maintain the logs records for related MySQL operations on a table such as INSERT, DELETE and UPDATE. For this the log table keeps summary of the related table where the triggers are linked or created so that we can know any updates available or done in the database by the user for specific query statement. MySQL Triggers are database objects that automatically respond when related events are performed in the table.

MySQL AFTER DELETE Trigger

This MySQL AFTER DELETE Trigger is formed on a database table to reserve summary table connected to it. Typically, the AFTER DELETE Trigger is fired promptly after a MySQL Delete query event accomplishes in a table.

Discover the simple syntax for using this trigger by the successive SQL command:

Code:

CREATE TRIGGER TriggerName
AFTER DELETE ON TableName FOR EACH ROW
TriggerBody

The above structure code is explained as below:

• Originally, arrange for a name to the trigger that is desired to be created just after the keywords mentioned as CREATE TRIGGER.
• After this, we will enhance the clause AFTER DELETE to implement the trigger to be executed right after any delete query event occurring in the table.
• Then mention the name of the table where the trigger is to be allied just after the keyword ON mentioned above.
• At last we need the trigger body that comprises of one or multiple statements that are essential to ample the query when we run a trigger.

But when we practice several statements in the trigger body then, to bind the statements we will apply BEGIN END blocks. So, it is mandatory to transform for the time being the default delimiter exemplified as follows.

Code:

DELIMITER $$
CREATE TRIGGER TriggerName
AFTER DELETE ON TableName FOR EACH ROW
BEGIN
>>>>statements
END$$
DELIMITER ;

In this kind of trigger, there is an option to access the OLD row but not to alter it. Also, in the AFTER DELETE trigger, there occurs no NEW row.

Believing, we will validate this by means of the following illustration.

Setting up a demo table to outlook the procedure of AFTER DELETE trigger named as Products.

Code:

DROP TABLE IF EXISTS Products;
CREATE TABLE Products (ProductID INT PRIMARY KEY, Product_NameVARCHAR(255) NOT NULL, Cost INT NOT NULL);

Now, entering few records into the table.

Code:

INSERT INTO `products`(`ProductID`, `Product_Name`, `Cost`) VALUES ('01','Parle G','100');

And so on.

View the records.

Code:

SELECT * FROM Products;

Output:

Let us also create another table that will store the deleted cost amount.

Code:

DROP TABLE IF EXISTS Products_logs;
CREATE TABLE Products_logs (Products_TotalDECIMAL(15,2) NOT NULL);

We will use the SUM() function to calculate the total cost from Products table and enter it the Products_logs.

Code:

INSERT INTO Products_logs(Products_Total)
SELECT SUM(Cost) FROM Products;

Output:

The succeeding code will create AFTER DELETE trigger in the Products table.

Code:

DELIMITER $$
CREATE TRIGGER after_cost
AFTER DELETE ON Products FOR EACH ROW
BEGIN
INSERT INTO Products_logs (Products_Total)
SELECT SUM(Cost) FROM Products;
END$$
DELIMITER ;

Now, we will check the trigger by deleting a row from Products table.

Code:

DELETE FROM Products WHERE ProductID = 03;

To view the changes, query the data forms Product_logs table below.

Code:

SELECT * FROM Products_logs;

Output:

You can see that the table that has new row showing reduced cost total information.

MySQL BEFORE DELETE Trigger

This MySQL BEFORE DELETE Trigger is created on a database table to preserve swift table related to it that includes the deleted rows of the base table. Normally, the BEFORE DELETE Trigger is triggered instantly before a MySQL Delete query event executes in a table.

Basic syntax for using this trigger by the subsequent SQL command:

Code:

CREATE TRIGGER TriggerName
BEFORE DELETE ON TableNameFOR EACH ROW
TriggerBody

The above structure code is explained as below:

• Initially, provide a name to the trigger that is needed to be created just after the keywords mentioned as CREATE TRIGGER.
• After this, we will add the clause BEFORE DELETE to implement the trigger to be fired right before any delete query event occurring in the table.
• Then provide the name of the table where the trigger is to be associated just after the keyword ON mentioned above.
• At last we need to add the trigger body that comprises of one or multiple statements that are necessary to complete the query when we fire a trigger.

But when we use multiple statements in the trigger body then, to wrap the statements we need to apply BEGIN END blocks. So, it is required to modify for the time being the default delimiter illustrated as follows.

Code:

DELIMITER $$
CREATE TRIGGER TriggerName
BEFORE DELETE ON TableName FOR EACH ROW
BEGIN
>>>>statements
END$$
DELIMITER;

In this type of trigger, there is a chance to access the OLD row but we cannot alter it. Also, in the BEFORE DELETE trigger, there exists no NEW row.

Example:

Let us set up a demo table to view the use of BEFORE DELETE trigger named as Emp_data having ids, names, salary amount and other fields.

Code:

DROP TABLE IF EXISTS Emp_data;

CREATE TABLE Emp_data(Emp_ID INT PRIMARY KEY, Emp_NameVARCHAR(255) NOT NULL, Emp_SalaryINT NOT NULL DEFAULT 0);

Now, inserting some records into the table created for reference using the query below.

Code:

INSERT INTO `emp_data`(`Emp_ID`, `Emp_Name`, `Emp_Salary`) VALUES ('10', 'Rita', '5000');

And so on.

View the records.

Code:

SELECT * FROM Emp_data;

Output:

Let us also create another table that will store the removed salary amount.

Code:

DROP TABLE IF EXISTS Salary_logs;
CREATE TABLE Salary_logs (Salary_ID INT PRIMARY KEY AUTO_INCREMENT, Emp_ID INT, Emp_Name VARCHAR(255) NOT NULL, Emp_Salary INT NOT NULL, Deleted_Time TIMESTAMP DEFAULT NOW());

The succeeding BEFORE DELETE trigger will add a new row in the table Salary_logs before the delete query from Emp_data table is executed to delete a row.

The trigger code is written as follows.

Code:

DELIMITER $$
CREATE TRIGGER before_salary
BEFORE DELETE ON Emp_dataFOR EACH ROW
BEGIN
INSERT INTO Salary_logs(Emp_ID, Emp_Name, Emp_Salary)
VALUES (OLD.Emp_ID, OLD.Emp_Name, OLD.Emp_Salary);
END$$
DELIMITER;

Now, we will check the trigger by deleting a row from Emp_data table.

Code:

DELETE FROM Emp_dataWHERE Emp_ID = 12;

To view the changes, query the data from Salary_logs table below.

Code:

SELECT * FROM Salary_logs;

Output:

You can see that the table that has recorded the trigger information with timestamp.

Conclusion

The MySQL triggers act as collectors that sums up all the values inserted into the columns of related table or deleted or say updated values of the table. This stores the lists of operations carried out for events executed simultaneously. The MySQL AFTER or BEFORE Trigger is used to invoke the triggers and add the removed records into the linked up archive table in the database.

This is a guide to MySQL DELETE Trigger. Here we discuss the introduction to MySQL DELETE Trigger, query examples on BEFORE, AFTER DELETE trigger respectively. You may also have a look at the following articles to learn more –

1. MySQL Datetime
2. MySQL CHECK Constraint
3. MySQL Date Functions
4. MySQL Alias