Trigger Performance for Simple/Common Scenarios - by Jason Kresowaty

Status : 

 


46
2
Sign in
to vote
ID 355461 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 7/8/2008 5:43:35 PM
Duplicates 406485 Access Restriction Public

Description

This is a suggestion to improve the performance (and ease of use) of some common scenarios for using triggers.

Often, a trigger is implemented to accomplish one or more of the following:

1. If the row is being INSERTed, force the values of certain columns to be the results of expressions.
2. If the row is being UPDATEd, force the values of certain columns to be the results of expressions.
3. In either case, prevent certain columns from being modified by the user.

For example, consider a table with the following columns:

CreateUser
CreateTime
UpdateUser
UpdateTime

During an INSERT of a row into such a table:

CreateUser - should force to ORIGINAL_LOGIN()
CreateTime - should force to GETDATE()
UpdateUser - should force to same as CreateUser
UpdateTime - should force to same as CreateTime

During an UPDATE of a row in such a table:

CreateUser - should not be changeable
CreateTime - should not be changeable
UpdateUser - should force to ORIGINAL_LOGIN()
UpdateTime - should force to GETDATE()

These tasks can currently be accomplished using INSTEAD OF or AFTER triggers. However, triggers support many diverse scenarios and thus incur superfluous performance overhead (including version store usage) for these simple use cases, especially when many rows are affected with one statement.

This is a request for a new feature that optimizes this kind of operation.

For example, for a column, add the ability to specify an expression, like a DEFAULT, to which the column is forced on an INSERT or UPDATE. (In the case of INSERT, this is very much like DEFAULT, except that is has precedence over any new data for the column that might have been specified by the user in the SQL statement.)

For example:

CREATE TABLE Test
(
	ID INT PRIMARY KEY IDENTITY NOT NULL,
	CreateUser sysname NOT NULL ON INSERT ORIGINAL_LOGIN() ON UPDATE UNCHANGEABLE,
	CreateTime DATETIME NOT NULL ON INSERT GETDATE() ON UPDATE UNCHANGEABLE,
	UpdateUser sysname NOT NULL ON INSERT ORIGINAL_LOGIN() ON UPDATE ORIGINAL_LOGIN(),
	UpdateTime DATETIME NOT NULL ON INSERT GETDATE() ON UPDATE GETDATE()
)

Ideally, GETDATE() would return the same date and time for all invocations in this context. That is, when CreateTime and UpdateTime are both being forced, they would get the same date and time.

UNCHANGEABLE should silently ignore any attempt to change the data from the old value. Alternatively, it could error. Consider another syntax such as "ON UPDATE ERROR" for this purpose. Such an error should be raised only if the data value is actually being changed. Performing an UPDATE equal to the current value should not trigger the error.

Also consider the following advanced scenarios:

1. Provide access to both old and new images of column values in such expressions. The "new" values would be per the SQL statement, but these would not actually be written to the database yet so that the ON INSERT/UPDATE expressions could override them. For example, the syntax old.CreateUser or new.UpdateTime.

2. Allow UDFs, including CLR, to be called in such expressions.
Sign in to post a comment.
Posted by Traderhut Games on 4/1/2016 at 1:21 PM
I've done auto-set updated on / createdOn fields on other databases decades ago. I think that MS SQL should get with the 90's and have this simple feature implemented. If it was implemented at the code that did the row update there would be zero impact on performance. (when rounded to the nearest 0.0001%)
However, I've worked at companies where there were rules against triggers (period. Not even if it will save hours per week of people working tickets caused by data corruptions caused by not having a trigger to fix something. - And I understand why they wanted to avoid them at all cost.) So, this would be a great way for MS SQL to catch up with the other SQL manufactures.
Posted by lslmustang on 7/19/2013 at 10:20 AM
I am particularly interested in an automatic LastUpdate datetime field vs. having to use an update trigger to populate this field. We use SP's and yes, we could add the code to populate it to every sp that updates a record. But what if someone need to edit the record, to fix a problem or what ever reason, doesn't update it via the SP but through SQL. There is no tracking of when that change happened without a trigger. It's very helpful in troubleshooting.
We want to avoid using triggers for performance reasons.
Inserts are completely solved with defaults so I have not issues there, we just want to know anytime that someone updates a table, via the SP or via SSMS or via a hacker.... (lets hope not)
Posted by Kent Waldrop on 8/23/2011 at 7:08 AM
I just came out here to see if something like this has been suggested and to make the suggestion if not already made. This scenario has come up for me many times in the past. I fully understand that either triggers or defaults can be used to get some of this done; however, defaults alone don't do the whole job. The problem with triggers are that the cause an additional update to be invoked against the target table. I would definitely use such a feature if it were available.

