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 )

Ok, and what's the question? By the way, a trigger function can't accept arguments, check the manual: postgresql.org/docs/current/interactive/plpgsql-trigger.html — Frank Heikens
– Frank Heikens, Commented Feb 1, 2011 at 15:36

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-02-01 21:18:58Z

3

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.

edited Feb 1, 2011 at 21:18

answered Feb 1, 2011 at 16:55

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user330315 Over a year ago

+1 a trigger seems to be overkill here (unless it is for training purposes)

Miko Kronn Over a year ago

I need triger because there is CHECK condition on application_counter that states that each candidate can post max 3 applications.

Daniel Over a year ago

You could also use a SELECT count() in the trigger... would be fast also if there is a useful index on candidate_id.

Miko Kronn Over a year ago

What do you mean by 'useful'?

Mike Sherrill 'Cat Recall' Over a year ago

@Miko Kronn: In that case, I'd rather use a CHECK() constraint on the table Applications. I'll edit my answer to show how to do that.

|

Daniel · Accepted Answer · 2011-02-01 17:40:31Z

3

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.

edited Feb 1, 2011 at 17:40

answered Feb 1, 2011 at 16:20

Daniel

28.2k20 gold badges93 silver badges140 bronze badges

4 Comments

Miko Kronn Over a year ago

How can I write this function to do what I want? I mean, how can I get id of "Candidates" being affected. There can be one (INSERT or DELETE) or two (UPDATE).

Ken Downs Over a year ago

Inside "myFunc()", for the Insert side, "Update candidates set counter = counter + 1 where id = new.candidate_id". For the delete trigger, refer to "old.candidate.id". Updates don't do anything unless it is possible to change the candidate_id field, in which case you decrement from old and increment new.

Miko Kronn Over a year ago

This might be a new question but... How can I prevent update on candidate_id column?

Daniel Over a year ago

Raise an exception in the trigger, if old.candiate_id is distinct from new.candidate_id

Collectives™ on Stack Overflow

Trigger in PostgreSQL

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related