SQL Server:更新触发器似乎会影响错误的列?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (186)

谢谢你的期待。我正在尝试编写一个SQL Server触发器,当添加包含日期信息的新记录时,会将星期几添加到DayOfWeek列。这是我的表,按顺序列:

食物台:

FoodName **varchar(20)**  
CategoryID (FK) **int**  
Price **smallmoney**  
StoreID (FK) **int**  
Date **datetime**  
DayOfWeek **varchar(9)**  
ShopperID (FK) **int**  
Week **int**  

这是我写的触发器:

-- Create a trigger to update day of the week when a record is inserted  
CREATE TRIGGER DOW  
ON Food  
FOR INSERT  
AS  
BEGIN  
    -- Declare a variable to hold the date ID  
    DECLARE @dateID DATETIME

    -- Get the date from the new record and store it in @dateID  
    SELECT @dateID = Date FROM Food  

    -- Insert day of the week based on the inserted date  
    INSERT INTO Food (DayOfWeek)  
        SELECT DATENAME(dw, @dateID)  
END  
GO  

SQL Server似乎接受了这个过程,但是当我运行另一个过程来插入新记录时,我收到了这个错误:

消息515,级别16,状态2,过程DOW,第8行[批处理开始行21] 无法将值NULL插入到“Week”列中,表*******; 列不允许空值。INSERT失败。

我不确定为什么这个触发器会影响“周”专栏。代码应该采用为Date输入的值,并使用DATENAME(dw,...)函数返回星期几,该日期应该进入DayOfWeek列。我编写了一个存储过程,接受一个日期作为输入,并将相应的星期几插入记录,它工作得很好,但这个触发器似乎不想合作。我很难过!

提问于
用户回答回答于

您的触发器存在一些主要问题。在触发器中,有一个插入表(插入和更新)和删除表(删除和更新)。您应该使用此表的信息来了解需要更新的记录。

This is bad because a trigger can have multiple rows
This SQL simply will not work correctly if you insert multiple rows.
DECLARE @dateID DATETIME
SELECT @dateID = Date FROM Food  

This SQL is trying to insert a new row which is causing your NULL error
It is not trying to update the row you are inserting
INSERT INTO Food (DayOfWeek)  
    SELECT DATENAME(dw, @dateID)  

它需要是一个INSTEAD OF触发器来避免列上的空约束。Wolfgang的答案仍然会导致空约束错误,因为在插入数据之后运行后触发器。INSTEAD OF触发器将代替实际插入运行。

CREATE TRIGGER DOW ON Food
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- update the day of the week for the inserted rows

    INSERT INTO Food (FoodName,CategoryID,Price,StoreID,[Date],ShopperID,[Week],[DayOfWeek])
        SELECT
            FoodName,CategoryID,Price,StoreID,[Date],ShopperID,[Week],DATENAME(dw, [Date]) AS [DayOfWeek]
        FROM inserted
END
GO

就个人而言,我认为存储周和星期几是一个坏主意。您已有一个可以派生该信息的值(日期)。每当您有多个基本上是重复数据的列时,您将遇到维护难题。

用户回答回答于

你的触发器做了什么:

  • Date从您的表中获取a (返回的最后一个),这不一定是最后插入的值。
  • 它尝试插入仅DayOfWeek包含Date指定内容的新记录。
  • 它失败了,因为至少Week还必须指定。

我想你想更新DayOfWeek插入行的值。为了能够这样做,必须有一种方法通过知道插入行的值来识别需要在Food表中更新的行。为了确保更新正确的行,应该有一个主键,允许您识别它们。肯定你有这样一个主键,我想它已经命名了FoodID,所以你可能想要这样做:

CREATE TRIGGER DOW ON Food
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- update the day of the week for the inserted rows
    UPDATE Food
        SET [DayOfWeek] = DATENAME(dw, f.[Date])
    FROM Food f
        INNER JOIN inserted i ON f.FoodID = i.FoodID
END
GO

扫码关注云+社区

领取腾讯云代金券