Something similar to GENERATED ALWAYS for a DEFAULT? (SQL Server / Azure SQL)

Question

Question: Is there any way to force a column to always use the default value definition? I would like to always have the user_id populated on an insert/update (I know delete might be a bit different) even if I didn't specify it in the SQL statement. The rowversion type and generated always clauses already do this for other types of data.

Environment: We're currently using Azure SQL Database in a multi-tenant setup with row-level-security and all user data resides in temporal-tables. Only explaining that because it adds a ton of restrictions to what I really can do and things I'd like to avoid if possible.

Setup: Our user generated data all lands in a series of tables that all have the same boilerplate definition. I've stripped things way down and obviously made this example far more generic than the real version, but I wanted to give something a bit more "real world" than foo/bar. We rely on a series of security predicates to enforce row-level-security and each end user's login to the application results in a unique SQL session. We establish the tenant_id and user_id within the session_context as a simple int value. That part is solid and can be relied on as always available prior to any actual user requests.

Goal: Ideally, I would like the updated_user_id column to be always populated with the default that is fetched from the session_context. That would be similar to how the updated_datetime is set from the generated always clause (part of the temporal table definition).

Workarounds: We can try to rely on the API/GUI layers to send over the right value or the default keyword, and then catch the issue with check constraints as per the example. Previously before hacking in a solution to make the table constraint always fire (by including row_version), we were doing horrible things with checking in triggers...

Notes: Unfortunately the idea of an INSTEAD OF trigger is simply out due to the base table being a system versioned temporal table. We would otherwise have to run all operations through a layer of views and/or create stored procs for any insert/update/delete statements (has been considered though).

Example Setup:

/*
alter table work.widget set (system_versioning = off)
drop table work.widget
drop table work.old_widget
*/

create table work.widget (
  widget_id   int identity primary key,
  widget_desc varchar(4000) null,

  row_version     rowversion not null,
  tenant_id       int not null default (cast(session_context(N'tenant_id') as int)),
  created_user_id int not null default (cast(session_context(N'user_id')   as int)),
  updated_user_id int not null default (cast(session_context(N'user_id')   as int)),

  created_datetime datetime2(7) not null default (sysdatetime()),
  updated_datetime datetime2(7) generated always as row start not null,
  expired_datetime datetime2(7) generated always as row end   not null,

  constraint check_widget_session check (
    (cast(session_context(N'tenant_id')   as int) = tenant_id)
    and (cast(session_context(N'user_id') as int) = updated_user_id)
    and (row_version > 0) -- doesn't ALWAYS fire without this
  ),

  period for system_time (updated_datetime, expired_datetime)
) with (
  system_versioning = on (
    history_table = work.old_widget,
    data_consistency_check = on
  )
)

Example Test:

exec sp_set_session_context N'tenant_id', 17
exec sp_set_session_context N'user_id',   22

insert into work.widget (widget_desc) values
  ('Big Thing - (original insert from user_id 22)'),
  ('Little Thing - (original insert from user_id 22)'),
  ('Other Thing - (original insert from user_id 22)')

exec sp_set_session_context N'user_id', 981

insert into work.widget (widget_desc) values
  ('Another Thing - (second insert from user_id 981)')

update work.widget
set widget_desc = 'Altered Thing - (updated by user_id 981)',
    updated_user_id = default
where widget_desc = 'Little Thing - (original insert from user_id 22)'

-- this statement will generate an error due to the check constraint
update work.widget
set widget_desc = 'Changed Thing - (updated by user_id 981)'
where widget_desc = 'Other Thing - (original insert from user_id 22)'


select *
from work.widget
for system_time all
order by widget_id, row_version

Example Results: