On Wednesday 23 June 2010 5:35:52 am Grzegorz Jaśkiewicz wrote: > consider following example: > > > > CREATE TABLE foob(id serial primary key, name varchar default ''); > CREATE TABLE fooA(id serial primary key, fooB int not null references > fooB(id) on update cascade on delete cascade, name varchar default > ''); > > CREATE FUNCTION foobarrA() RETURNS trigger AS > $_$ > BEGIN > RAISE NOTICE 'foobarred %', (SELECT name FROM fooB WHERE id = OLD.fooB); > RETURN OLD; > END; > $_$ LANGUAGE 'plpgsql'; > > CREATE TRIGGER foobarrrred BEFORE DELETE ON fooA FOR EACH ROW EXECUTE > PROCEDURE foobarrA(); > insert into foob(name) select random()::varchar FROM > generate_series(1,100); insert into fooa(name, foob) select > random()::varchar, (select id from foob order by random() limit 1) FROM > generate_series(1,100); > > select foob from fooa order by random() limit 1; > foob > ------ > 70 > (1 row) > > DELETE FROM foob where id =70; > NOTICE: foobarred <NULL> > CONTEXT: SQL statement "DELETE FROM ONLY "public"."fooa" WHERE $1 > OPERATOR(pg_catalog.=) "foob"" > NOTICE: foobarred <NULL> > > > > I always assumed, that since triggers are set to BEFORE, the data will > still exist in the tables when they are fired, it will still be > accessible. I looked in the manual, and there is no mention of that > effect anywhere I can find. > > > And here's the question, is there any way in which I can overcome that > (to me) problem ? Other than, by substituting foreign key with my own > trigger, to handle that situation and than delete data. > > > thank you . > > -- > GJ My suspicion is that this is an identifier problem. See error below: CONTEXT: SQL statement "DELETE FROM ONLY "public"."fooa" WHERE $1 OPERATOR(pg_catalog.=) "foob"" <--- *** It would seem to me there is confusion between the table fooB(b) and the column foob. I am afraid at this point I can not be any more helpful. -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
