Provide a mechanism for columns to automatically update themselves - by Adam Machanic

Status : 

 


235
2
Sign in
to vote
ID 3105516 Comments
Status Active Workarounds
Type Suggestion Repros 8
Opened 10/4/2016 6:25:35 AM
Access Restriction Public

Description

It is extremely common for databases to contain columns like "UpdatedDate," "UpdatedBy," etc. These columns work based on a trust model: As a database designer, I must trust that all of my downstream consumers will follow the intended rules, remembering to update the columns every single time any transaction occurs. And of course I have to trust that they'll put in the correct values. The only alternative to trust is to put ugly and potentially slow triggers in place to make sure everything happened the right way.

This situation needs to change, and this is especially prevalent now, thanks to SQL Server 2016 temporal tables. Many customers I've spoken to regarding these tables would really like to be able to audit who made the change. Some of them would like to audit which host name the change came from. And so on and so forth. The bottom line is that we need a flexible solution to allow users to declaratively model these situations.
Sign in to post a comment.
Posted by AlexGay on 1/11/2017 at 2:14 AM
This is a mechanic that is so common I automatically write my create table scripts with CreatedBy, CreatedDate, ModifiedBy, ModifiedDate columns, set defaults on the first two, and create a trigger to update the latter two. I like the idea of having a last updated host as well. It would be great to be able to get this without the extra work.
Posted by HenrikStaunPoulsen on 1/11/2017 at 2:09 AM
Until this has been added to SQL Server, I have developed a work-a-round for the UpdatedDate part of this request.
Please see http://dba.stackexchange.com/questions/98312/how-to-convert-timestamp-rowversion-to-a-date-and-time-in-sql-server
It is not too demanding for the SQL Server.

It would be much better to have this Connect Item solved as Fixed, but in the meantime, I need to see the date and time when a row changed.
Posted by Adam Machanic on 1/10/2017 at 5:00 PM
@R Herring

I'm not sure why this would cause issues with a partitioned view -- do you have a reference on that? -- but why not use table partitioning instead? At this point, post 2016 SP1, I can think of no reasons to keep using partitioned views.


--Adam
Posted by R Herring on 1/10/2017 at 4:38 PM
I like the idea of "bound expressions" similar to computed columns. Unfortunately that approach precludes including the table in an updateable partitioned view.
I would be nice to find a way to work around that restriction :)
Posted by Adam Machanic on 1/10/2017 at 1:17 PM
@Tutor_eBiz-Developer

Doesn't matter. This has nothing to do with credentials or availability thereof. It's a request for the ability to persist an arbitrary expression. Surely you can figure out how to get the "user name" appropriate for YOUR app in any real world scenario? Put it somewhere to make it available -- I assume this feature would follow similar rules as some other bound expressions in the product, with regard to limited scope, so that probably means session context -- and suddenly you can get it right in the expression you define on the column.


--Adam
Posted by Tutor_eBiz-Developer on 1/10/2017 at 1:12 PM
Can't imagine how this could work in the real world, since much is web/desktop/mobile/etc application specific (and has little to do with using MS credentials).

What I get by using timestamp or rowversion datatypes does what I need to understand that something is changed. It would be WONDERFUL to have something that would translate these datatype values into something usable.

ONE PIECE of this that IS DOABLE, is the UpdateDate/CreatedDate scenarios. These have nothing to do with whose credential package an application is using.

Posted by Another Rhoads on 1/10/2017 at 10:58 AM
Agreed: this would be better implemented within the database engine rather than application as it would also catch ad-hoc modifications.
Posted by Adam Machanic on 1/10/2017 at 9:51 AM
@Sanford

Just to clarify, I am not asking for an "automatically store user name" feature. I'm asking for "automatically store whatever I want, based on whatever expression I provide." So the fact that the user name may or may not be available is not really relevant.

That said, there are plenty of workarounds for various situations. I imagine that if you're using a shared connection, you can get some notion of "user" some other way. For example, you can store the application's idea of the user name in session context, and allow the auto update column expression to grab it from there. Lots of ways to make this work -- what we need is the functionality to do it!


--Adam
Posted by Sanford Olson on 1/10/2017 at 9:43 AM
I would love this too. The issue is that many/most applications don't use Windows Auth in the connection to SQL Server (connection pooling issues, etc), so SQL can't get the User information
Posted by Jeff Tedrow on 10/4/2016 at 2:15 PM
This is similar to my feedback located here: https://connect.microsoft.com/SQLServer/feedback/details/2769130/sql-2016-temporal-tables-with-triggers. The solution I suggest is similar to this, and could be used in many different cases developing using SQL server.