用于插入和更新的SQL触发器 [英] SQL Trigger for Insert and Update

问题描述

需要帮助的SQL新手。

Sql newbie in need of help.

我有一个表，该表带有在插入时发生的SQL触发器。看起来像这样：

I have a table with an SQL trigger that occurs on Insert. It looks like this:

ALTER TRIGGER Holiday_ITrig
ON HolidayDate
FOR INSERT
AS
IF NOT EXISTS (
                SELECT
                        ID
                    FROM
                        WorkingDay w
                    INNER JOIN
                        inserted i ON
                            w.ID = I.WorkingDayId
                    WHERE
                        WorkingDayTypeId = 2
                )
BEGIN
    ROLLBACK TRANSACTION
END

但是，我还需要触发器来触发更新。

However, I also need the trigger to fire on update. Is this possible within the same trigger or do I have to create a new 'update' trigger?

推荐答案

只需添加 UPDATE 到动作列表。

您将需要检查UPDATE和原始逻辑是否适用相同的逻辑插入。

You will need to check that the same logic applies to an UPDATE as well as the original INSERT.

ALTER TRIGGER Holiday_ITrig
ON HolidayDate
FOR INSERT, UPDATE
AS
IF NOT EXISTS (
                SELECT
                        ID
                    FROM
                        WorkingDay w
                    INNER JOIN
                        inserted i ON
                            w.ID = I.WorkingDayId
                    WHERE
                        WorkingDayTypeId = 2
                )
BEGIN
    ROLLBACK TRANSACTION
END

创建触发MSDN文章

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