Thread: Mutating table (without any error message)

Hi,

I'm having a big throuble with postgreSQL, called "mutating table"

This problem also happen with Oracle,
but we found a soluction creating packagers.

In first.
I would like to explain that this exemple is just a exemple.
Don't try to understand its utility.

Step to emulation the error:

1) Creating a principal table and a child table
   CREATE TABLE PAI(CODPAI INTEGER PRIMARY KEY, NOMPAI VARCHAR(30));
   CREATE TABLE FILHO(CODPAI INTEGER, CODFIL SERIAL, NOMFIL VARCHAR(30),
PRIMARY KEY (CODPAI, CODFIL));

2) Creating constraint between principal and child table
   ALTER TABLE FILHO ADD CONSTRAINT FK_PAI_FILHO FOREIGN KEY (CODPAI)
REFERENCES PAI (CODPAI);

3) Creating trigger on principal table
   CREATE OR REPLACE FUNCTION TR_AIPAI() RETURNS OPAQUE AS '
   BEGIN
    INSERT INTO FILHO (CODPAI, NOMFIL) VALUES (NEW.CODPAI, ''FILHO DO
PAI''||NEW.CODPAI);
    RETURN NULL;
   END;
   ' language 'plpgsql';

   CREATE TRIGGER AIPAI AFTER  INSERT ON PAI FOR EACH ROW EXECUTE PROCEDURE
TR_AIPAI();

If we taking a insert on principal table it will insert on child table,
until here, thats all right...

4) Create a trigger on child table that make a select on principal table
   CREATE OR REPLACE FUNCTION TR_SELECT_PAI() RETURNS OPAQUE AS'
   DECLARE I INTEGER;
   BEGIN
    SELECT COUNT(*) INTO I FROM PAI;
    RETURN NULL;
   END;
   'language 'plpgsql';

   CREATE TRIGGER BIFILHO BEFORE INSERT ON FILHO FOR EACH ROW EXECUTE
PROCEDURE TR_SELECT_PAI();

In this moment when we insert a register on principal table
it don't insert the register on child table, neither send any error message.

It's the error that I want to register,
it inserted the record in principal table, but didn't insert in child table.

thanks,

Cristian Luciano Custodio