如何使用表中的触发器更新特定的列和行

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.

我想设置一个触发器当inches降水大于4时，它使Rainout值1表示真如果inches降水小于4那么它使Rainout值为0。所有这些都应该更新同一个表中的行。

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

2 个解决方案

#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

#2

CREATE TRIGGER tr_weather_ForInsertUpdate
ON [dbo].[DailyParkReport]
FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON

    UPDATE DailyParkReport
        SET Rainout = CASE WHEN i.InchesPrecipitation >= 4 THEN 1 ELSE 0 END
    FROM
        DailyParkReport a
        INNER JOIN inserted i ON i.Id = a.Id
END