SQL Server - 插入触发器后 - 更新同一表中的另一列

Question

我有这个数据库触发器：

CREATE TRIGGER setDescToUpper
ON part_numbers
 AFTER INSERT,UPDATE
AS
DECLARE @PnumPkid int, @PDesc nvarchar(128)

SET @PnumPkid = (SELECT pnum_pkid FROM inserted)
SET @PDesc = (SELECT UPPER(part_description) FROM inserted)

UPDATE part_numbers set part_description_upper = @PDesc WHERE pnum_pkid=@PnumPkid

GO

这是一个坏主意吗？那就是更新同一个表上的列。我希望它在插入和更新时都触发。

它有效，我只是害怕周期性情况。触发器内的更新会一次又一次地触发触发器。 会发生这种情况吗？

请不要对大写字母吹毛求疵。疯狂的情况。

Answer 1

它取决于数据库上当前设置的触发器的递归级别。

如果你这样做：

SP_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

或者这样：

ALTER DATABASE db_name
SET RECURSIVE_TRIGGERS OFF

上面的触发器将不会再次被调用，并且你将是安全的（除非你陷入某种死锁；这可能是可能的，但也许我错了）。

尽管如此，我不认为这是一个好主意。更好的选择是使用INSTEAD OF 触发器。这样您就可以避免在数据库上执行第一次（手动）更新。只有触发器内定义的才会被执行。

INSTEAD OF INSERT 触发器如下所示：

CREATE TRIGGER setDescToUpper ON part_numbers
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO part_numbers (
        colA,
        colB,
        part_description
    ) SELECT
        colA,
        colB,
        UPPER(part_description)
    ) FROM
        INSERTED
END
GO

这将自动用该语句“替换”原始 INSERT 语句，并对 part_description 字段应用显式 UPPER 调用。

INSTEAD OF UPDATE 触发器是类似的（我不建议您创建单个触发器，请将它们分开）。

另外，这解决了@Martin评论：它适用于多行插入/更新（您的示例不适用于）。

Answer 2

另一种选择是将更新语句包含在 IF 语句中，并调用 TRIGGER_NESTLEVEL() 来限制更新正在第二次运行。

CREATE TRIGGER Table_A_Update ON Table_A AFTER UPDATE 
AS
IF ((SELECT TRIGGER_NESTLEVEL()) < 2)
BEGIN
    UPDATE a
    SET Date_Column = GETDATE()
    FROM Table_A a
    JOIN inserted i ON a.ID = i.ID
END

当触发器最初运行时，TRIGGER_NESTLEVEL 设置为 1，因此将执行更新语句。该更新语句将依次触发相同的触发器，只不过这次 TRIGGER_NESTLEVEL 设置为 2 并且更新语句将不会被执行。

您还可以首先检查 TRIGGER_NESTLEVEL，如果它大于 1，则调用 RETURN 退出触发器。

IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN;

Answer 3

请改用计算列。使用计算列几乎总是比触发器更好的主意。

请参阅下面使用 UPPER 函数的计算列的示例：

create table #temp (test varchar (10), test2 AS upper(test))
insert #temp (test)
values ('test')
select * from #temp

这听起来不像是破记录或其他任何东西，但这非常重要。切勿编写在多个记录插入/更新/删除时无法正常工作的触发器。这是一种非常糟糕的做法，因为迟早会发生其中一种情况，并且您的触发器将导致数据完整性问题，因为它不会精确地失败，它只会在其中一条记录上运行该过程。这可能需要很长时间，直到有人发现混乱，并且通常不可能正确修复数据。

Answer 4

当无事可做时退出触发器可能会更安全。检查嵌套级别或通过关闭 RECURSIVE 来更改数据库可能容易出现问题。

Ms sql 在触发器中提供了一种简单的方法来查看特定列是否已更新。使用 UPDATE() 方法查看某些列是否已更新，例如 UPDATE(part_description_upper)。

IF UPDATE(part_description_upper)
  return

Answer 5

是的，它会递归地调用您的触发器，除非您关闭递归触发器设置：

ALTER DATABASE db_name SET RECURSIVE_TRIGGERS OFF 

MSDN 在 http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx 位于“递归触发器”标题下。

Answer 6

是的...有一个额外的步骤来更新表，您可以在其中设置初始插入中的值可能是一个额外的、可以避免的过程。
您是否有权访问原始插入语句，实际上可以使用 UPPER(part_description) 值将part_description 插入到part_description_upper 列中？

经过思考，您可能没有访问权限，因为您可能会这样做，所以也应该提供一些选项...

1）取决于此part_description_upper列的需要，如果只是为了“查看”，那么可以使用返回part_description值和“ToUpper()”它（取决于编程语言）。

2) 如果想避免“实时”处理，只需创建一个 sql 作业，在低流量期间每天检查一次您的值，并将该列更新为当前未设置的任何值的 UPPER part_description 值。

3）跟随你的触发器（并像其他人提到的那样观察递归）......

HTH

Dave

Answer 7

create or replace 
TRIGGER triggername BEFORE INSERT  ON 
table FOR EACH ROW 
BEGIN
/*
Write any select condition if you want to get the data from other tables
*/
:NEW.COLUMNA:= UPPER(COLUMNA); 
--:NEW.COUMNa:= NULL;
END; 

上面的触发器将在插入之前更新列值。
例如，如果我们将 COLUMNA 的值指定为 null，则每个插入语句都会将该列更新为 null。