asked on

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

ASKER CERTIFIED SOLUTION

tlovie

membership

This solution is only available to members.

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

Start Free Trial

tlovie

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

vnewman29

ASKER

That worked beautifully.