Friends,

Please help me to undestand the best design to tackle this recurring problem.
I have a EMPLOYEE_MASTER table with the following structure. EMP_DESIGNATION may change over time. I want to keep the history of such changes for future references. What is the most efficient approach to store designation here? Should I move it to another table with an effective start date and end date?

EMPLOYEE_MASTER(EMPID,EMPNAME,EMP_DESIGNATION)

Thanks,
Harish.

Spot on.

pootle flump ur codings are working excelent.

Run this code with your table name as the parameter,
and it will generate a script for creating an archive table, along with the triggers necessary to maintain it. It will record every change to the data in your table.
sqlblindman private pastebin - collaborative debugging tool