Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

table %s.%s is mutating, trigger/function may not see it

891951Oct 9 2011 — edited Oct 9 2011
Error starting at line 1 in command:
INSERT INTO customer
(cus_id, cus_surname, cus_firstname, cus_address, cus_suburb, cus_city, cus_password, cus_phone, cus_crstatus, cus_stamp)
VALUES
('1', 'DOE', 'JOHN', '88 HIGH STREET', 'LOWER HUTT', 'WELLINGTON', 'JOHNNY', '567 2930', 'G', 'john doe')
Error report:
SQL Error: ORA-04091: table X.CUSTOMER is mutating, trigger/function may not see it
ORA-06512: at "X.TRIG_CUS_ID", line 4
ORA-04088: error during execution of trigger 'X.TRIG_CUS_ID'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

When i added the customer table i created the constraint:
CREATE TABLE CUSTOMER
(
  
  CUS_CRSTATUS VARCHAR2(2),
  CONSTRAINT customer_chk_crstatus CHECK (CUS_CRSTATUS = 'G' OR CUS_CRSTATUS = 'F'
                                          OR CUS_CRSTATUS = 'B')
);
After creating the table i changed the column data type to char(1) .

I thought this caused the problem, so i dropped the constraint, changed the type to varchar2(1) and re-added it. But the error still comes up.

edit: Before i inserted 'G' for cus_crstatus for joe doe, i put in a random letter to check if the constraint worked.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 6 2011
Added on Oct 9 2011
2 comments
933 views