How to update a specific column and row with a trigger in a table

Question

So I have this table

CREATE TABLE [dbo].[DailyParkReport] (
[Id]                  INT        IDENTITY (1, 1) NOT NULL,
[Date]                DATE       NOT NULL,
[InchesPrecipitation] FLOAT (53) NOT NULL,
[NumVisitors]         INT        NOT NULL,
[Rainout]             BIT        NOT NULL,
[Temperature]         FLOAT (53) NOT NULL,
CONSTRAINT [PK_DailyParkReport] PRIMARY KEY CLUSTERED ([Id] ASC)
);

and I want to set up a trigger for whenever the InchesPrecipitation is greater than 4 it makes the Rainout value 1 meaning true and if the InchesPrecipitation is < 4 then it makes the Rainout value 0. All of this should update the row in the same table.

What I have for the trigger so far is:

CREATE TRIGGER tr_weather_ForInsertUpdate 
ON [dbo].[DailyParkReport]
FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON
    IF (Select InchesPrecipitation FROM INSERTED) > 4

    UPDATE DailyParkReport
    SET Rainout = 1
    WHERE Rainout = 0

    ELSE

    UPDATE DailyParkReport
    SET Rainout = 0
    WHERE Rainout = 1

END

The problem I'm running into is that whenever the trigger fires it updates every single row in the table and I only want it to update that specific row.

I have fixed the problem by updating my trigger to the following

CREATE TRIGGER tr_weather_ForInsertUpdate 
ON [dbo].[DailyParkReport]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (Select InchesPercipitation FROM INSERTED) > 4

    UPDATE d SET Rainout = 1
    FROM dbo.DailyParkReport AS d
    WHERE EXISTS (SELECT 1 FROM inserted WHERE Id = d.Id)

ELSE

    UPDATE d SET Rainout = 0
    FROM dbo.DailyParkReport AS d
    WHERE EXISTS (SELECT 1 FROM inserted WHERE Id = d.Id)

END

Answer 1

One option would be a computed column... it seems to be a decent fit here. It does have it's draw backs, of course.

create table #DailyParkReport 
([Id]                 INT        IDENTITY (1, 1) NOT NULL,
[Date]                DATE       NOT NULL,
[InchesPrecipitation] FLOAT (53) NOT NULL,
[NumVisitors]         INT        NOT NULL,
[Rainout]             as case when [InchesPrecipitation] >= 4 then 1 else 0 end,
[Temperature]         FLOAT (53) NOT NULL,
CONSTRAINT [PK_DailyParkReport] PRIMARY KEY CLUSTERED ([Id] ASC))

GO


insert into #DailyParkReport
values
(getdate(),3,1,98.6)
,(getdate(),5,1,98.6)

select * from #DailyParkReport

update #DailyParkReport
set InchesPrecipitation = 6 where Id = 1

select * from #DailyParkReport

drop table #DailyParkReport