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
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 ($triggerset, col1, col2)
SELECT $triggerset, 'somevalue', othervalue
SET $triggerset = i.$triggerset,
col1 = 'some_value',
col2 = 'othervalue'
FROM tbl t
JOIN inserted i ON t.keycol = i.keycol
$triggerset would be all columns mentioned in the original statement. However, if a column is explicitly mentioned in the statement, this excludes the column from $triggerset.
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.