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:
- If the row is being INSERTed, force the values of certain columns to be the results of expressions.
- If the row is being UPDATEd, force the values of certain columns to be the results of expressions.
- In either case, prevent certain columns from being modified by the user.
For example, consider a table with the following columns:
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.)
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:
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.
Allow UDFs, including CLR, to be called in such expressions.