Mysql – How to modify this trigger with loops and concat

MySQLtrigger

I have this trigger below which inserts new values in the an emplog table. I want to have my trigger insert in the description column the event that took place. For example, if I changed the last_name I want it to record:

old.lastname was changed to new.last_name

If its the first name or gender or DOB or marital or SSN that were updated it should do the same.

How can I achieve this? Can I use the concat function and loops?

CREATE TRIGGER emplog_update AFTER UPDATE ON emp 
FOR EACH ROW 
INSERT INTO emplog 
VALUES (
    NEW.id
    ,NEW.lastname
    ,NEW.firstname
    ,NEW.gender
    ,NEW.dob
    ,NEW.marital
    ,NEW.SSN
    ,'U'
    ,NULL
    ,USER()
    ,('this is where the description will go')
    );

Best Answer

How about expanding this a little and do a proper log table, logging every update, insert or delete. That way you can keep a proper history. For this, in MySQL, you will need three triggers :

CREATE TABLE EMP ( ID INT,
                   LASTNAME varchar(100),
                   firstname varchar(100),
                   gender varchar(1),
                   dob date,
                   marital varchar(1),
                   ssn varchar(20)
                 );

CREATE TABLE EMPLOG ( empid INT,
                   operation varchar(1),
                   operationdate date,
                   operated_by varchar(200),
                   LASTNAME varchar(100),
                   firstname varchar(100),
                   gender varchar(1),
                   dob date,
                   marital varchar(1),
                   ssn varchar(20)
                 );

CREATE TRIGGER emplog_insert AFTER INSERT ON emp 
FOR EACH ROW 
INSERT INTO emplog VALUES 
(NEW.id,
 'I',
 Now(),
 USER(),
 NEW.lastname,
 NEW.firstname,
 NEW.gender,
 NEW.dob,
 NEW.marital,
 NEW.SSN
);

CREATE TRIGGER emplog_update AFTER UPDATE ON emp 
FOR EACH ROW 
INSERT INTO emplog VALUES 
(NEW.id,
 'U',
 Now(),
 USER(),
 NEW.lastname,
 NEW.firstname,
 NEW.gender,
 NEW.dob,
 NEW.marital,
 NEW.SSN
);

CREATE TRIGGER emplog_delete AFTER DELETE ON emp 
FOR EACH ROW 
INSERT INTO emplog VALUES 
(OLD.id,
 'D',
 Now(),
 USER(),
 OLD.lastname,
 OLD.firstname,
 OLD.gender,
 OLD.dob,
 OLD.marital,
 OLD.SSN
);

If you then wish to check out the history of what happened for a specific employee, you simply run this query :

select *
from Emplog
where empid = ?
order by operationdate
Related Question