SQL Server 2008触发器 [英] SQL Server 2008 Trigger

问题描述

我对SQL Server并不陌生，但对触发器却不陌生。我已经阅读了一些教程，说实话，语法仍然有点令人困惑。

I'm not new to SQL Server but I am new to triggers. I've read a few tutorials and to be honest the syntax is still a little confusing.

我正在尝试创建一个触发器来检查设置为一个特定的值，然后更新同一表中的另一个字段。

I'm trying to create a trigger that checks for a field being set to a specific value and then update another field in the same table.

希望下面的代码能说明一切，对于某些人来说问题很明显

Hopefully the code below will speak for itself and the problem will be obvious to someone

ALTER TRIGGER updateCompletedDate
ON projects
AFTER INSERT, UPDATE
AS
   IF UPDATE(status)
   BEGIN
        UPDATE projects
        SET date_complete = GETDATE()
        FROM inserted
        WHERE inserted.status = 'Completed'
          AND projects.date_complete = '1900-01-01'
END

上面的代码没有我想要的效果希望。当我更新一行并将状态设置为已完成时， date_complete 列仍为默认值 1900-01-01 。

The above code doesn't have the effect I'd hoped for. When I update a row and set the status to Completed the date_complete column remains at the default of 1900-01-01.

谢谢

根据marc_s的评论我将触发器修改为现在可以使用的触发器。解决方法是在where子句中添加 AND insert.id = projects.id 。

As per marc_s' comments I modified the trigger to the following which now works. The fix was the addition of AND inserted.id = projects.id in the where clause.

ALTER TRIGGER updateCompletedDate
ON projects
AFTER INSERT, UPDATE
AS
IF UPDATE(status)
BEGIN
    UPDATE projects
    SET projects.date_complete = GETDATE()
    FROM inserted
    WHERE inserted.status = 'Completed'
    AND inserted.id = projects.id
    AND projects.date_complete = '1900-01-01'
END

谢谢大家。

推荐答案

我看不到如何链接在 Inserted 伪表到 Projects 表中的行。

I don't see how you're linking those rows that were inserted/updated in the Inserted pseudo table to the rows in your Projects table. There should be something like

inserted.ID = projects.ID 

或 WHERE 子句中的内容：

UPDATE p
SET 
    p.date_complete = GETDATE()
FROM  
    dbo.Projects p
INNER JOIN 
    inserted i ON inserted.id = projects.id
WHERE 
    i.status = 'Completed'
    AND p.date_complete = '1900-01-01'

这篇关于SQL Server 2008触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！