Trigger or Stored Procedure?

Question

Let's say I have two tables, foo and bar:

CREATE TABLE foo(
   foo_id    serial    NOT NULL,
   count     integer   NULL,
   PRIMARY KEY(foo_id)
   );

CREATE TABLE bar(
   bar_id    serial    NOT NULL,
   foo_id    SERIAL    NOT NULL REFERENCES foo(foo_id),
   PRIMARY KEY(bar_id)
   );

When something gets inserted into bar with a given foo_id, the corresponding record in foo should have its count incremented. How exactly do I go about doing this? I'm fairly new to databases in general.

For example:

INSERT INTO foo VALUES(1, 0);
INSERT INTO bar VALUES(1, 1);

At the second line, count should be incremented at the record for foo_id = 1.

I also need to the count decremented on a delete, but I'm sure that's pretty similar to incrementing on insert.

Answer 1

Follow your INSERT statement with:

UPDATE bar SET count = (count+1) WHERE foo_id=1

And on delete:

UPDATE bar SET count = (count-1) WHERE foo_id=1

Answer 2

Recognising that this count is denormalised, and assuming you need it for some purpose that can't be obtained on-the-fly at run time, such as a COUNT() or EXISTS(), I would suggest you use a trigger on bar. More specifically, three triggers, one each for update, insert and delete since each will have a different effect on foo. Note, too, that you may need equivalent triggers on other bar-like tables which also have a foreign key pointing back to foo, depending on what your specific needs are.

In this case a trigger has the advantage of separating "proper" business rules (which should be in a stored procedure) from system optimisation. The former can be maintained by developers without the latter cluttering the code. Secondly, there is less risk that the optimisation code is compromised during a code change. Third, future SPs cannot forget to implement this optimisation or choose not to implement it. The trigger is there for all usage of the table. If there is a case when the count should not be updated that can be made explicit in the trigger code and well commented there for all to see.

Answer 3

I think you need to question why you are storing a count on the foo table. You will be able to retrive the number of bar records referencing any given foo_id at a later date by using SELECT COUNT(*)...

Answer 4

You should be able to retrieve the count by running

SELECT COUNT(*) FROM foo 
INNER JOIN bar
ON foo.foo_is = bar.foo_id