Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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')
    );
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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