EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL DELETE Trigger

MySQL DELETE Trigger

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byRavi Rathore

Updated May 29, 2023

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 with it. These triggers created help to maintain the logs records for related MySQL operations on a table, such as INSERT, DELETE, and UPDATE. For this, the log table keeps a 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 statements. MySQL Triggers are database objects that automatically respond when related events are performed in the table.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

MySQL AFTER DELETE Trigger

We create a MySQL AFTER DELETE Trigger on a database table to preserve a summary table associated with it. The AFTER DELETE Trigger fires immediately after a Delete query event is executed 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 below:

  • You should initially specify a desired name for the trigger to create immediately after the “CREATE TRIGGER” keywords.
  • Next, we will modify the clause “AFTER DELETE” to implement the trigger execution immediately after any delete query event that occurs in the table.
  • Then, specify the table name where the trigger will be applied after the aforementioned keyword “ON.”
  • At last, we need the trigger body comprising one or multiple statements 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 them to BEGIN END blocks. So, it is mandatory to transform; for the time being, the default delimiter is 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 using the following illustration.

I am setting up a demo table to outlook the procedure of AFTER DELETE trigger named Products.

Code:

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

Now, enter a 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:

View the records

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 the Products table and enter it into the Products_logs.

Code:

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

Output:

MySQL DELETE Trigger 2

The succeeding code will create an 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 ;

We will now check the trigger by deleting a row from the Products table.

Code:

DELETE FROM Products WHERE ProductID = 03;

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

Code:

SELECT * FROM Products_logs;

Output:

To view the changes, query the data forms Product_logs table

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

MySQL BEFORE DELETE Trigger

We create a BEFORE DELETE trigger in MySQL on a database table to preserve a related swift table that includes the deleted rows of the base table. Typically, the BEFORE DELETE Trigger triggers immediately before a Delete query event in a table.

The 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 below:

  • You must provide an initial name for the trigger to create immediately after the “CREATE TRIGGER” keywords.
  • Next, we will include the clause “BEFORE DELETE” to implement the trigger’s firing just before any delete query event happens in the table.
  • After the aforementioned keyword “ON,” you should specify the table name where you want to link the trigger.
  • At last, we need to add the trigger body comprising one or multiple statements 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;

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

Example:

To set up a demo table named “Emp_data” with columns such as “id,” “name,” “salary_amount,” and other fields, we can follow these steps:

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);

Insert 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:

MySQL DELETE Trigger 4

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 BEFORE DELETE trigger that follows will insert a new row into the Salary_logs table prior to executing the delete query on the Emp_data table to remove a row.

The trigger code is 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 the Emp_data table.

Code:

DELETE FROM Emp_dataWHERE Emp_ID = 12;

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

Code:

SELECT * FROM Salary_logs;

Output:

MySQL DELETE Trigger 5

You can see the table recording the trigger information with a timestamp.

Conclusion

The MySQL triggers act as collectors that sum up all the values inserted into the columns of a related table or delete or update the table’s values. This stores the lists of operations carried out for events executed simultaneously. The MySQL AFTER or BEFORE Trigger invokes the triggers and adds the removed records into the linked-up archive table in the database.

Recommended Articles

We hope that this EDUCBA information on “MySQL DELETE Trigger” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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