Re: A "cascade on delete" constraints deletes AFTER the source is gone??

On Mon, Dec 20, 2004 at 09:56:35AM +0200, Vitaly Belman wrote:

> I have two tables. "Books" and "Book_Authors" (which links between
> book_id and author_id).

Is there a third table, perhaps Authors?

> Book_authors has a foreign key on book_id to the Books table. On key
> violation it is set to delete the rows (if a book is deleted, it
> should't be linked to any authors).
>
> In Book_Authors I also have a trigger on DELETE. When a book is
> unlinked from an author, then the author vote_count should be reduced
> (as the author vote_count is the sum of all votes of his books).

Which table has vote_count -- the Authors table that I'm guessing
exists? Where does vote_count's value come from? A field in Books?
Is there a reason you're maintaining vote_count instead of querying
for it with an aggregate like SUM or COUNT?

> The problem is that when a book is deleted and then the trigger tried
> to get the number of it votes, it returns NULL, as the book is already
> gone and so its data. If it was a simple matter of triggers I could
> play with BEFORE/AFTER, but since it is constraints issue, it seems to
> be all happening AFTER the deletion.

Is there a reason you can't maintain vote_count with a trigger on
Books?

Could you post a minimal but complete example (CREATE statements
and INSERT or COPY statements with sample data) so we can get a
better idea of what you're trying to do?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/