SQL Update Trigger

I created a update trigger that will insert the old data (before update) into an Audit table and will also insert the new data (after update) into the Audit table.  I just can't get it to work properly.

ALTER TRIGGER [dbo].[updAuditClientName]
   ON  [dbo].[entity]
   For UPDATE
AS
    if Update (nm_full)
Begin        

      insert into AuditClientName(Log_type,id_Client, client_code, client_nm_full, lastmodified)
            select 'Old', c.id_client, c.client_code, e.nm_full, getDate()
            FROM         dbo.client as c INNER JOIN
                      dbo.entity as e ON c.id_client = e.id
            Join Deleted d on d.id = c.id_client

      insert into AuditClientName(Log_type,id_Client, client_code, client_nm_full, lastmodified)
            select 'New', c.id_client, c.client_code, e.nm_full, getDate()
            FROM         dbo.client as c INNER JOIN
                      dbo.entity as e ON c.id_client = e.id
            Join Inserted i on i.id = c.id_client

End
vnewman29Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
tlovieConnect With a Mentor Commented:
I think that the issue is the you are using e.nm_full - this is the data in the table - it won't be updated when the trigger if firing.  I think you should use this:

ALTER TRIGGER [dbo].[updAuditClientName]
   ON  [dbo].[entity]
   For UPDATE
AS
    if Update (nm_full)
Begin        

      insert into AuditClientName(Log_type,id_Client, client_code, client_nm_full, lastmodified)
            select 'Old', c.id_client, c.client_code, d.nm_full, getDate()
            FROM         dbo.client as c INNER JOIN
                      dbo.entity as e ON c.id_client = e.id
            Join Deleted d on d.id = c.id_client

      insert into AuditClientName(Log_type,id_Client, client_code, client_nm_full, lastmodified)
            select 'New', c.id_client, c.client_code, i.nm_full, getDate()
            FROM         dbo.client as c INNER JOIN
                      dbo.entity as e ON c.id_client = e.id
            Join Inserted i on i.id = c.id_client

End
0
 
tlovieCommented:
in fact, I don't think you need to join to the entity table at all:

ALTER TRIGGER [dbo].[updAuditClientName]
   ON  [dbo].[entity]
   For UPDATE
AS
    if Update (nm_full)
Begin        

      insert into AuditClientName(Log_type,id_Client, client_code, client_nm_full, lastmodified)
            select 'Old', c.id_client, c.client_code, d.nm_full, getDate()
            FROM         dbo.client as c INNER JOIN
            Deleted d on d.id = c.id_client

      insert into AuditClientName(Log_type,id_Client, client_code, client_nm_full, lastmodified)
            select 'New', c.id_client, c.client_code, i.nm_full, getDate()
            FROM         dbo.client as c INNER JOIN
            Inserted i on i.id = c.id_client

End
0
 
vnewman29Author Commented:
That worked beautifully.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.