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
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).
/* 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 ) )
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