Re: unexpected effect of FOREIGN KEY ON CASCADE DELETE

2010/6/23 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)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