I have two triggers for an update in the person table.
The first trigger will update the lastmodified date on the update record (below)
ALTER TRIGGER [dbo].[updPerson]
set Lastmodified = Getdate()
where PersonID in (select Deleted.PersonID from Deleted)
The second trigger will take the old and new record an insert into my Auditperson table (below).
ALTER TRIGGER [dbo].[updAuditPerson]
if exists (select * from Deleted)
insert into AuditPerson (PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName,EquitracNumber, SignificantOther, StatusID, LastModifiedBy, LastModified, AuditAction)
select Deleted.PersonID, Deleted.Prefix, Deleted.FirstName, Deleted.MiddleName, Deleted.LastName, Deleted.Suffix, Deleted.Credentials, Deleted.FullName, Deleted.NickName, Deleted.SAMAccountName, Deleted.EquitracNumber, Deleted.SignificantOther, Deleted.StatusID, SYSTEM_USER, GetDate(), 'Record Updated'
Now my auditperson table is showing two records for the same person.
John C. Doe (new record after change), last modified 2010-09-02 14:46:10.460
John Doe (old record before change - added the C.), last modified 2010-09-02 14:48:20.340
Here's the problem my audit table is now showing two records but I want the new record to have the lastest modified date.
I've tried combining this as one trigger and this only added the old record to my audit table. I need the old and new record but the new record must have the latest modified date.
Any ideas on how I can achieve this in SQL?