We help IT Professionals succeed at work.
Get Started

SQL Update Trigger

vnewman29
vnewman29 asked
on
380 Views
Last Modified: 2012-05-10
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?



Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE