What I have is a database table CODE that is used to store various types of codes in the application. Due to customer requirements, I must now write a trigger on that table that creates a row to another table, that references two rows in the code table: the inserted/updated row and a second row.
CREATE OR REPLACE TRIGGER FOO
AFTER INSERT OR UPDATE
ON CODE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
-- ...
m_CODE_STATUS_ID CODE.CODE_ID%TYPE;
BEGIN
SELECT X.CODE_ID INTO m_CODE_STATUS_ID
FROM CODE X
WHERE X.CODE_TYPE = 'MSTATUS' AND X.CODE_VALUE = '0';
INSERT INTO OTHER_TABLE (ID,STATUS,CODE_ID)
VALUES(SYS_GUID(),m_CODE_STATUS_ID,:NEW.CODE_ID)
END
Which currently gives the error ERROR:-4091 ORA-04091: table CODE is mutating, trigger/function may not see it
If I were only referencing the changed row, I could use the answer under this question. However, I need to reference a “third” row.
How do I accomplish this?
This can be done with a compound trigger. In a compound trigger, you can combine different firing times into a single trigger that shares e.g. variables.
In this case combine a BEFORE STATEMENT section of the trigger to retrieve the third row, then do the rest in AFTER EACH ROW section.
CREATE OR REPLACE TRIGGER FOO
FOR INSERT OR UPDATE
ON CODE
REFERENCING NEW AS NEW OLD AS OLD
COMPOUND TRIGGER
-- ...
m_CODE_STATUS_ID CODE.CODE_ID%TYPE;
-- before statement happens before the table starts mutating
BEFORE STATEMENT IS
BEGIN
SELECT X.CODE_ID INTO m_CODE_STATUS_ID
FROM CODE X
WHERE X.CODE_TYPE = 'MSTATUS' AND X.CODE_VALUE = '0';
END BEFORE STATEMENT;
-- after each row runs after a row has been inserted or updated.
AFTER EACH ROW IS
INSERT INTO OTHER_TABLE (ID,STATUS,CODE_ID)
VALUES(SYS_GUID(),m_CODE_STATUS_ID,:NEW.CODE_ID)
END AFTER EACH ROW;
END FOO;