I have a script that runs weekly. It updates multiple tables in my database. It turns out one statement attempts to update 150,000 rows on a table that contains 250,000 rows. That failed this week after I'd introduced a trigger on that table.
The trigger is auditing 1 column on that table - if the value in that column changes, a record is inserted into a table with identical name but in the 'audit' schema.
ALTER trigger [dbo].[tau_myTableName] on [dbo].[myTableName] after update as if exists ( select 1 from inserted i join deleted d on i.primaryKeyId = d.primaryKeyId where isNull(i.someColumn,-1) <> isNull(d.someColumn,-1) ) insert into audit.myTableName (transaction_type, transaction_user, primaryKeyId, someColumn) select 'U', SYSTEM_USER, primaryKeyId, someColumn from inserted;
The update statement that was hanging, is:
UPDATE myTableName SET unrelatedEmailAddressColumn= 'email@example.com' WHERE unrelatedEmailAddressColumn IS NOT NULL AND unrelatedEmailAddressColumn <> ''
Why should that update hang?
Ps. SQL server 2016.