Sql server trigger to update time field when there is real change in another field?

In a trigger, you have access to the 'inserted' and 'deleted' system tables. Those tables contains the records in the table that have been updated by the statement that caused the trigger to execute For an 'UPDATE' trigger, the 'inserted' table contains the records like they are in the new state, the 'deleted' table contains the records with the old values You'll have to make use of those 2 tables to find out which records have really changed, and update the ModificationDate for those records I think the statement inside the trigger will look something like this. (I haven't tested it) UPDATE myTable SET ModificationDate = getdate() FROM inserted, deleted WHERE inserted.

EmployeeId = deleted. EmployeeId AND (inserted. Name deleted.Name OR inserted.

Address deleted. Address) Edit: I've played around a bit: create trigger upd_employee on employee after update as begin update employee set modifdate = getdate() where employee. Empid in ( select i.

Empid from inserted I inner join deleted d on i. Empid = d. Empid where (i.

Name d. Name or i. Address d.

Address ) ) end insert into employee values (1, 'Frederik' , '', null) insert into employee values (2, 'User', '', null) update employee set address = 'some address' select * from employee update employee set name = 'test' where empid = 2 select * from employee update employee set name = 'test' where empid = 2 select * from employee.

In a trigger, you have access to the 'inserted' and 'deleted' system tables. Those tables contains the records in the table that have been updated by the statement that caused the trigger to execute. For an 'UPDATE' trigger, the 'inserted' table contains the records like they are in the new state, the 'deleted' table contains the records with the old values.

You'll have to make use of those 2 tables to find out which records have really changed, and update the ModificationDate for those records. I think the statement inside the trigger will look something like this. (I haven't tested it) UPDATE myTable SET ModificationDate = getdate() FROM inserted, deleted WHERE inserted.

EmployeeId = deleted. EmployeeId AND (inserted. Name deleted.Name OR inserted.

Address deleted. Address) Edit: I've played around a bit: create trigger upd_employee on employee after update as begin update employee set modifdate = getdate() where employee. Empid in ( select i.

Empid from inserted I inner join deleted d on i. Empid = d. Empid where (i.

Name d. Name or i. Address d.

Address ) ) end insert into employee values (1, 'Frederik' , '', null) insert into employee values (2, 'User', '', null) update employee set address = 'some address' select * from employee update employee set name = 'test' where empid = 2 select * from employee update employee set name = 'test' where empid = 2 select * from employee.

Nice answer. Very succinct – Preet Sangha Jul 24 at 9:59 @frederick I would just add a check using Update() before wasting any time joining the tables. Looks great otherwise.

If update(name) or update(address) begin ... BOL link – Nathan Skerl Jul 24 at 18:14 @Frederik Gheysels --- (1) What happens if a user/program updates the ModificationDate field "manually? " (2) What's wrong with classic inner joins on the Employees, inserted and deleted tables? What's the good of "select i.

Empid from etc. "? – user669226 Aug 2 at 2:03 This answers 95% of my question. I accept the answer.

– user669226 Aug 10 at 12:00.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.