Kent
Posted by Umachandar [MSFT] on 3/10/2011 at 6:48 PM
Hello Jason,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to reopen it and we will take another look.

--
Umachandar, SQL Programmability Team
Posted by Erland Sommarskog on 8/31/2008 at 3:57 AM
I think this is a very interesting suggestion!

Jason calls for three things, and the first is something that is already possible to achieve with a CHECK constraint.
However a CHECK constraint cannot enforce the rule on updates, since CHECK constraints only fires if the colunm is
touched, see this repro:

CREATE TABLE bludder
(a int NOT NULL PRIMARY KEY,
    b varchar(40) NOT NULL,
    moddate datetime2(3) NOT NULL
     DEFAULT sysdatetime()
     CHECK (abs(datediff(MS, moddate, sysdatetime())) < 20),
    moduser sysname NOT NULL
     DEFAULT original_login()
     CHECK (moduser = original_login())
)    
go
INSERT bludder(a, b) VALUES (1, 'No 1')
INSERT bludder(a, b, moddate, moduser) VALUES
(2, 'You can''t cheat', '1999-09-09', 'Someoneelse')    
INSERT bludder(a, b) VALUES (2, 'OK, no cheating')
SELECT * FROM bludder
go
CREATE USER someoneelse WITHOUT LOGIN
GRANT UPDATE, INSERT, DELETE, SELECT ON bludder TO someoneelse
go
EXECUTE AS USER = 'someoneelse'
go
WAITFOR DELAY '00:00:05'
UPDATE bludder
SET    b = 'We want user and timestamp to change now'
WHERE a = 2
go
REVERT
go
SELECT * FROM bludder
go
DROP USER someoneelse
DROP TABLE bludder
                    
I think Jason points to something very important here. UC seems to think that this a special case that does not warrant
special consideration. True, being able to enforce a constraint for a column even when it's not updated is likely to be
confined to auditing, but I really get worried if Microsoft considers auditing to be a fringe case. The exact syntax can be
disputed; it seems more natural to extend the syntax for CHECK and/or DEFAULT constraints. In any case, it's
extremely important to understand that merely DEFAULT constraints does not cut it for updates. The suggestion is
that you should include DEFAULT keyword in your updates, but the essenece of Jason's suggestion is that we want
to enforce users and programmers to actually use that DEFAULT! Today, the only way to this is a trigger, which certainly
has performance considerations. True, with BEFORE triggers, the performance can be mitigated, assuming a good
implementation which permits us to modify the inserted rows. Nevertheless, DRI is so much simpler than trigger,
that DRI is still very much desireable.

Finally, the last suggestion is something I've suggested myself in https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=346200.
I like to stress that this is something which goes far beyond auditing, and is least of all any fringe case. Rather,
I think this is a very common scenario, which rarely is enforced today because it's too difficult to do. (And you assume
the application to do it right.) I find the question on the difference between UNCHANGEABLE and GRANT/DENY
amazing. GRANT/DENY is about permission. Since when did permissions have anything to do with business rules?
UNCHANGEABLE is a constraint and has nothing to do with permissions. What is important, though, is that
UNCHANGEABLE should have a WHEN clause, since often a column can be changeable when the item is in
an initial state. For instance, on an Order a lot of things can be permitted to changed, until the order has been
shipped, but once shipped, it should be frozen. This is also covered in my Connect request.

Posted by Jason Kresowaty on 7/11/2008 at 3:43 PM
Thanks, just to add a little more detail regarding one of your comments:

> You can use the DEFAULT mechanism and modify your INSERT/UPDATE statements accordingly.

Unfortunately, this assumes all apps/users will follow this convention. This is too hard to ensure both from a backwards compatibility and development standpoint. It is an area in which triggers excel (except for the performance implication). If someone or some app does not update these columns, the user/time of the last update to the row loses its meaning. The last update time is essential for data synchronization purposes. Also, if someone fails to correctly update the last user column, we cannot tell whether it was our app or an admin (again, a person who does NOT work for us) going in and modifying the data directly. The latter sounds like an "auditing" purpose, but comprehensive auditing is not the intent; rather, what is desired is a very low overhead, automatic way to gather the update time and user without any modification of SQL statements.

Sometimes "big" updates come from apps, not just admins. So it is important to have high performance updating of these columns even when not under admin control.

