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.
Requires Free Membership to View
CREATE OR REPLACE TRIGGER CERTMAN.CERTOP_OPERATOR
AFTER UPDATE
ON CERTMAN.OPERATOR_V
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
MERGE
INTO certop.operator_v b
USING certman.operator_v a
ON (b.operator_id = a.operator_id)
WHEN MATCHED THEN
UPDATE certop.operator_v
SET(OPERATOR_NAME,CADDRESS,CCITY,CSTATE,CZIP,CHPHONE,CWPHONE,
CFAX,CEMAIL)
=(select OPERATOR_NAME,CADDRESS,CCITY,CSTATE,CZIP,CHPHONE,CWPHONE,CFAX,CEMAIL
from certman.operator_v);
WHEN NOT MATCHED THEN
INSERT INTO CERTOP.operator_v
( operator_name,
operator_id,
copstatus,
dexpires,
drenewed,
caddress,
ccity,
cstate,
czip,
chphone,
cwphone,
cfax,
cemail)
VALUES
( :new.operator_name,
:new.operator_id,
:new.copstatus,
:new.dexpires,
:new.drenewed,
:new.caddress,
:new.ccity,
:new.cstate,
:new.czip,
:new.chphone,
:new.cwphone,
:new.cfax,
:new.cemail);
END;
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

Join the conversationComment
Share
Comments
Results
Contribute to the conversation