data history of table

• sahana

  SSCrazy

  Points: 2530

  More actions

  August 10, 2005 at 9:41 am

  #164848

  I have  a table emp in my database and the records in the database will be updated frequently.

  My emp table has the following columns

  emp_Number,emp_name,join_date,sal

  So if any of the values are updated for the exissing data I want to store the data in the table into a history table before its updated.

  So how can i implement this with a trigger?

  thanks.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 10, 2005 at 9:45 am

  #581096

  Create trigger tr_emp_Audit_UD on dbo.emp

  for update, delete

  as

  set nocount on

  Insert into dbo.emp_history (emp_number, emp_name, join_date, sal)

  select emp_number, emp_name, join_date, sal from deleted

  go

  you might want to add a modification data on the history (default=getdate()) and updated by (default = user_name())