Auditing: How to write SQL user to a table WITHOUT a trigger

Question

I'm trying to find a way to automatically write the value of ORIGINAL_LOGIN() to a column within a table whenever a record is inserted or updated. Note that this needs to be in the SQL layer, so that it can cater for direct SQL updates to the DB as well as updates from the application.

In the past I've used INSTEAD OF triggers to achieve this kind of thing, but I'm using Temporal Tables to maintain the audit history of each of the records and they won't allow the use of triggers, so that option is out.

Whilst researching this and playing with options, I tried using a computed field in the table, which worked fine on the History temporal table, but in the "current" table it always returns the name of the querying user, rather than the user who last wrote the record.

Is there any way I can have the database automatically set the value of a field at the time it is written that doesn't involve triggers?

Answer 1

Maybe you can try with a default value

create table #test (id int, name varchar(50), username varchar(50) default (system_user))

insert into #test (id, name) values (1, 'john'), (2, 'rambo')

select * from #test

in my case this returns

id name  username
1  john  sa
2  rambo sa