0

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

1 Answer 1

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
2
  • im getting an error #1136 - Column count doesn't match value count at row 1
    – bli
    Apr 24, 2013 at 19:28
  • forgot the operated_by column in the log table. sorry (fixed it )
    – druzin
    Apr 24, 2013 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.