I realize that an admin can always get around things (and needs to do so under certain circumstances). Even with the ON INSERT / ON UPDATE syntax that I suggest, there would still need to be a way to disable it because an admin sometimes would need to load data "exactly", for example when copying it between databases, files, etc. (But even if the admin had to do an ALTER COLUMN to temporarily take the ON INSERT / ON UPDATE clauses away, this would be fine.)

That is all.
Posted by Umachandar [MSFT] on 7/11/2008 at 12:16 PM
Hi,
Regarding comment below:

>> Sometimes there are tables with millions of rows. Usually these rows are updated by apps which update
>> only a small number at a time. Triggers work fine. However, sometimes a larger update needs to be
>> done by batch processing. Turning the triggers off is bad for administrative and locking reasons. So, I
>> would like SQL Server to perform well in this situation without the need to turn the triggers off. My
>> proposed solution is to eliminate the need for triggers when implementing some common scenarios.

As I suggested before, you don't need new syntax to provide this functionality. You can use the DEFAULT mechanism and modify your INSERT/UPDATE statements accordingly. I am not sure why you would use trigger to maintain these audit columns. Granted you need to specify the DEFAULT keyword in the SET list of the UPDATE statements but that is not reason enough to use triggers. And in the case where you do want to use triggers, it is trivial to rewrite logic of trigger to skip processing for certain administrative tasks. For example, you could add the line below at the top of the trigger:

if object_id('tempdb.dbo.#skip_my_trigger') is not null
return;

Now in your admin routines, simply create a temporary table before doing any of your transactions.

create table #skip_my_trigger( i int );

There are other ways to achieve this by using CONTEXT_INFO. You can store some global state to signal other modules.
We have following mechanisms to alter row data at runtime:

1. Defaults
2. Instead of triggers
3. After triggers

All of the above are general purpose mechanisms. Adding yet another mechanism with more rules to solve a specific scenario doesn't seem to be that useful. We have to think carefully about introducing multiple features with similar capabilities since we have made that mistake in the past and it is very hard to unify them at a later point in time. There have been requests for BEFORE triggers also. Btw, triggers are part of the ANSI SQL standard. They don't have INSTEAD OF triggers yet and our syntax is much different.
Anyway, I will keep this suggestion active to see if there are any votes or other community feedback. And based on the votes, we will take this into consideration for a future version of SQL Server.

--
Umachandar, SQL Programmability Team
Posted by Jason Kresowaty on 7/10/2008 at 3:59 PM
Hi, thank you for your comprehensive response. I hope I can convince you to give this issue a bit more consideration before closing it.

Below are my responses to your questions:

> 1. Will this be compatible with specification of DEFAULT on the column also?

Yes. However, if ON INSERT is specified it has precedence over DEFAULT, so the DEFAULT would not have an effect on the INSERT. (Note: If ON UPDATE is not specified, the DEFAULT could still be meaningfully used by a SET = DEFAULT statement.)

> 3. What happens if the column is explicitly present in the INSERT/UPDATE statement and DEFAULT keyword is used?

I think I covered that one. I would prefer that the new value specified by the INSERT or UPDATE statement is ignored as is trumped by the ON INSERT or ON UPDATE expression result. An alternative implementation would error if the value specified by the statement disagrees with what would be specified by the ON INSERT or ON UPDATE clause. Here is the important thing: if the value set by the SQL statement euqals the value determined by evaluation of the ON INSERT or ON UPDATE clause, this should not be an error. This is a relatively minor issue, but my thoughts of why this should ignore the statement values is to provide better backwards compatibility with applications and parity with triggers; i.e., the ON INSERT and ON UPDATE have the same effects as an equivalent AFTER UPDATE trigger.

2. What happens if the column is explicitly present in the INSERT/UPDATE statement and DEFAULT keyword is used?

Same as above. The DEFAULT is not used because the ON INSERT or ON DELETE clause trumps it. Rationale is so that the effect is the same as that of an AFTER UPDATE trigger.

4. Would you make some columns updateable only for certain users or under certain circumstances? Think of scenario where you want to correct certain entries based on audit.

Not in the declarative syntax. Perhaps this could be a CLR integration point. More on this further down.

5. For the UNCHANGEABLE option, would you allow some UDF or expression to be specified?

Not sure what you mean.

6. The column definition syntax in the CREATE TABLE DDL is much more complex and harder to understand. Lot of the rules for these type of audit column(s) are not declarative in nature and trying to provide a syntax that works for small number of cases is not good enough.

