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

How to create AFTER INSERT trigger in MySQL

Often the companies want to keep the records who has inserted the records in the table. It helps for audit purpose even later the records are deleted from actual table. In MySQL we can create After Insert trigger that can be used to insert the same record which we are inserting in actual table to Audit table.

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
  ,insertdate datetime
  ,insertedby 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 insertdate and insertedby.

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

Create AFTER INSERT Trigger in MySQL:



DELIMITER //

CREATE TRIGGER customer_after_insert
AFTER insert
   ON customer FOR EACH ROW

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

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

   -- Insert record into customer_audit table
   INSERT INTO customer_audit
   ( idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,insertdate,insertedby)
    
   VALUES
   ( new.idcustomer,
   new.firstname,new.lastname,new.age,new.phonenumber,
   new.dob,new.gender,
     SYSDATE(),
     var_User );

END; //

DELIMITER ;


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

Insert some rows in customer table and then query "customer_audit" table, you will see the same records with insert date and inserted by.

No comments:

Post a Comment