BEFORE DELETE Trigger - MySQL Triggers

In MySQL, a trigger is a named database object that is associated with a table and defined to run automatically before or after certain events occur on the table. The BEFORE DELETE trigger is a type of trigger that is automatically executed before a row is deleted from the table. In this tutorial, we'll cover the syntax, example, output, explanation, use, important points, and summary of BEFORE DELETE Trigger in MySQL.

Syntax

Here's the syntax for creating a BEFORE DELETE trigger in MySQL:

CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name
FOR EACH ROW
BEGIN
   -- Trigger statements
END;

In this syntax:

• trigger_name is the name of the trigger that you want to create.
• BEFORE DELETE specifies that the trigger should be executed before a row is deleted from the table.
• table_name is the name of the table that you want to associate the trigger with.
• FOR EACH ROW specifies that the trigger should be executed for each row that is deleted from the table.
• -- Trigger statements represent the statements that you want to execute when the trigger is invoked.

Example

Suppose we have a table called Employees with columns id, name, salary, and department. We can create a BEFORE DELETE trigger on this table to log the deleted row in another table called DeletedEmployees. Here's how we can do it:

CREATE TRIGGER before_employee_delete
BEFORE DELETE
ON Employees
FOR EACH ROW
BEGIN
   INSERT INTO DeletedEmployees
   VALUES (OLD.id, OLD.name, OLD.salary, OLD.department);
END;

In this example, we're creating a trigger called before_employee_delete that is executed before a row is deleted from the Employees table. The trigger inserts a copy of the deleted row into the DeletedEmployees table.

Output

When a row is deleted from the Employees table, the trigger is automatically invoked, and a copy of the deleted row is inserted into the DeletedEmployees table.

Explanation

In the example above, we created a BEFORE DELETE trigger that logs a copy of the deleted row in another table called DeletedEmployees. We used the OLD keyword to reference the values of the deleted row.

When a row is deleted from the Employees table, the MySQL server invokes the before_employee_delete trigger. The trigger then inserts a copy of the deleted row into the DeletedEmployees table.

Use

BEFORE DELETE triggers can be used to enforce business rules and data integrity. You can use them to control what data is deleted from a table, audit data changes, and maintain referential integrity.

Important Points

• A BEFORE DELETE trigger is executed before a row is deleted from a table.
• The OLD keyword is used to reference the values of the deleted row.
• Each row that is deleted from the table triggers the execution of the trigger.
• The trigger can contain one or more SQL statements separated by semicolons.

Summary

In this tutorial, we covered the syntax, example, output, explanation, use, and important points of BEFORE DELETE Trigger in MySQL. Triggers are powerful database objects that can be used to enforce business rules, maintain data integrity, and control data changes. BEFORE DELETE triggers can be used to perform tasks before a row is deleted from a table, such as logging deleted data into another table. Understanding MySQL Triggers can help you enhance your database security and data integrity.