Audit Trigger

Transact-SQL
1

Trying to put a trigger against a view to write Audit records when changes are made to the fields.
I'm not able to get any records written to my iminvlocaud_sql table.

Just looking for update changes against price, std_cost, target_margin

ALTER TRIGGER [dbo].[Audit]
on [dbo].[QIVItemPrice]
instead of update, delete, insert
AS
Begin
set nocount on;
insert into iminvlocaud_sql(
item_no,
loc,
price,
std_cost,
target_margin
)

select
d.item_no,
d.loc,
d.price,
d.std_cost,
d.target_margin
from deleted d join inserted_sql i on d.item_no = i.item_no and
d.loc = i.loc
where i.price <> d.price or i.std_cost <> d.std_cost or
i.target_margin <> d.target_margin

2

Why are you using an instead of trigger? Instead of means that the DML on the original table is not going to happen. You likely want a regular after trigger.

What is inserted_sql? I believe you want inserted instead.

3

I read you have to do an instead of on a SQL view.

_sql was a typo when putting this into the forum.

4

I missed that it was a view. You'll need to still do the insert/update/delete inside the trigger for whatever base table the view is hitting, then you can do your audit work.