Trigger Performance - 1 or 2 Triggers?

Question

I have a question about triggers performance.

CREATE TABLE [dbo].[_test](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Date] [DATETIME] NULL,
[DateYearID] [INT] NULL,
[DateQuarterID] [INT] NULL,
[Date1] [DATETIME] NULL,
[Date1YearID] [INT] NULL,
[Date1QuarterID] [INT] NULL)

Now I want to have trigger that must update DateYearID and DateQuarterID columns if I update Date column (or insert a new row) , and must update Date1YearID and Date1QuarterID columns if I update Date1 column (or insert a new row). What is better, have a single trigger like

IF UPDATE(DATE)
   UPDATE _test SET DateYearID = ... , DateQuarterID = ...
IF UPDATE (DATE1)
   UPDATE _test SET Date1YearID = ... , Date1QuarterID = ...

or have two different triggers, first of them updating DateYearID,DateQuarterID columns, and the second one updateing DateYear1ID,DateQuarter1ID columns.

I am using SQL Server 2014.

Thanks a lot for help.

Answer 1

I would suggest a single trigger, with a single pass through inserted and/or the base table, and just updating all four values for any row that is affected. I have proven before that avoiding updating columns in a row where other column values have changed is not worth the effort, and that updating the same row twice from two different triggers will be slower.

IF UPDATE(DATE) OR UPDATE(DATE1)
BEGIN
  UPDATE t 
    SET DateYearID   = ...,
        DateQuarterID  = ...,
        Date1YearID    = ...,
        Date1QuarterID = ...
  FROM dbo._test AS t
  INNER JOIN inserted AS i
  ON t.ID = i.ID;
END     

(Also, just as an aside, note that UPDATE() tells you the column was referenced in an update statement, but it doesn't guarantee that the value has changed.)

And yes, I agree with Gianluca's comment: depending on how these four columns are calculated, you may not need a trigger at all. You may not even need a computed column - simple calculations (like DATEPART(YEAR,Date1)) are often best done at query time and only when you need them. Storing redundant data is overrated - I/O and memory are much more important than CPU in almost all systems, even with today's SSD and PCIe flash drives...