BEFORE DELETE Trigger

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

Description

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

Syntax

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

CREATE TRIGGER trigger_name

BEFORE DELETE

   ON table_name FOR EACH ROW

 

BEGIN

 

   -- variable declarations

 

   -- trigger code

 

END;

Parameters or Arguments

trigger_name

The name of the trigger to create.

BEFORE DELETE

It indicates 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 MariaDB.

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 ;