Mutating table error 1

[snowcold]

Using Oracle 9i.

Here's my situation. We have a table TOOL,

  TOOL_TYPE          VARCHAR2 (10)  NOT NULL, 
  POWER              NUMBER (4,2)  NOT NULL, 
  TOOL_INVENTORY_ID  VARCHAR2 (20)  NOT NULL, 
  TOOL_ID            VARCHAR2 (50), 
  DESIGN_ID          VARCHAR2 (30), 
  ACTIVE

When the users insert new records, I want to concatenate the TOOL_TYPE, POWER and TOOL_INVENTORY_ID to create the TOOL_ID.

At first, I was receiving the ORA-04091 error; mutating table error...but I thought this was because I was attempting to update that TOOL table inside a trigger that was triggered by the insert on that TOOL table.

Now, I created 2 triggers.
The first one, enters the data in a log table, with my concatenated record, TOOL_ID and the second trigger that updates the data into my TOOL table. But I receive the error message still.

here are my triggers:

CREATE OR REPLACE TRIGGER TR_CREATE_TOOL_ID
AFTER INSERT
ON TOOL
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW 
DECLARE

vToolType	varchar2(100);

BEGIN

vToolType := :new.TOOL_TYPE || '-' || :new.DIOPTER || '-' || :new.TOOL_INVENTORY_ID;

insert into TOOL_LOG values 
(:new.tool_type, :new.diopter, :new.tool_inventory_id, vToolType, :new.design_id, :new.active);

  
END ;

the second one:

CREATE OR REPLACE TRIGGER CAMS.TR_CREATE_IOL_TOOL_ID_2
AFTER INSERT
ON CAMS.IOL_TOOL_LOG
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW 
DECLARE

BEGIN

update tool
set tool_id = :new.tool_id
where tool_inventory_id = :new.tool_inventory_id
and design_id = :new.design_id
and active = :new.active
and diopter =  :new.diopter;
and tool_type = :new.tool_type

END ;

any ideas on how to accomplish this?

 

[snowcold]

Here is the correct trigger. The above second trigger is incorrect.

Thanks.

CREATE OR REPLACE TRIGGER TR_CREATE_TOOL_ID_2
AFTER INSERT
ON TOOL_LOG
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW 
DECLARE

BEGIN

update tool
set tool_id = :new.tool_id
where tool_inventory_id = :new.tool_inventory_id
and design_id = :new.design_id
and active = :new.active
and diopter =  :new.diopter;
and tool_type = :new.tool_type;
  
END ;
/

 

[Thargy]

Snow,

I am attempting to duplicate your problem locally.
Hoever, the tool table does not have a data type for the ACTIVE field. Please let me know what this is.

Looking through your triggers, neither one seems to be acting on the table "TOOL". Is this supposed to be the case?

Have I understood correctly that you want to concatenate fields, after insert, to make the tool_id, and then subsequently write the activity to a log table?

Regards

Thare

Grinding away at things Oracular

 

[Thargy]

Snow,

I've also just noticed that the second trigger updates tool after an insert on the tool_log. I'm getting confused as to what you're trying to achieve.

Trigger-based audit maybe?

Regards

Tharg

Grinding away at things Oracular

 

[snowcold]

here is the table design

TOOL ( 
 TOOL_TYPE          VARCHAR2 (10)  NOT NULL, 
  DIOPTER                NUMBER (4,2)  NOT NULL, 
  TOOL_INVENTORY_ID  VARCHAR2 (20)  NOT NULL, 
  TOOL_ID            VARCHAR2 (50), 
  DESIGN_ID              VARCHAR2 (30), 
  ACTIVE                 VARCHAR2 (30)  NOT NULL 
) ;

What I am trying to do is have the users enter into a grid the values for Tool_type, Diopter and Tool_inventory_id. I then will have a trigger called which will concatenate these values and place them into this table, TOOL.

The reason I have included a second table was that I thought that would solve my mutating error message, but it has not.

Thanks alot for looking into this!!!

 

[sem]

You may search any Oracle-related site (including this one) and find zillions of answers on your question, because this error is quite standard.
Most probably your TOOL and TOOL_LOG are connected via foreign key, aren't they? The issue is that foreign key can not be validated when no parent record inserted yet (execution is within trigger). You should postpone inserts into related table until post-insert statement-level trigger.

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Snow,

