asked
Trigger to Insert 1 Row Of Historical Data To Log All Row Updates
I'm using a SQL Server 2000 database that need a trigger to insert a row of data into a historical log table that records all data changes from the original table into 1 row. i.e. a row exists in the person table: person_tbl id, firstname, lastname, age 1, hary, bever, 92 i.e. we update the row to correct the errors: update person_tbl set firstname = 'harry', lastname = 'beaver', age = '29' where id = 1 i.e. on the update I want the old row recorded as one row for historical purposes: person_history_tbl id, firstname, lastname, age 1, hary, bever, 29 i.e. while the original table holds the new data: person_tbl id, firstname, lastname, age 1, harry, beaver, 29 At present the trigger inserts multiple rows into the historical table for each update. TIA
Thanks I managed to resolve the issue over the weekend. By final code is: create trigger person_tbl_update on dbo.person_tbl for update as if update(firstname) or update(surname) or update(age) begin if (select count(*) from inserted) > 0 and (select count(*) from deleted) > 0 begin insert into person_history_tbl(id,firstname,surname,age) select id,firstname,surname,age from deleted end end
insert into person_history_tbl ( id, firstname, lastname, age ) select id, firstname, lastname, age from inserted
16 People are following this question.
