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

So, you're in the role of a MySQL Developer, and you've been asked to create a trigger that keeps track of updated records. To do this, you can utilize an "AFTER UPDATE" trigger in MySQL. Here's how you can achieve that:

First, let's go through a couple of methods you can use to accomplish this task:

Method 1: You can maintain a single set of columns and include an extra column that indicates the record type, such as "OLD" or "NEW".

Method 2: Alternatively, you can create an audit table with the same column names but differentiate them with prefixes like "old_column1," "old_column2," for old values and "new_column1," "new_column2," for new values.

Now, let's create a sample table called "customer" and insert some 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'),
-- More rows...
(5, 'Robert', 'Ladson', 69, '505-345900', '1960-01-01', 'M');

Next, let's create an audit table to store 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, it's time to create the "AFTER UPDATE" trigger:

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 the username of the person performing the update
SELECT USER() INTO var_User;
SELECT SYSDATE() INTO var_date;

-- Insert the old 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 the updated record into customer_audit table
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 ;

In this trigger, we've defined variables to store the user's name and the current date. We then insert the old and updated records into the `customer_audit` table with appropriate labels.

After setting up the trigger, you can update a record in the `customer` table and check the `customer_audit` table to confirm that the changes have been audited successfully. This will help you keep track of changes to customer records in your MySQL database.

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

Popular Posts

Categories