Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Trigger Performance for Simple/Common Scenarios

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.

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 31

    <=-=Jul 10 2008 2:54PM=-=>

    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

    <=-=Jul 10 2008 3:59PM=-=>

    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.

    <=-=Jul 11 2008 12:16PM=-=>

    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

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base