How to create AFTER UPDATE Trigger in MySQL 2nd Method - MySQL Developer Tutorial

How to create AFTER UPDATE trigger in MySQL

You are working as MySQL Developer and you are asked to create a trigger that should keep records of updated records. You are required to keep the old records and after update. To keep the records of updated records, you can use After Update Trigger in MySQL. In below example, we are going to create customer Audit table
Method  2: 
You can have only one set of columns and then an extra column that tells about Record type such as "OLD" or "NEW".
Method 1: 
Create audit table with list of same columns but name them old_column1,old_column2....,new_column1,new_column2....

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
  ,RecordType varchar(100)
  ,actiondate datetime
  ,actionby varchar(100)
) ;

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

Create AFTER UPDATE Trigger in MySQL:

-- drop trigger customer_after_update;
DELIMITER //

CREATE TRIGGER customer_after_update
AFTER update
   ON customer FOR EACH ROW

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


   -- Get user name who is performing update
   SELECT USER() INTO var_User;
    SELECT SYSDATE() into var_date;

   -- Insert record into customer_audit table
   INSERT INTO customer_audit
   ( idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,actiondate,actionby,RecordType
 )
       VALUES
   ( old.idcustomer,
   old.firstname,old.lastname,old.age,old.phonenumber,
   old.dob,old.gender,var_date,
     var_User,"oldrecord"
   );
   
   insert into customer_audit(idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,actiondate,actionby,RecordType
 )
   VALUES(
   new.idcustomer,
   new.firstname,new.lastname,new.age,new.phonenumber,
   new.dob,new.gender, var_date, var_User,"updatedRecord" );
    

END; //

DELIMITER ;

We declare var_User variable so we can save the user name who is updating the record. We used SYSDATE() function to record the datetime for update.

Now you can update a record in customer table and then check the customer_audit table if all changes are audited successfully.