Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    Argentina
    Posts
    4

    Exclamation Unanswered: What´s the problem?

    Using the SQL*Plus of Oracle 8i Personal Edition:

    CREATE TABLE COBERTURA (
    NombreCob VARCHAR(30) NOT NULL,
    TelCob INTEGER NULL
    CONSTRAINT positivo560
    CHECK (TelCob >= 1),
    DirCob VARCHAR(30) NULL,
    CONSTRAINT XPKCOBERTURA
    PRIMARY KEY (NombreCob)
    );


    CREATE TABLE PARTICIPANTE (
    NroDoc INTEGER NOT NULL
    CONSTRAINT positivo561
    CHECK (NroDoc >= 1),
    NombreCob VARCHAR(30) NULL,
    NyAp VARCHAR(30) NOT NULL,
    Dir VARCHAR(30) NOT NULL,
    Tel INTEGER NULL
    CONSTRAINT positivo562
    CHECK (Tel >= 1),
    Mail VARCHAR(30) NULL,
    Localidad VARCHAR(30) NOT NULL,
    Pcia VARCHAR(30) NOT NULL,
    CodPostal VARCHAR(10) NULL,
    Sexo CHAR(1) NOT NULL
    CONSTRAINT sexo49
    CHECK (Sexo = 'f' or Sexo = 'm'),
    Edad SMALLINT NOT NULL
    CONSTRAINT positivo563
    CHECK (Edad >= 1),
    CONSTRAINT XPKPARTICIPANTE
    PRIMARY KEY (NroDoc),
    CONSTRAINT posee
    FOREIGN KEY (NombreCob)
    REFERENCES COBERTURA
    );

    create trigger tI_PARTICIPANTE after INSERT on PARTICIPANTE for each row
    declare numrows INTEGER;
    begin
    insert into COBERTURA (NombreCob)
    select NombreCob
    from PARTICIPANTE
    where
    :new.NombreCob is not null and
    not exists (
    select * from COBERTURA
    where
    :new.NombreCob = COBERTURA.NombreCob
    );
    end;
    /

    Inserting...

    SQL> INSERT INTO PARTICIPANTE (NroDoc, NombreCob, NyAp, Dir, Tel, Mail, Localidad, Pcia, CodPostal,
    Sexo, Edad)
    2 values (21, 'nomcob', 'nyap1', 'dir1', 23, 'mail1', 'loca1', 'pci', 7000, 'f', 23);
    INSERT INTO PARTICIPANTE (NroDoc, NombreCob, NyAp, Dir, Tel, Mail, Localidad, Pcia, CodPostal, Sexo,
    *
    ERROR at line 1:
    ORA-04091: table SYSTEM.PARTICIPANTE is mutating, trigger/function may not see
    it
    ORA-06512: at "SYSTEM.TI_PARTICIPANTE", line 3
    ORA-04088: error during execution of trigger 'SYSTEM.TI_PARTICIPANTE'

    What´s the problem?
    Then I drop the trigger and I create the following trigger:

    create trigger tI_PARTICIPANTE after INSERT on PARTICIPANTE for each row
    declare numrows INTEGER;
    DECLARE Y VARCHAR(20);
    begin
    insert into tmp_participante VALUES (:NEW.NombreCob);
    CURSOR C IS SELECT DISTINCT NombreCob FROM tmp_participante;
    OPEN C;
    FETCH C INTO Y,
    WHILE C%found LOOP
    insert into COBERTURA (Y)
    where
    Y is not null and
    not exists (select * from COBERTURA where Y = COBERTURA.NombreCob);
    FETCH C INTO Y
    END LOOP;
    CLOSE C;
    DELETE FROM tmp_presentacion;
    end;
    /
    Warning: Trigger created with compilation errors.


    What´s the problem? Which are the possible reasons of this warning?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: What´s the problem?

    You cannot reference the trigger table within a FOR EACH ROW trigger.

    In this case, I think you can simply remove the "for each row" clause from the trigger definition and modify the DML like this:

    Code:
    create trigger tI_PARTICIPANTE after INSERT on PARTICIPANTE
    begin
        insert into COBERTURA (NombreCob)
          select NombreCob
            from PARTICIPANTE p
            where NombreCob is not null and
              not exists (
                select * from COBERTURA
                  where
                    p.NombreCob = COBERTURA.NombreCob
              );
    end;
    This will "fix up" the COBERTURA table for every row that is missing.

    If that is not desirable (i.e. if you must ONLY "fix up" COBERTURA for the new values) then you need to do the standard "mutating table" work around (which involves a package and several triggers) described in detail here:

    http://asktom.oracle.com/~tkyte/Mutate/index.html

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    to avoid the mutating table problem, try the following:

    create a procedure with a identification parameter and in the declaration pragma autonomous_transaction and a cursor selecting the row from the source table, identified by the parameter. Then after begin, open the cursor, fetch it and insert the fetched data into the target table.
    Do not forget commit.

    good luck
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Using autonomous_transaction to get round the mutating problem is a bad idea. Yes, the mutating table problem goes away - but so does data integrity! If the original INSERT is rolled back, the autonomous transaction will not be. There will be phantom records in COBERTURA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •