Mutating table (urgent)
От | Cristian Custodio |
---|---|
Тема | Mutating table (urgent) |
Дата | |
Msg-id | 002201c2d9d5$d085db50$fb01a8c0@ttcristian обсуждение исходный текст |
Ответы |
Re: Mutating table (urgent)
|
Список | 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.
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.
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));
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);
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 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...
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 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 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
В списке pgsql-general по дате отправления: