Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Costly update trigger -70,000,000 logical reads for 30,000 rows updated! RE: Costly update trigger -70,000,000 logical reads for 30,000 rows updated!

  • February 2, 2009 at 8:22 am

    #936463

    Peso (2/2/2009)

    No need to check for UPDATE, DELETE or INSERT.

    Trigger is designed for UPDATE.

    Also, the trigger is nesting itself by updating the table for which the trigger ís triggering on...

    Use this

    CREATE TRIGGER[dbo].[TG_U_UpdTable]

    ON[dbo].[UpdTable]

    AFTERUPDATE

    AS

    IF UPDATE(db_updateDate) OR UPDATE(db_updateBy)

    RETURN

    UPDATEd

    SETd.db_updateDate = GETDATE(),

    d.db_updateBy = SYSTEM_USER

    FROMdbo.UpdTable AS d

    INNER JOINinserted AS i ON i.UpdTableOID = d.UpdTableOID

    Thanks for the suggestion, yes, the code that checks for type of DML is redundant.

    Can you explain what the following does though, it's not clear to me:

    IF UPDATE(db_updateDate) OR UPDATE(db_updateBy)

    RETURN

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]