asked on

SQL Update Trigger

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?

pivar

Hi,

How about

ALTER TRIGGER [dbo].[updPerson]
ON [dbo].[Person]
FOR UPDATE
AS BEGIN
Update Person
set Lastmodified = Getdate()
from Person p
join Deleted d on d.PersonID=d.PersonID

insert into AuditPerson (PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName,EquitracNumber, SignificantOther, StatusID, LastModifiedBy, LastModified, AuditAction)
select PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName, EquitracNumber, SignificantOther, StatusID, SYSTEM_USER, GetDate(), 'Record Updated'
from Deleted

insert into AuditPerson (PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName,EquitracNumber, SignificantOther, StatusID, LastModifiedBy, LastModified, AuditAction)
select PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName, EquitracNumber, SignificantOther, StatusID, SYSTEM_USER, GetDate(), 'Record Updated'
from Inserted
END

/peter

vnewman29

ASKER

The first part of the trigger updated the last modified field for ALL of the records in the person table.
And the audit table is showing both table except they have the same last modified date which can be fix using the "Waitfor Delay".

Any ideas on how to fix the top part of the trigger. Should we use the where clause here?

pivar

I'm sorry, first part had a typo, should be

Update Person
set Lastmodified = Getdate()
from Person p
join Deleted d on d.PersonID=p.PersonID

vnewman29

ASKER

I believe I got it to work with your guidance.

ALTER TRIGGER [dbo].[updAuditPerson]
ON [dbo].[Person]
FOR UPDATE
AS BEGIN
Update Person
set Lastmodified = Getdate()
from Person p
where PersonID in (select Deleted.PersonID from Deleted)

      Print 'First Record'
insert into AuditPerson (PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName,EquitracNumber, SignificantOther, StatusID, LastModifiedBy, LastModified, AuditAction)
select PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName, EquitracNumber, SignificantOther, StatusID, SYSTEM_USER, GetDate(), 'Record Updated'
from Deleted

      Print 'Second Record'
            Waitfor Delay '00:00:01'
insert into AuditPerson (PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName,EquitracNumber, SignificantOther, StatusID, LastModifiedBy, LastModified, AuditAction)
select PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName, EquitracNumber, SignificantOther, StatusID, SYSTEM_USER, GetDate(), 'Record Updated'
from Inserted
END

-------------

This is producing what I want. I am going to test some more.

pivar

I may not have understood the lastmodified requirement. They should have the same time as the modification happens once? Or do you mean the old record should use the modification date for the earlier modification like:

insert into AuditPerson (PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName,EquitracNumber, SignificantOther, StatusID, LastModifiedBy, LastModified, AuditAction)
select PersonID, Prefix, FirstName, MiddleName, LastName, Suffix, Credentials, FullName, NickName, SAMAccountName, EquitracNumber, SignificantOther, StatusID, SYSTEM_USER, Lastmodified, 'Record Updated'
from Deleted

You mustn't use waitfor in the trigger.

ASKER CERTIFIED SOLUTION

pivar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vnewman29

ASKER

Thanks that work perfectly.

vnewman29

ASKER

How do I close this question? I am satsify with the answer.