I have joined a team where ALL tables have triggers on them. It seems primarily for auditing e.g., who created or updated it and dates.

What I have learnt thus far is that they can be bad and watching Brent Ozar's stuff, this all makes me worry. I've never used them and tended to keep clear of them, setting this data normally in the API side if needs be.

1. Is this equivalent to 2 updates instead of one?
2. Also it seem to update all the columns even if they don't change!? does that look right? What I mean is that if I update a row and set a new Price, will that also trigger a change on the other fields that have not changed, and hence also cause the index to be changed, when nothing changed say on the StartDate.

Based of question 2. I have done a test and it does seem to tell the index it has changed even though I didn't change the columns in that index! So in this case I said update the Price and only the price, but I imagine the trigger tells it that everything in that column has changed. That particular index does not have the Price in it.

This all makes me very worried!

Does this mean that if I have any indexes on the table; any update will trigger a change in all of them, as all the columns get changed? and hence cause a knock on issue having to constantly update indexes?

Similar to this example one:

ALTER TRIGGER [dbo].[MyTrigger]
ON [dbo].[MyTable]
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON

    IF NOT EXISTS (
        -- We have a check here to stop inserting overlapping data
    )
    BEGIN
        UPDATE 
            p
        SET           
            p.MyId = i.MyId,
            p.StartDate = i.StartDate,
            p.EndDate = i.EndDate,
            p.Price = i.Price,              
            p.CreatedBy = i.CreatedBy,
            p.CreatedDate = i.CreatedDate,
            p.AuditUser = CASE WHEN UPDATE(AuditUser) THEN i.AuditUser ELSE SUSER_SNAME() END,
            p.AuditDate = SYSUTCDATETIME(),
            p.AuditApp = RTRIM(ISNULL(APP_NAME(),''))
        FROM 
            PriceValues p
        INNER JOIN
            inserted i ON p.Id = i.Id
    END
    ELSE
    BEGIN
        RAISERROR ('Cant update due to overlap', 16, 101);  
    END
END

I'm looking for consensus on what is the best angle of attack if this is going to start biting us. If this is an issue I need details on why and what we can do to remove this issue? Something to go back to the team and business and explain to them.

CodePudding user response：

Triggers can cause problems, but they're not inherently terrible. Perform normal performance monitoring on the database to determine if there is an issue that needs to be addressed.

CodePudding user response：

In your particular example the trigger is used to inject additional information for the Audit columns.

Being an instead of trigger It's a single update of only the intended row(s).

Triggers have the potential to be bad for performance and can create problems down the line if you encapsulate business logic in them as they are often overlooked or not tested, plus are a common cause of bugs if not understood correctly, or for example where ported from another RDBMS where the operation is similar but not identical to SQL Server.

In your case the trigger is as it should be, short and sweet. However, there could be a performance hit from the if not exists part which you've chosen to withold - generally to know if something does not exist SQL Server must scan the narrowest supporting index it has available, which might impact on concurrency among other things.