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.

This was last published in September 2011

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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