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

Make it possible to refer to the affected columns in an INSTEAD OF INSERT/UPDATE trigger as a set

This suggestion that adds to the suggestion in
https://feedback.azure.com/forums/908035-sql-server/suggestions/32896687-before-trigger-behavior-with-execute-original-stat
which calls for a method to redo the statement in an INSTEAD OF trigger.

That suggestion calls for a single statement. However, that solution overlooks the case that you may want to modify other columns in you trigger. A simple example is a last_updated column, which you may want to set in an INSTEAD OF UPDATE trigger. But you may also have a column which is set from more complex rules that you cannot use in a DEFAULT constraint, therefore you need to do this on INSERT as well.

The suggestion EXECUTE ORIGINAL_STATEMENT is great when all you want to do is reissue the statement that fired the trigger. Not the least is true for INSTEAD OF DDL triggers (which we need badly.)

But this syntax gives little room for specifying extra columns - or overriding columns that were specified in the original statement. Here is a suggested syntax:

INSERT tbl ($trigger_set, col1, col2)
SELECT $trigger_set, 'some_value', other_value
FROM inserted

UPDATE tbl
SET $trigger_set = i.$trigger_set,
col1 = 'some_value',
col2 = 'othervalue'
FROM tbl t
JOIN inserted i ON t.keycol = i.keycol

$trigger_set would be all columns mentioned in the original statement. However, if a column is explicitly mentioned in the statement, this excludes the column from $trigger_set.

You may ask: "can't you already do this in an AFTER trigger", and indeed I can. At a higher cost, since rows have to be modified multiple times.

2 votes
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

    Erland Sommarskog shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    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