I have the following for you.

CREATE TABLE TOOL
	(
	TOOL_TYPE VARCHAR2 (10) NOT NULL, 
	DIOPTER NUMBER (4,2) NOT NULL, 
	TOOL_INVENTORY_ID VARCHAR2 (20) NOT NULL, 
	TOOL_ID VARCHAR2 (50), 
	DESIGN_ID VARCHAR2 (30), 
	ACTIVE VARCHAR2 (30) NOT NULL 
	);

and once the table has been created, verify that my dummy data works, including a null value for tool_id

INSERT INTO TOOL VALUES('tooltype_1',11.01,'tool_inventory_id1',NULL, 'design_id1', 'active_val1');                                 
INSERT INTO TOOL VALUES('tooltype_2',11.02,'tool_inventory_id2',NULL, 'design_id2', 'active_val2');
INSERT INTO TOOL VALUES('tooltype_3',11.03,'tool_inventory_id3',NULL, 'design_id3', 'active_val3');
INSERT INTO TOOL VALUES('tooltype_4',11.04,'tool_inventory_id4',NULL, 'design_id4', 'active_val4');
INSERT INTO TOOL VALUES('tooltype_5',11.05,'tool_inventory_id5',NULL, 'design_id5', 'active_val5');
INSERT INTO TOOL VALUES('tooltype_6',11.06,'tool_inventory_id6',NULL, 'design_id6', 'active_val6');
INSERT INTO TOOL VALUES('tooltype_7',11.07,'tool_inventory_id7',NULL, 'design_id7', 'active_val7');
INSERT INTO TOOL VALUES('tooltype_8',11.08,'tool_inventory_id8',NULL, 'design_id8', 'active_val8');
INSERT INTO TOOL VALUES('tooltype_9',11.09,'tool_inventory_id9',NULL, 'design_id9', 'active_val9');
COMMIT;

This goes in successfully, as you can see when you run the insert statements. Next, clear out the table and bolt on the trigger to do the donkey work.

DELETE FROM TOOL;
COMMIT;

CREATE OR REPLACE TRIGGER TBI_TOOL 
BEFORE INSERT ON TOOL 
FOR EACH ROW
BEGIN    
    :NEW.TOOL_ID := :NEW.tool_type||:NEW.diopter||:NEW.tool_inventory_id;
END;

Now repeat the original insert, still using the null values for tool_id. Then check the tables contents, and voila!

INSERT INTO TOOL VALUES('tooltype_1',11.01,'tool_inventory_id1',NULL, 'design_id1', 'active_val1');                                 
INSERT INTO TOOL VALUES('tooltype_2',11.02,'tool_inventory_id2',NULL, 'design_id2', 'active_val2');
INSERT INTO TOOL VALUES('tooltype_3',11.03,'tool_inventory_id3',NULL, 'design_id3', 'active_val3');
INSERT INTO TOOL VALUES('tooltype_4',11.04,'tool_inventory_id4',NULL, 'design_id4', 'active_val4');
INSERT INTO TOOL VALUES('tooltype_5',11.05,'tool_inventory_id5',NULL, 'design_id5', 'active_val5');
INSERT INTO TOOL VALUES('tooltype_6',11.06,'tool_inventory_id6',NULL, 'design_id6', 'active_val6');
INSERT INTO TOOL VALUES('tooltype_7',11.07,'tool_inventory_id7',NULL, 'design_id7', 'active_val7');
INSERT INTO TOOL VALUES('tooltype_8',11.08,'tool_inventory_id8',NULL, 'design_id8', 'active_val8');
INSERT INTO TOOL VALUES('tooltype_9',11.09,'tool_inventory_id9',NULL, 'design_id9', 'active_val9');
COMMIT;

Finish off with SELECT * FROM TOOL, for proof positive that this works.

By the way snow, sem/dima has a point, you really should RTFM, as a mutating table error is bog standard, and has known solutions.

Regards

Tharg


Grinding away at things Oracular

 

[snowcold]

Yes, I must be a little more pro-active in searching for my answers..please excuse me.


The solution thargtheslayer provided worked excellantly.

Thanks!