postgres. plpgsql stack depth limit exceeded
Ok, if you really want the trigger on update, what you could do it set this trigger as column specific, so that it is not fired on an update to all_books
, which is causing your recursion. Something like this -
create trigger total2
after update of copy_id
on totalbooks
for each row
execute procedure total1();
Of course, you can change which columns trigger the function, I just chose copy_id
because that is what you are counting.
HOWEVER
If you are updating with a count()
result, you can just put the trigger on INSERT
and DELETE
actions. This way the trigger will fire when the count changes, but will not itself be triggered by the update. // EDIT: Since your sum
is only a count of all records in copies
, it will only change when a record is inserted or updated, so running this trigger on update would not make sense anyway.
EDIT: I figured it would be useful to add a link to the CREATE TRIGGER Documentation. See the section labeled "event", because this details how to specify columns in the event.
EDIT FOR NEW INFORMATION:
Given what it sounds like you need to accomplish, I think you need to rethink your data design, I suggest you use a parent-child relationship (Anytime you are caching shared data on many rows in a table because they share something in common, that is a sign that you might need a parent table instead).
Have a books
table where each row is information about one book (title, author, etc), and then have a copies
table where each row holds information about one copy of a book (serial number, last checked out, etc).
That way, getting the count of copies is as simple as SELECT COUNT(*) FROM copies WHERE book_id=[some book id]
.
If you really want to cache the count somewhere, do it on the books
table.
Create an INSERT OR UPDATE
trigger on copies
that does UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=NEW.book_id) WHERE id=NEW.book_id
.
Then create a DELETE
trigger on copies that does UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=OLD.book_id) WHERE id=OLD.book_id
The reason for two triggers is that the NEW
variable is only available in INSERT
or UPDATE
triggers, and OLD
is only available in DELETE
triggers. You could do it all as one trigger, but that requires more code than I wanted to put here.
Make sure all your triggers are AFTER
triggers, or else a newly inserted/deleted row won't be considered in the count.