I want to audit which program/module changes the content of a table
table stock:
id: integer // unique key
location: string // where is it
in_stock: integer // How many do we have
wanted: integer // How many do we want to keep in stock
src: string // who and what changed it

My queries are supposed to be like
update stock set in_stock=in_stock+1,src='a=goods_received;u=johnso n'
where id=12345

I have a trigger which copies the values and a timestamp to stock_audit.

But if src is not set in a query
update stock set in_stock=in_stock-1 where id=12345

src keeps its old value, which is misleading.
So I want to put a default value in src if it is not explicitly set in the
query.

Can this be done?

I'm searching the app for every place where an update can take place, so
this should be a way to trace "unexpected" updates.

Leif

"Leif Neland" <leif (AT) neland (DOT) dk> writes:

In MSSQL, UPDATE triggers can use IF UPDATE(column) to check
whether the named column was explicitly updated. I suppose that
should work in a CASE expression too, e.g. CASE WHEN UPDATE(src)
THEN inserted.src ELSE 'default value' END.

I'm not sure whether UPDATE(column) in INSERT triggers returns
the correct value or just always TRUE. Although perhaps it does
not matter for your purposes.