I am trying to create a trigger using a merge statement. However, I am receiving the following error message:
PLS-00103: Encountered the symbol “ “ when expecting one of the following: := . ( @ % ; <a SQL statement>
What am I missing? I have included the trigger SQL below.
CREATE OR REPLACE TRIGGER CERTMAN.CERTOP_OPERATOR
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
INTO certop.operator_v b
USING certman.operator_v a
ON (b.operator_id = a.operator_id)
WHEN MATCHED THEN
WHEN NOT MATCHED THEN
INSERT INTO CERTOP.operator_v
It appears you are trying to Update/Insert into a table of the same name but in a different schema. However, you attempt to query (in your Update statement) the very table your row-level after update trigger is firing for. This generally leads to a mutating table error.
Instead, you should use the pseudocolumns in a simple Update statement, rather than using the subquery.
Alternatively, you might consider the table in the other schema as a good candidate for a Materialized View. Then you'd get the desired refresh behavior (Insert or Update) without a lot of additional coding.
This was first published in September 2011