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]
ON [dbo].[Person]
FOR UPDATE
AS
BEGIN
Update Person
set Lastmodified = Getdate()
where PersonID in (select Deleted.PersonID from Deleted)
END
The second trigger will take the old and new record an insert into my Auditperson table (below).
ALTER TRIGGER [dbo].[updAuditPerson]
ON [dbo].[Person]
FOR UPDATE
AS
if exists (select * from Deleted)
BEGIN
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'
from Deleted
END
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?