在配置触发器时更新表中的列时出现错误

Question

我得到这个错误:

Msg 217, Level 16, State 1, Procedure trg_upd, Line 7 [Batch Start Line 0]
最大存储过程、函数、触发器或视图嵌套级别超过(limit 32).

当我在数据库中使用这个触发器时:

CREATE TRIGGER trg_upd_inrt
ON [dbo].[tbl_A]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE A
SET [Desc] = CASE i.[Desc]
WHEN 'HR DD' THEN 'HRDD'
WHEN 'H PP' THEN 'HPP'
WHEN 'RPT AR' THEN 'RPTARIN'
WHEN 'APPP PPLE' THEN 'APPLE'
WHEN 'HR DD' THEN 'HRDD'
ELSE Desc
END 
FROM tbl_A a
INNER JOIN deleted i ON a.id = i.id 
END

Answer 1

不幸的是，SQL Server并没有让你做你想做的事情变得简单。您需要使用instead of触发器并更新所有列:

CREATE TRIGGER trg_upd_inrt ON [dbo].[tbl_A] INSTEAD OF UPDATE
AS BEGIN
SET NOCOUNT ON;
UPDATE A
SET [Desc] = CASE i.[Desc]
WHEN 'HR DD' THEN 'HRDD'
WHEN 'H PP' THEN 'HPP'
WHEN 'RPT AR' THEN 'RPTARIN'
WHEN 'APPP PPLE' THEN 'APPLE'
WHEN 'HR DD' THEN 'HRDD'
ELSE i.[Desc]
END,
COL1 = i.Col1,
COL2 = i.Col2,
. . . -- all the rest of the columns that might be updated
FROM tbl_A a INNER JOIN
inserted i
ON a.id = i.id ;
END;

注意，这里使用新的值而不是旧的值进行替换。这对我来说很有意义。由于表别名的原因，我假设您真正想要的是inserted而不是deleted。

如果您确实需要这两个值，只需在FROM子句中包含这两个值:

FROM tbl_A a INNER JOIN
inserted i
ON a.id = i.id INNER JOIN
deleted d
ON d.id = d.id