Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE
От | Thom Brown |
---|---|
Тема | Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE |
Дата | |
Msg-id | AANLkTinS_LBPGYIExNu-Amw16d9MGoDWtiiyrKv5tcVf@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE (Grzegorz Jaśkiewicz <gryzman@gmail.com>) |
Список | pgsql-general |
2010/6/23 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > the delete will succeed. > That's not the point of the exercise tho. > > The point, is to print name in trigger, rather than null! > But if it's been deleted from foob already, how can it print it? So if foob has a row with an id of 5, then: DELETE FROM foob WHERE id = 5; That row is deleted from foob. This cascades to attempt to delete it from fooa. The trigger happens first though which tries to find the row from foob where id = 5... but it's already been deleted, so no name is selected. To demonstrate, change your trigger function to: create FUNCTION foobarrA() RETURNS trigger AS $_$ BEGIN RAISE NOTICE 'foobarred %', (SELECT name FROM fooB WHERE id = 999); RETURN OLD; END; $_$ LANGUAGE 'plpgsql'; and add in: insert into foob(id, name) values (999, 'stuff'); insert into fooa(id, foob) values (999, 999); after your inserts. This will successfully select the value because it's not deleted. And then running: DELETE FROM foob where id =999; Will return NULL again because it's just been deleted before the trigger on fooa. So cases where it's returning NULL is because there's been no match. Thom
В списке pgsql-general по дате отправления: