Provide a mechanism for columns to automatically update themselves
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.
Upvotes: 249<=-=Oct 4 2016 2:15PM=-=>
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.<=-=Jan 10 2017 9:43AM=-=>
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<=-=Jan 10 2017 9:51AM=-=>
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<=-=Jan 10 2017 10:58AM=-=>
Agreed: this would be better implemented within the database engine rather than application as it would also catch ad-hoc modifications.<=-=Jan 10 2017 1:12PM=-=>
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.<=-=Jan 10 2017 1:17PM=-=>
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<=-=Jan 10 2017 4:38PM=-=>
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 :)
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<=-=Jan 11 2017 2:09AM=-=>
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.<=-=Jan 11 2017 2:14AM=-=>
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.
@(Anonymous, June 07, 2018 03:58/03:57). You are missing the point. Since your MyTest table only has defaults, a malicious/errant developer can still force whatever data they want into the table. The point is to define the table so that ONLY SQL Server's internal processes can populate the values (much like an IDENTITY column). This way the database designer can fully enforce true, run-time values.
@JediSQL that functionality exists. It's called a check constraint.
CREATE TABLE MyTest
UpdatedBy SYSNAME DEFAULT (SUSER_NAME()),
UpdatedFrom SYSNAME DEFAULT (HOST_NAME()),
UpdatedWhen DATETIME DEFAULT (GETDATE()));
INSERT INTO MyTest (MyValue) VALUES ('New Row');
SET MyValue = 'Updated Row',
UpdatedBy = DEFAULT,
UpdatedFrom = DEFAULT,
UpdatedWhen = DEFAULT;
I have thought about this kind of thing a lot myself. There should be one expression for INSERT and one expression for UPDATE (and optional single expression for INSERT, UPDATE). In the constraint definition (or however it might be implemented), there should be an option where the column will IGNORE or REJECT any values explicitly provided to an INSERT or UPDATE statement. IGNORE would be like IGNORE_DUP_KEY where an inert warning is printed, and REJECT would cause a level 16 RAISERROR and block the statement. This would give the DBA full control and be used prevent errant/malicious code from creating deceptive audit data.