2

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: Widget Table Contents

  • Can't you just get the session variable from a stored procedure and insert it that way? If you already have access to the data to set the session it seems like you should be able to send that directly into the stored proc as well. – Erik Mar 2 '17 at 21:59
  • What I'm really trying to do is just to be able to always have the updated_user_id set to whatever is in the session_context without having to rely on the application layer. With more than one application connecting into the same database, it is safer to me to leave that lower level logic in a single place, the database itself. In the example, the one update statement works, and the second doesn't. – Brian Jorden Mar 2 '17 at 23:26
  • Your question is rather long and, while generally I appreciate lots of details, in this case I don't have the time to digest everything right now. But I did recently write an article showing how to do this with triggers. I do spot that you said "evil" and "triggers" in the same sentence, so if you are against triggers based on principle alone or, less likely, good reasons, then I guess it won't be useful. – Aaron Bertrand Mar 3 '17 at 0:51
  • Aaron, let me first say, it was a little silly to classify triggers as inherently a bad idea as I've argued against that in the past as well. Although, given other options, I tend to lean away from them if possible due to some of the troubleshooting and debugging challenges. Also, thanks for the response and detailed article. The triggers issue was my first go-to, an instead of trigger to handle this, but those aren't allowed with temporal tables of course. Your after update may be a reasonable enough solution for my needs though, just not a huge fan of 2 records for every insert. – Brian Jorden Mar 3 '17 at 0:59
  • It just seems that generated always as row start works flawlessly. I'd love someone to tell me I'm dumb for missing a way to do something like generated always as default or generated always as (your_logic_here). Looks like you ran into and came up with a detailed/large solution to solve this same need. – Brian Jorden Mar 3 '17 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.