The syntax might be quite good if UDFs are allowed and good CLR integration is provided. For example, a CLR integration that intercepts the incoming values for all of the columns in the row and allows a "last minute" change of data values or the throwing of an error. The CLR integration must be good enough so that it works adequately when the number of rows affected is many millions.

Note that a trigger might still be needed to enforce some security rules, but as long as all of the value updating is done by the ON INSERT or ON UPDATE expression, there might still be a performance win due to the elimination of the "double update" problem.

7. The syntax is not compatible with ANSI SQL standards or other database systems

Neither are T-SQL TRIGGERs.

8. Typically in a database system, the decision to whether make certain columns updateable or not are more complex that a binary rule and I don't see how it is possible to enable it easily at the DDL level. Moreover the rules might change over time and it is better maintained at the application layer

This is, of course, a common design debate -- when does logic go in the database versus in the application. Since people are split on this issue, supporting both design strategies is important. With all of the CLR and XML features, it seems like the SQL team has been working to add more and more "application" functionality to the database server.

9. How is the UNCHANGEABLE option different from GRANT/DENY UPDATE permission on the column(s)? If the UPDATE is happening through SP then you don't deal with explicit permissions also in the common case. This just adds another layer of manageability on top of it which seems unnecessary

It's almost like denying the update, except the UPDATE statement is not prevented from specifying the column. If the UPDATE specifies a value different than what is already in the column, the column is silently not updated. In other words, UNCHANGEABLE is like an AFTER UPDATE trigger that sets the value of the column to the value of the column in the "old" image, thus rendering it "unchanged".

Finally, let me bring this down to earth... Sometimes there are tables with millions of rows. Usually these rows are updated by apps which update only a small number at a time. Triggers work fine. However, sometimes a larger update needs to be done by batch processing. Turning the triggers off is bad for administrative and locking reasons. So, I would like SQL Server to perform well in this situation without the need to turn the triggers off. My proposed solution is to eliminate the need for triggers when implementing some common scenarios.

Feel free to email me if I could be of further assistance in explaining my needs here. The automatic maintenace of simple tracking columns like the ones I specify has been a performance sore spot for many years. Thanks.
Posted by Umachandar [MSFT] on 7/10/2008 at 2:54 PM
Hi jasonkres,
Thanks for your feature suggestions. Your main issue seem to be the performance of trigger-based mechanism to maintain created/modified user/time columns. The recommended mechanism is to create appropriate DEFAULTs on the columns and use the DEFAULT keyword in the UPDATE statement. Why are you using triggers to maintain these columns? It is because you want to avoid specifying these columns in the UPDATE statement with the DEFAULT keyword like:

UPDATE your_table
SET UpdateUser = DEFAULT, UpdateTime = DEFAULT
....
WHERE ...;

Your suggestions seem to be just syntactic sugar in addition to some requirements on whether a column is updateable or not. Below are some of the questions / concerns I have with your feature suggestions:

1. Will this be compatible with specification of DEFAULT on the column also?
2. What happens if the column is explicitly present in the INSERT/UPDATE statement and DEFAULT keyword is used?
3. What happens if the column is explicitly present in the INSERT/UPDATE statement and a value is specified?
4. Would you make some columns updateable only for certain users or under certain circumstances? Think of scenario where you want to correct certain entries based on audit.
5. For the UNCHANGEABLE option, would you allow some UDF or expression to be specified?
6. The column definition syntax in the CREATE TABLE DDL is much more complex and harder to understand. Lot of the rules for these type of audit column(s) are not declarative in nature and trying to provide a syntax that works for small number of cases is not good enough.
7. The syntax is not compatible with ANSI SQL standards or other database systems
8. Typically in a database system, the decision to whether make certain columns updateable or not are more complex that a binary rule and I don't see how it is possible to enable it easily at the DDL level. Moreover the rules might change over time and it is better maintained at the application layer
9. How is the UNCHANGEABLE option different from GRANT/DENY UPDATE permission on the column(s)? If the UPDATE is happening through SP then you don't deal with explicit permissions also in the common case. This just adds another layer of manageability on top of it which seems unnecessary

I am inclined to close this request as "Won't Fix" since I don't see this as a feature we would implement. Introducing syntax in the language to solve a limited set of the audit tracking column(s) is not something I could push for in the product. There is a good programming technique available to implement this today via INSERT/UPDATE statement and this adds little value on top of it. And the rules for tracking audit information is so diverse and complicated that it will be hard to come up with a declarative syntax that covers the common scenarios.

Thanks
Umachandar, SQL Programmability Team