Update after trigger on a table in sql

Question

My update trigger on a table looks like this.

CREATE trigger [HumanResources].[tr_update_department]
on [HumanResources].[Department]
for update
as
begin
insert into HumanResources.tbl_Department_audit
select * from deleted;
end
GO

I am basically looking for updated row information in audit table called tbl_Department_audit.

I have tested the the trigger, whenever I am updating a single row in Department table, two rows with the before update and after update row information inserted in to the tbl_Department_audit.

What I expect is to audit only the one row either before update data or after update data, not both.

@maxiwheat : Triggered table have only one trigger that is the above one. — Venkat
@jcwrequests Single record may update in several times , we want to look all the updates on the perticular record. So distinct is not a good idea in this case. — Venkat
if single record may update several times, wouldn't each time be different from the previous, why do you need auditing updates that didn't change anything? — Luis LL

PeekNCook PeekNCook · Accepted Answer · 2013-09-23T15:39:49

Please change the trigger script to as below

CREATE trigger [HumanResources].[tr_update_department]
on [HumanResources].[Department]
after update
as
begin
insert into HumanResources.tbl_Department_audit
select * from deleted;
end
GO

This should insert only one record after the update is done.

Update after trigger on a table in sql

1 Answers