From: | "Cristian Custodio" <crstian(at)terra(dot)com(dot)br> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Mutating table (urgent) |
Date: | 2003-02-21 18:19:43 |
Message-ID: | 002201c2d9d5$d085db50$fb01a8c0@ttcristian |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
If anybody can help me, I'll be thanks for ever.
Cristian Custodio
From | Date | Subject | |
---|---|---|---|
Next Message | Doug McNaught | 2003-02-21 18:26:20 | Re: transactions |
Previous Message | Jan Wieck | 2003-02-21 18:15:50 | Re: Foreign Key with Constant |