MySQL: BEFORE DELETE Trigger

This tutorial explains how to create a BEFORE DELETE Trigger in MySQL with syntax and examples.

Description

A BEFORE DELETE Trigger means that MySQL will fire this trigger before the DELETE operation is executed.

Syntax

The syntax to create a BEFORE DELETE Trigger in MySQL is:

CREATE TRIGGER <strong>trigger_name</strong>
BEFORE DELETE
   ON <strong>table_name</strong> FOR EACH ROW

BEGIN

   -- variable declarations


   -- trigger code


END;

Parameters or Arguments

trigger_name

The name of the trigger to create.

BEFORE DELETE

It ndicates that the trigger will fire before the DELETE operation is executed.

table_name

The name of the table that the trigger is created on.

Restrictions

• You can not create a BEFORE trigger on a view.
• You can update the NEW values.
• You can not update the OLD values.

Note

• See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE INSERT, and BEFORE UPDATE triggers.
• See also how to drop a trigger.

Example

Let's look at an example of how to create an BEFORE DELETE trigger using the CREATE TRIGGER statement in MySQL.

If you had a table created as follows:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  created_date DATE,
  created_by VARCHAR(30),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

We could then use the CREATE TRIGGER statement to create an BEFORE DELETE trigger as follows:

DELIMITER //

CREATE TRIGGER contacts_before_delete
BEFORE DELETE
   ON contacts FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing the DELETE into table

   SELECT USER() INTO vUser;

   -- Insert record into audit table

   INSERT INTO contacts_audit
   ( contact_id,
     deleted_date,
     deleted_by)
   VALUES
   ( OLD.contact_id,
     SYSDATE(),
     vUser );

END; //

DELIMITER ;