Trigger in PostgreSQL

Question

I have table "Candidates" with id (primary key) and application_counter
and table "Applications" with foreign key (candidate_id). I want application_counter to be modified each time Application is added or removed (or modified by changing candidate_id).

All I can do is to write:

CREATE TRIGGER myTrigger AFTER INSERT OR DELETE OR UPDATE
ON "Applications" FOR EACH ROW
EXECUTE PROCEDURE funcname ( arguments )

And the question is How can I write this trigger?

Synopsis from page http://www.postgresql.org/docs/8.1/interactive/sql-createtrigger.html

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )

Answer 1

I'd use a view, INNER JOIN the two tables, and count the rows in the applications table. (See COUNT().) Triggers can be disabled; that view will always give you the right answer.

(Later . . .)

I understand you want to limit a candidate's rows in the table "applications" to 3 or less. In that case, I think it's best to use a CHECK() constraint on "applications" rather than the combination of a trigger on "applications" and a CHECK() constraint on "candidates".

To do that in PostgreSQL, you have to use a function, and call the function from the CHECK(). (As far as I know. You still can't execute arbitrary SELECT statements in CHECK() constraints, right?) So, you'd create this function,

CREATE FUNCTION num_applications(cand_id integer)
  RETURNS integer AS
$BODY$ 

  DECLARE 
    n integer; 
  BEGIN 
    select count(*) 
    into n 
    from applications
    where (candidate_id = cand_id);

    return n; 
  END; 
$BODY$ 
LANGUAGE plpgsql;

and then you'd add a CHECK() constraint to the table 'applications'.

ALTER TABLE applications
  ADD CONSTRAINT max_of_three CHECK (num_applications(candidate_id) < 3);

"< 3" because the CHECK() constraint is evaluated before adding a row. It's probably worth testing to see how this behaves with deferred constraints. If I have time later, I'll do that.

Answer 2

CREATE TRIGGER myname AFTER INSERT, DELETE OR UPDATE 
ON table applications
EXECUTE PROCEDURE myfunc();

CREATE FUNCTION myfunc() RETURNS TRIGGER AS 
BEGIN
    IF TG_OP != 'DELETE' THEN
        update candicate set application_count = application_count + 1 where id = new.candidate_id;
    END IF;
    IF TG_OP != 'INSERT' THEN
        update candicate set application_count = application_count + 1 where id = old.candidate_id;
    END IF;
END;

I hope you get the idea... works now also with updated candidate_id's.