How to create after delete trigger in MySQL - MySQL Developer Tutorial

How to create after delete trigger in MySQL

Often we want to know who has deleted the records from table. In MySQL we can use After Delete Trigger to capture deleted row and insert into some audit table.  Many of the companies have the requirement to keep tract of deleted records and After Delete Trigger in MySQL works great.

Let's create sample table "customer" and insert some sample data.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');

Create Audit table which will save the deleted records.

CREATE TABLE `customer_audit` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
  ,deletedate datetime
  ,deletedby varchar(100)
) ; 

Notice that I have kept all the same column in customer_Audit what we have in customer table. Also I have added two more column deletedate and deletedby.

Now let's create After Delete Trigger. From here you can get the idea how your After Delete Trigger will look like.

Create After Delete Trigger in MySQL:

DELIMITER //

CREATE TRIGGER customer_after_delete
AFTER DELETE
   ON customer FOR EACH ROW

BEGIN
-- declare variables
  DECLARE var_User varchar(50);

   -- Get user name who is performing delete
   SELECT USER() INTO var_User;

   -- Insert record into customer_audit table
   INSERT INTO customer_audit
   ( idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,deletedate,deletedby)
    
   VALUES
   ( old.idcustomer,
   old.firstname,old.lastname,old.age,old.phonenumber,
   old.dob,old.gender,
     SYSDATE(),
     var_User );

END; //

DELIMITER ;

We declare var_User variable so we can save the user name who is deleting the record. We used SYSDATE() function to record the datetime for delete. All the other columns are populated from old values which are deleted from customer table.

Delete some records from customer table.

Delete from customer where idcustomer in (1,2);

Once you delete the records and run below query for customer_audit. you will see below records.

MySQL After Delete Trigger  | MariaDB After Delete Trigger example