Trigger for getting METADATA of table

[sunil divyeshvar]

Hi ,

Suppose i hv one table called emp
table structure as follows:
emp_name,
emp_address,
emp_number

now,
i need to create a LOG table of emp so that if any field of emp table got updated the value stored in LOG_emp table.

so now my LOG_emp table will have following structure:

field_name ,
old_value,
new_value,
modify_date

, suppose i update emp_name in emp table then my entry in LOG_emp table will look like this:
field_name old_value new_value modify_date
emp_name sunil manu 2010-12-17


then if i update emp_number then my entry in LOG_emp table will look like this:

field_name old_value new_value modify_date
emp_name sunil manu 2010-12-17
emp_number 123 456 2010-12-18


is it possible ? inshort which ever field is update i want the NAME of that column in my LOG_emp table.

----------------------------

I have alrdy created such trigger bt i want to make it dynamically i dnt want to define all the columns name as shown in following trigger:


DELIMITER //
CREATE TRIGGER trg_update_emp after update on emp
FOR EACH ROW BEGIN

IF old.emp_name <> new.emp_name THEN
INSERT INTO LOG_emp(field_name,old_value,new_value,modify_date) VALUES ('emp_name',old.emp_name,new.emp_name,now());
END IF;

END //
DELIMITER ;


this is only for emp_name column thus u can write for all columns by using IF-END IF statements.

i dnt want to write all these for all the columns is there any other way ???


Thanks.



Edited 2 time(s). Last edit at 12/23/2010 12:16PM by sunil divyeshvar.