Can altering a computed column definition cause an update trigger to fire?

user_5678315 0
Viewed 137

Scenario:

  1. Table has 1 or more computed columns. Those columns have simple calculations based solely on the values of other columns in the table. Columns are marked as PERSISTED.
  2. Table has an AFTER INSERT, UPDATE trigger to INSERT or UPDATE rows to a second table.
  3. Second table schema is identical EXCEPT there are no computed columns.
  4. After deploy and table use (table now has production data in it), it is determined that the calculation is in error and needs to be corrected.

The developer expected that changing the computed column calculation would cause the trigger to fire (basically a DML operation). What was observed was more like a DDL operation. The source table showed the correct results of the changes to the calculation but the second table did not. The fact that the computed column values were corrected led the dev to conclude that the changes would be reflected in the other table.

It is my assertion that such a table change is similar to altering the type of a column. It is a DDL operation and does not qualify as an operation that would cause a DML trigger to fire. The problem is that I am inferring that result based on experience. I have not found documentation stating that this should be the expected result. Can anyone enlighten me?

THank you

calculated-columns triggers sql-server-2016 sql
2 Answers

An alter to the table is not an update to the data, so a trigger defined to fire on update will not fire when an alter is executed.

You must refresh the second table:

delete from table2;
insert into table2 select * from table1;
user_2561960

In the documentation for CREATE TRIGGER it says: (my bold)

Creates a DML, DDL, or logon trigger. A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event fires, whether table rows are affected or not. For more information, see DML Triggers.

DDL triggers run in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

And in that linked article it says:

Types of DML Triggers

AFTER trigger
AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed....

As you can see clearly, the only actions which fire DML triggers are those for statements. ALTER instead fires DDL triggers, which are very different.

And you can see from this quick mock-up in db<>fiddle that that is the case.

user_148689970
Related