AFTER DELETE Trigger

This MariaDB tutorial explains how to create an AFTER DELETE Trigger in MariaDB with syntax and examples.

Description

An AFTER DELETE Trigger means that MariaDB will fire this trigger after the DELETE operation is executed.

Syntax

The syntax to create an AFTER DELETE Trigger in MariaDB is:

CREATE TRIGGER trigger_name

AFTER 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.

AFTER DELETE

It indicates that the trigger will fire after the DELETE operation is executed.

table_name

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

Restrictions

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

Note

• See also how to create AFTER INSERT, AFTER UPDATE, BEFORE DELETE, 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 AFTER 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 AFTER DELETE trigger as follows:

DELIMITER //

 

CREATE TRIGGER contacts_after_delete

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