How can I modify this trigger with loops and concat?

Question

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')
 );

Answer 1

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

• bli: im getting an error #1136 - Column count doesn't match value count at row 1
• druzin: forgot the operated_by column in the log table. sorry (fixed it )