0

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= 'someone@someemail.com' 
    WHERE   unrelatedEmailAddressColumn IS NOT NULL AND unrelatedEmailAddressColumn <> ''

Why should that update hang?

Ps. SQL server 2016.

  • Why do you think the trigger is making it hang? Maybe it's something else, like blocking from another session. – Aaron Bertrand Feb 4 at 1:25
  • Because 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. – youcantryreachingme Feb 4 at 1:28
  • So what is the wait type while the script is running? How many rows does your update statement affect? What % of the table? If you are inserting 250,000 rows into an audit table, you can't expect that to return immediately. – Aaron Bertrand Feb 4 at 1:54
  • Thanks Aaron. 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. – youcantryreachingme Feb 4 at 2:30
  • The trigger doesn’t “audit a column” - if you update 250,000 rows, there will be 250,000 rows in inserted, 250,000 rows in deleted, and your exists then has to scan 250,000 rows in a join, even if no rows could ever match. So it's more about populating and then reading all those rows, not so much about the eventual insert of zero rows. – Aaron Bertrand Feb 4 at 2:41
0

I think the whole script matter now,

Also you should use Try Catch,Begin Trans.

Trigger script can be rewritten as below ,for 2 reasons.

First , Logic is wrong

Second <> operator are often slow

        insert into audit.myTableName (transaction_type, transaction_user, primaryKeyId, someColumn)
        select
        'U', SYSTEM_USER, primaryKeyId, someColumn
        from inserted i
where Not Exists(select 1 from deleted d where  i.primaryKeyId = d.primaryKeyId
        where   isNull(i.someColumn,-1) = isNull(d.someColumn,-1)
)

Notice how instead of <>, I use Not Exists

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.