Q
Problem solve Get help with specific problems with your technologies, process and projects.

Avoiding and fixing mutating table errors

PL/SQL expert Daniel Clamage tells one reader why her merge statement is creating a mutating table error.

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

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.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

=(select OPERATOR_NAME,CADDRESS,CCITY,CSTATE,CZIP,CHPHONE,CWPHONE,CFAX,CEMAIL

from certman.operator_v);

here you should not write semi colon , this will give error.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close