CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Favolas
Favolas

Reputation: 7243

PL/SQL Trigger not working properly

I have a problem with a trigger. After creating the trigger it gives me the message

BD1.UTILIZADOR is in mutation, trigger cant read or execute

I have this table

    create table UTILIZADOR 
    (
       U_ID                 NUMBER(6)            not null,
       U_NOME               VARCHAR2(60)         not null,
       U_SEXO               VARCHAR2(10)         not null,
       U_IDADE              NUMBER(3)            not null,
       U_ALTURA             NUMBER(3)            not null,
       U_PESO               NUMBER(6)            not null,
       U_IMC                NUMBER(2,2),
       U_PRIVILEGIOS        NUMBER(1)            not null,
       U_PASSWORD           VARCHAR2(10)         not null,
       constraint PK_UTILIZADOR primary key (U_ID)
    );

and If i do this:

INSERT INTO UTILIZADOR (U_ID,U_NOME,U_SEXO,U_IDADE,U_ALTURA,U_PESO,U_PRIVILEGIOS,U_PASSWORD) VALUES (1,'my name','Male',32,174,74000,0,'password');

and then

SELECT * FROM UTILIZADOR;

I can see that everything is as expected.

What I want to do, Is when inserting or updating U_ALTURA or U_PESO, the field U_IMC gets automatically updated/inserted.

Here is what if done:

CREATE OR REPLACE TRIGGER CALCULA_IMC
AFTER INSERT OR UPDATE OF U_ALTURA, U_PESO ON UTILIZADOR
FOR EACH ROW
BEGIN
    UPDATE UTILIZADOR
        SET U_IMC = (U_PESO / 1000) /  (POWER(U_ALTURA / 100,2));
END;
/

Can someone please tell me what I'm doing wrong?

Upvotes: 2

Views: 4346

Answers (3)

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

I assume you want to update a field of the record being updated and not on the entire table:

CREATE OR REPLACE TRIGGER CALCULA_IMC
AFTER INSERT OR UPDATE OF U_ALTURA, U_PESO ON UTILIZADOR
FOR EACH ROW
BEGIN
    :NEW.U_IMC = (:NEW.U_PESO / 1000) /  (POWER(:NEW.U_ALTURA / 100,2));
END;
/

Upvotes: 3

Ollie
Ollie

Reputation: 17538

Your trigger code is updating every record in the UTILIZADOR table, this would include the record your INSERT statement has just inserted, I am assuming you don't want every record in your table updated whenever you insert a new record into it.

As you trigger fires after insert or update it is effectively going to recursively call itself infinitely and you really don't want that.

You need to understand a little more about triggers before you can effectively do what you want to do.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

http://www.techonthenet.com/oracle/triggers/

Here is a great article about mutating tables and triggers with some suggestions for getting round the problem:

http://www.oracle-base.com/articles/9i/MutatingTableExceptions.php

There may well be a better way to achieve your goals, for example you could use a BEFORE INSERT OR UPDATE trigger and assign the updates to the :NEW values instead of issuing a new UPDATE in your trigger body etc.

A row-level trigger can’t read or write the table it’s fired from. A statement-level trigger can, however.

Hope it helps...

Upvotes: 5

OTTA
OTTA

Reputation: 1081

Your trigger is attempting to update a row that's already being updated, think about it, in a post update trigger you're attempting to make another update which would fire the trigger again, causing another update which would then fire the trigger again.......

Oracle doesn't like trying to change or examine something that’s already being changed.

Try setting u_imc directly in your insert / update statements.

Upvotes: 1

Related Questions