Solution for Accessing old and new values without :OLD and :NEW in a trigger
is Given Below:
As discussed here, I’m unable to use :OLD and :NEW on columns with collation other than USING_NLS_COMP. I’m trying to find a way around this but haven’t been successful so far.
This is the original trigger:
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;
This is what I’ve tried:
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;
This results in error ORA-04091. I’ve also tried moving the select into the AFTER STATEMENT section which works, but there is no way to access the old values. If somebody has a solution for this it would be most appreciated.
I created a minimal reproducible example:
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;
In the discussion you reference a document concerning USING_NLS_COMP. That has nothing to do with the error you are getting. The error ORA-04091 is a reference to the table that fired the trigger (mutating). More to come on this. I am not saying you do not have USING_NLS_COMP issues, just that they are NOT causing the current error.
There are misconceptions shown in your trigger. Beginning with the name itself; you should avoid the prefix SYS. This prefix is used by Oracle for internal objects. While SYS prefix is not specifically prohibited at best it causes confusion. If this is actually created in the SYS schema then that in itself is a problem. Never use SYS schema for anything.
There is no reason to create a record type containing a single variable, then create a collection of that type, and finally define variables of the collection. Just create a collection to the variable directly, and define variables of the collection.
The bulk collect in the select statements is apparently misunderstood as used. I assume you want to collect all the new and old values in the collections. Bulk collect however will not do this. Each time
bulk collect runs the collection used is cleared and repopulated. Result being the collection contains only the only the LAST population. Assuming
id is unique the each collection would contain only 1 record. And now that brings us to the heart of the problem.
ORA-04091: <table name> is mutating, trigger/function may not see it results from attempting to SELECT from the table that fired the trigger; this is invalid. In this case the trigger fired due to a DML action on the
persons table as a result you cannot select from persons in a row level trigger (stand alone or row level part of a compound trigger. But it is not needed. The pseudo rows :old and :new contain the complete image of the row. To get a value just reference the appropriate row and column name. Assign that to your collection.
Taking all into account we arrive at:
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;
NOTE: Unfortunately you did not provide sample data, nor description of the functions in the AFTER STATEMENT section. Therefore the above is not tested.