在触发器中不使用:old和:new访问旧值和新值

Question

正如这里所讨论的，我无法在排序规则不是使用ing_nls_comp的列上使用:OLD和:NEW。我正在想办法解决这个问题，但到目前为止还没有成功。

这是原始触发器:

CREATE OR REPLACE TRIGGER SYS$PERSONSSALUTATIONAU
AFTER UPDATE ON PERSONS 
FOR EACH ROW
begin
State_00.Salutations_ToDelete(State_00.Salutations_ToDelete.Count + 1) := :old.SalutationTitle;
State_00.Salutations_ToInsert(State_00.Salutations_ToInsert.Count + 1) := :new.SalutationTitle;
end;

这是我尝试过的:

CREATE OR REPLACE TRIGGER SYS$PERSONSSALUTATIONAU
FOR UPDATE ON Persons
COMPOUND TRIGGER
TYPE Persons_Record IS RECORD (
SalutationTitle NVARCHAR2(30)
);
TYPE Persons_Table IS TABLE OF Persons_Record INDEX BY PLS_INTEGER;
gOLD Persons_Table;
gNEW Persons_Table;
BEFORE EACH ROW IS BEGIN
SELECT SalutationTitle
BULK COLLECT INTO gOLD
FROM Persons
WHERE ID = :OLD.ID;
END BEFORE EACH ROW;
AFTER EACH ROW IS BEGIN
SELECT SalutationTitle
BULK COLLECT INTO gNEW
FROM Persons
WHERE ID = :NEW.ID;
END AFTER EACH ROW;
AFTER STATEMENT IS BEGIN
FOR i IN 1 .. gNEW.COUNT LOOP
State_00.Salutations_ToDelete(State_00.Salutations_ToDelete.Count + 1) := gOLD(i).SalutationTitle;
State_00.Salutations_ToInsert(State_00.Salutations_ToInsert.Count + 1) := gNEW(i).SalutationTitle;
END LOOP;
END AFTER STATEMENT;
END;

这会导致ORA-04091错误。我还尝试将选择移动到AFTER STATEMENT部分，但没有办法访问旧值。如果有人有解决这个问题的办法，我将不胜感激。

编辑:

我创建了一个最小的可复制示例:

CREATE TABLE example_table (
id VARCHAR2(10),
name NVARCHAR2(100)
);
CREATE TABLE log_table (
id VARCHAR2(10),
new_name NVARCHAR2(100),
old_name NVARCHAR2(100)
);
CREATE OR REPLACE TRIGGER example_trigger
AFTER UPDATE ON example_table
FOR EACH ROW BEGIN
INSERT INTO log_table VALUES(:old.id, :new.name, :old.name);
END;
INSERT INTO example_table VALUES('01', 'Daniel');
-- this works as expected
UPDATE example_table SET name = ' John' WHERE id = '01';
SELECT * FROM log_table;
DROP TABLE example_table;
CREATE TABLE example_table (
id VARCHAR2(10),
-- this is the problematic part
name NVARCHAR2(100) COLLATE XCZECH_PUNCTUATION_CI
);
INSERT INTO example_table VALUES('01', 'Daniel');
-- here nothing is inserted into log_example, if you try to
-- recompile the trigger you'll get error PLS-00049
UPDATE example_table SET name = ' John' WHERE id = '01';
SELECT * FROM log_table;
DROP TABLE example_table;
DROP TABLE log_table;
DROP TRIGGER example_trigger;

Answer 1

在讨论中你引用了一个关于使用nls_comp的文档。这和你得到的误差无关。错误ORA-04091是对触发触发器(发生变化)的表的引用。这方面还有更多。我并不是说您没有USING_NLS_COMP问题，只是它们不会导致当前的错误。
你的触发器中显示了一些误解。从名字本身开始;你应该避免前缀SYS。这个前缀被Oracle用于内部对象。虽然SYS前缀并没有被明确禁止，但它会引起混淆。如果这实际上是在SYS模式中创建的，那么这本身就是一个问题。永远不要使用SYS模式。
没有理由创建包含单个变量的记录类型，然后创建该类型的集合，最后定义集合的变量。只需直接为变量创建一个集合，并定义集合的变量。
select语句中的大容量收集显然被误解了。我假设您希望收集集合中的所有新值和旧值。然而，批量收集不会这样做。每次bulk collect运行时，使用的集合都会被清除并重新填充。结果是集合只包含最后一个种群。假设id是唯一的，每个集合将只包含1条记录。这就引出了问题的核心。
错误ORA-04091: <table name> is mutating, trigger/function may not see it是由于试图从触发触发器的表中SELECT而导致的;这是无效的。在本例中，由于persons表上的DML操作触发了触发器，因此不能从行级触发器(单独或复合触发器的行级部分)中的人员中进行选择。但这是不必要的。伪行:old和:new包含该行的完整映像。要获取值，只需引用适当的行和列名。把它分配给你的集合。
综合考虑，我们得出:

create or replace trigger personssalutation 
for update 
on persons 
compound trigger
type persons_table is table of 
persons.salutationtitle%type;
gold persons_table := persons_table();
gnew persons_table := persons_table();
before each row is 
begin
gold.extend;
gold(gold.count) := :old.salutationtitle;
end before each row;
after each row is 
begin
gnew.extend; 
gold(gold.count) := :new.salutationtitle;
end after each row;
after statement is 
begin
for i in 1 .. gnew.count loop
state_00.salutations_todelete(state_00.salutations_todelete.count + 1) := gold(i);
state_00.salutations_toinsert(state_00.salutations_toinsert.count + 1) := gnew(i);
end loop;
end after statement;

end personssalutation;

注意:遗憾的是，您没有提供示例数据，也没有在AFTER STATEMENT部分提供函数的描述。因此，以上内容不进行测试。