MS SQL 和 Oracle 中触发器的比较
本文讨论了 Oracle 和 MS SQL 中触发器的区别，并展示了可变表（mutating table）问题。
引言
本文的目的是展示 Oracle 和 MS SQL 中触发器工作方式的差异。撰写本文的主要动机是我找不到一篇合适的文章，能够从 MS SQL / Sybase 开发者的角度解释这些差异。目前，本文并非旨在进行深入分析，仅涵盖常规 DML 触发器。它不包含“instead of”触发器、系统事件触发器或 DDL 触发器，尽管这三者在 Oracle 和 MS SQL 中都存在。
背景
Oracle（与其他大多数供应商一样）的触发器概念与 MS SQL Server 不同。MS SQL（如 Sybase）采用基于集合的方法。受数据修改（插入、更新、删除）影响的行存储在 inserted 和 deleted 表中。MS SQL 中的常规 DML 触发器总是在语句执行后执行。修改前的数据存储在 deleted 表中，修改后的数据存储在 inserted 表中。两者都可以从触发器内部访问。还可以连接 inserted 和 deleted 表，并使用它们来更新触发器执行的表。
在 Oracle 中，有“before”和“after”触发器，触发器可以定义为按行（per row）或按语句（per statement）执行（还有复合触发器，可以同时包含按行和按语句的部分）。“Before per statement”触发器在语句执行前执行，并且只能访问修改前的数据表。“After per statement”触发器在语句完成后执行（因此有时被称为延迟触发器，但这有点误导，因为 SQL 中的延迟通常意味着执行推迟到可能包含多个语句的事务提交时）并且只能访问修改后的数据表。
这意味着，“after statement”触发器无法看到修改前的数据，而“before statement”触发器也无法看到修改后的数据。只有“per row”触发器可以同时看到修改前和修改后的数据，但仅限于当前行。要在“per row”触发器中访问行的修改前数据，Oracle 提供了 OLD 伪记录（pseudorecord）；要访问修改后的数据，则使用 NEW 伪记录，它们对应于当前正在修改的行。因此，OLD 和 NEW 类似于 deleted 和 inserted，但它们是行级别的。此外，在 Oracle 中，无法从“per row”触发器内部访问或修改表的其他行。如果尝试这样做，就会遇到著名的 Oracle 可变表错误（mutating table error）。您只能在“before per row”触发器中修改 NEW 伪记录，然后它将被写入表。
Oracle 禁止访问表的其他行，因为从概念上讲，“per row”触发器是在修改表的每一行时在表修改过程中被触发的，当时该行正处于被修改的过程中。因此，当触发器针对给定行触发时，某些行可能已经被更改，而有些行尚未被更改。结果，查询一个仍在修改过程中的表显然极有可能导致不一致和错误的结果，这就是为什么它被禁止的原因。只有当前行可以在“before per row”触发器中被修改，或者必须使用“per statement”触发器。
Oracle 触发器的另一个属性，也是经常出错的根源，是当触发器调用的表在同一触发器中进行另一次对同一表的更改时，触发器会被递归调用。在 MS SQL 中，触发器递归是可能的，但至少对于常规触发器而言，这不是默认行为，递归触发器（也称为嵌套触发器）需要作为服务器选项显式设置。
可变表错误和递归触发器错误是相关的，因为它们在触发器调用的表被同一触发器访问或修改时被调用，但不应混淆。可变表错误发生在尝试在“per row”触发器中访问或修改同一表时，而递归错误发生在“per statement”触发器中对同一表进行修改导致触发器再次递归触发时。
使用代码
为了说明这些差异，我们将来看一个简单的数据库，其中包含一个存储个人信息的表。
CREATE TABLE Person(
ID int NOT NULL,
Name varchar(50) NOT NULL,
PreviousName varchar(50) NULL,
SameNameCount int NULL,
CONSTRAINT pk_Person PRIMARY KEY (ID))
GO
每当 Name 列的值发生更改时，PreviousName 列都应更新为更改前的姓名。SameNameCount 列应在插入、更新、删除行时更新，并存储 Name 列具有相同值的行的数量。（例如，如果 Name 值为“John”的行有两条，那么这两行的 SameNameCount 列的值都应为 2。）
MS SQL 解决方案
在 MS SQL 中，触发器如下所示
CREATE TRIGGER tiud_PERSON ON Person FOR INSERT, UPDATE, DELETE
AS
BEGIN
IF UPDATE(Name)
BEGIN
PRINT 'tiud_PERSON UPDATE PreviousName on Person from deleted.'
UPDATE Person
SET PreviousName = d.Name
FROM PERSON p
JOIN deleted d ON d.ID = p.ID
WHERE d.Name <> p.Name
END
PRINT 'tiud_PERSON UPDATE SameName on Person from inserted and deleted.'
UPDATE Person
SET SameNameCount = (SELECT COUNT(*) FROM Person where Name = p.Name)
FROM PERSON p
WHERE Name in (SELECT Name FROM inserted UNION SELECT Name FROM deleted)
END
GO
运行以下语句来测试触发器
INSERT Person (ID, Name) VALUES (1, 'Peter')
INSERT Person (ID, Name) VALUES (2, 'Peter')
INSERT Person (ID, Name) VALUES (3, 'Paul')
GO
SELECT * FROM Person
GO
ID Name PreviousName SameNameCount
----------- -------------------------------------------------- -------------------------------------------------- -------------
1 Peter NULL 2
2 Peter NULL 2
3 Paul NULL 1
(3 row(s) affected)
UPDATE Person
SET Name = 'Paul'
WHERE ID = 1
GO
SELECT * FROM Person
GO
ID Name PreviousName SameNameCount
----------- -------------------------------------------------- -------------------------------------------------- -------------
1 Paul Peter 2
2 Peter NULL 1
3 Paul NULL 2
(3 row(s) affected)
DELETE Person
WHERE ID = 3
GO
SELECT * FROM Person
GO
ID Name PreviousName SameNameCount
----------- -------------------------------------------------- -------------------------------------------------- -------------
1 Paul Peter 1
2 Peter NULL 1
(2 row(s) affected)
要在 Oracle 中实现相同的功能，一种可能的解决方案是使用“per row”触发器来更新 PreviousName 列，并使用“per statement”触发器来更新 SameNameCount 列。在实施此解决方案之前，我们先看一些会导致可变表错误和递归触发器错误的实现。
可变表错误和递归触发器错误
要创建 Person 表，请在 Oracle 上运行以下 SQLCREATE TABLE Person(
ID int NOT NULL,
Name varchar(50) NOT NULL,
PreviousName varchar(50) NULL,
SameNameCount int NULL,
CONSTRAINT pk_Person PRIMARY KEY (ID));
为了演示可变表错误，我们首先将 SameNameCount 列的更新实现为一个“per row”触发器。
CREATE OR REPLACE TRIGGER tar_Person
AFTER INSERT OR UPDATE OR DELETE
ON Person
FOR EACH ROW
BEGIN
--Update SameNameCount
UPDATE Person p
SET SameNameCount = (SELECT COUNT(*) FROM Person WHERE Name = p.Name);
END;
/
要测试触发器并触发可变表错误，请运行以下语句
INSERT INTO Person (ID, Name) VALUES (1, 'Peter');
ERROR at line 1:
ORA-04091: table SYSTEM.PERSON is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TAR_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAR_PERSON'
下一个实现是通过一个“per statement”触发器来更新 SameName 列，以演示触发器递归错误
--Drop previous trigger
DROP TRIGGER tar_Person;
CREATE OR REPLACE TRIGGER tas_Person
AFTER INSERT OR UPDATE OR DELETE
ON Person
BEGIN
--Update SameNameCount
UPDATE Person p
SET SameNameCount = (SELECT COUNT(*) FROM Person WHERE Name = p.Name);
END;
/
运行以下语句来测试触发器并触发触发器递归错误
INSERT INTO Person (ID, Name) VALUES (1, 'Peter');
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERSON'
ORA-06512: at "SYSTEM.TAS_PERSON", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TAS_PERS
Oracle 解决方案
为了绕过这个问题，我们需要一个更复杂的方案，在这种情况下，就是将触发器拆分成两个，让更新触发器只在 Name 更新时触发
--Drop previous trigger
DROP TRIGGER tas_Person;
--Split trigger into insert, delete and update trigger
CREATE OR REPLACE TRIGGER tas_Person_id
AFTER INSERT OR DELETE
ON Person
BEGIN
--Update SameNameCount
UPDATE Person p
SET SameNameCount = (SELECT COUNT(*) FROM Person WHERE Name = p.Name);
END;
/
CREATE OR REPLACE TRIGGER tas_Person_u
AFTER UPDATE OF Name ON Person
BEGIN
--Update SameNameCount
UPDATE Person p
SET SameNameCount = (SELECT COUNT(*) FROM Person WHERE Name = p.Name);
END;
/
运行以下语句来测试实现
--Do an insert
INSERT INTO Person (ID, Name) VALUES (1, 'Peter');
1 row created.
--Check result
SELECT * FROM Person;
ID NAME
---------- --------------------------------------------------
PREVIOUSNAME SAMENAMECOUNT
-------------------------------------------------- -------------
1 Peter
1
--Now insert some more rows and check the result
INSERT INTO Person (ID, Name) VALUES (2, 'Peter');
INSERT INTO Person (ID, Name) VALUES (3, 'Paul');
SELECT * FROM Person;
ID NAME
---------- --------------------------------------------------
PREVIOUSNAME SAMENAMECOUNT
-------------------------------------------------- -------------
1 Peter
2
2 Peter
2
3 Paul
1
现在 SameNameCount 列的更新已经可以正常工作了，我们现在可以创建更新 PreviousName 列的触发器了。作为一名习惯了 MS SQL 风格的触发器的 MS SQL 开发人员，您可能会首先编写一个“after”触发器，但这同样会导致可变表错误。正确的实现方法是使用“before”触发器。
CREATE OR REPLACE TRIGGER tbr_Person_u
BEFORE UPDATE
ON Person
FOR EACH ROW WHEN (NEW.Name IS NOT NULL AND NEW.Name <> OLD.Name)
BEGIN
--Update PreviousName if name change
:NEW.PreviousName := :OLD.Name;
END;
/
UPDATE Person SET Name = 'John' WHERE ID = 2;
SELECT * FROM Person;
ID NAME
---------- --------------------------------------------------
PREVIOUSNAME SAMENAMECOUNT
-------------------------------------------------- -------------
1 Peter
1
2 John
Peter 1
3 Paul
1
MS SQL 触发器的局限性
到目前为止，Oracle 触发器可能显得更复杂、更难处理，因为它有“per statement”和“per row”触发器，必须将它们组合使用才能实现 MS SQL 在单个触发器中完成的功能。然而，这种分离使得 Oracle 触发器在处理影响表主键的操作时更强大。
如下所示，MS SQL 触发器无法同时获取修改前和修改后的数据，因为在更新过程中，用于连接 deleted 表的主键已经发生了变化：
--Recreate records
DELETE Person
GO
INSERT Person (ID, Name) VALUES (1, 'Peter')
INSERT Person (ID, Name) VALUES (2, 'Peter')
INSERT Person (ID, Name) VALUES (3, 'Paul')
GO
--Show current rows in table
SELECT * FROM Person
GO
ID Name PreviousName SameNameCount
----------- -------------------------------- -------------------------------- -------------
1 Peter NULL 2
2 Peter NULL 2
3 Paul NULL 1
(3 row(s) affected)
--Now do an update that also affects the primary key
UPDATE Person
SET ID = 100 + ID, Name = 'John'
GO
--Display result
SELECT * FROM Person
GO
ID Name PreviousName SameNameCount
----------- -------------------------------- ------------------------------------ -------------
101 John NULL 3
102 John NULL 3
103 John NULL 3
(3 row(s) affected)
更新主键是否明智是另一个讨论话题。上述示例的目的是仅演示 MS SQL 触发器的局限性，可以看到，Previous name 列未被更新，而 Same name 列已更新。
由于 Oracle 还提供“per row”视图，因此更新也能捕获主键的更新，并按预期完成工作：
--Recreate records
DELETE Person;
INSERT INTO Person (ID, Name) VALUES (1, 'Peter');
INSERT INTO Person (ID, Name) VALUES (2, 'Peter');
INSERT INTO Person (ID, Name) VALUES (3, 'Paul');
--Show current rows in table
SELECT * FROM Person;
ID NAME
---------- --------------------------------------------------
PREVIOUSNAME SAMENAMECOUNT
-------------------------------------------------- -------------
1 Peter
2
2 Peter
2
3 Paul
1
--Do the update
UPDATE Person
SET ID = ID + 100, Name = 'John';
--Show result
SELECT * FROM Person;
ID NAME
---------- --------------------------------------------------
PREVIOUSNAME SAMENAMECOUNT
-------------------------------------------------- -------------
101 John
Peter 3
102 John
Peter 3
103 John
Paul 3
Previous name 和 Same name 列已更新为正确的值。
SQLite 中的可变表
最后，下面是一个 SQLite（http://www.sqlite.org/）的示例，它只有“per row”触发器，但不会阻止会导致 Oracle 中可变表错误的那些操作。
/*
SQLlite script to demonstrate mutating table problem
All triggers are per row
*/
drop table if exists PERSON;
create table PERSON (
ID int not null PRIMARY KEY,
Name varchar(100) not null,
PreviousName varchar(100) null,
SameNameCount int null);
create trigger tua_PERSON after update on PERSON
begin
update PERSON
set PreviousName = OLD.Name,
SameNameCount = (select count(*) from PERSON where Name = NEW.Name)
where NEW.Name <> OLD.Name
and ID = NEW.ID and ID = OLD.ID;
end;
insert into PERSON (ID, Name) values (1, 'Peter');
insert into PERSON (ID, Name) values (2, 'Peter');
insert into PERSON (ID, Name) values (3, 'Peter');
select * from PERSON;
1|Peter||
2|Peter||
3|Peter||
update PERSON
set Name = 'Paul';
select * from PERSON;
1|Paul|Peter|1
2|Paul|Peter|2
3|Paul|Peter|3
SameNameCount 列的值反映了触发器触发时表的状态，因此在三个表记录之间不一致。
结论
本文的冗长度已经显示了该主题的复杂性，并且如引言中所述，本文仅涵盖常规 DML 触发器。
不深入探讨所有方面，为了简洁起见，MS SQL 触发器更容易使用，并且遇到的陷阱更少，但存在 Oracle 触发器没有的局限性。
历史
- 2013 年 7 月 29 日：初始版本。