Why is a trigger preventing update? 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 one 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) &lt;&gt; 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= 'someone@someemail.com' 
    WHERE   unrelatedEmailAddressColumn IS NOT NULL AND unrelatedEmailAddressColumn &lt;&gt; ''

Why should that update hang?
When the trigger is enabled, the script doesn't complete in 20 minutes. The statement before this one has persisted. When I disable the trigger the script completes in under a minute. The script runs on two databases - if I leave the trigger disabled on the second database then the first completes and the second hangs. It's a test environment with minimal users, the script is run immediately after a restore and sp_who2 doesn't show any unexpected connections.
The trigger should insert zero rows as it is auditing a column that is not updated by the statement. That said, I've discovered a second trigger exists after update on that table. The second trigger updates a column in any row being updated. This shouldn't conflict but I haven't explicitly set a trigger order, so I will either set an order or merge the triggers then test again. As an aside, I pulled the update statement out of the script file and run just that single update in SSMS with the same hanging effect.
I would recommend using this trigger template where possible.  change it to suit your needs, of course.
It uses an IF UPDATE(column) to short circuit if the DML doesn't reference the column the trigger cares about.  It also uses the EXCEPT clause for what I've found the fastest way to compare the INSERTED and DELETED tables.  Just add more columns to the lists to get more comparisons without needing to worry about NULL/not null values.
Just adding an IF UPDATE block would solve your issue immediately with the least change to the trigger logic.
As for why your statement hung; the INSERTED and DELETED triggers are heaps without indexes.  This makes joining them on the primary key an expensive operation, much less the comparison of the relevant column.  You can make it as fast as possible (as the below has done) but your best bet to avoid it seemingly hanging would be to batch your statements.  Do 5,000 rows at a time to avoid unnecessarily large strain on your tempdb.
A random additional note; we have gone a few steps further, we don't have audit triggers like this, but we use INSTEAD OF INSERT/DELETE to enforce the use of soft-delete in our application.  However, when we do need to hard delete records, we have another short circuit that checks context info for a special binary value, then just doesn't fire off for that value.  You could implement something like that as well.  Leave the trigger in place for the application, but special statements like huge table maintenance could just issue the proper context info and the trigger would ignore the statement.  You would then need to handle the logic for what the trigger was supposed to do on your own, but triggers are brute force.
ALTER TRIGGER trgMyTriggerName ON dbo.MyTableName 
AFTER UPDATE
AS
BEGIN

    /** Inside triggers, UPDATE(column) checks to make sure that the update statement 
        actually referenced the column.  NOTE that this does not mean the value actually 
        changed, just that the update (or whatever DML) statement referenced the column.

        If you use an ORM like entity framework then the whole row gets updated all the time
        anyways, regardless of if the value changed.  still, this is a good short circuit to
        prevent unnecessary logic from being executed.
        **/
    IF UPDATE(MyAuditColumn)
    BEGIN

        ;WITH CTE_Changed AS
            (
            SELECT I.PrimaryKeyID, I.AuditColumn
            FROM INSERTED AS I
            EXCEPT
            SELECT D.PrimaryKeyID, D.AuditColumn
            FROM DELETED AS D
            )
        INSERT INTO audit.MyTableName
            (transaction_type, transaction_user, primaryKeyId, someColumn)
        SELECT 'U', SYSTEM_USER, C.PrimaryKeyID, C.AuditColumn
        FROM CTE_Changed AS C

    END



END