Postgres - update statement as a trigger

Question

I've been playing around for the last hour or more trying to put an update statement into a trigger. I understand the concept of an UPDATE statement and the below works just fine

UPDATE cars SET country = 'France';

What I want is to put this into a trigger so that when the cars table is updated, the column country will automatically be updated with France.

I've played around with adapting Functions and Triggers that I've found out on the interweb but I'm obviously making the statement wrong as either they don't execute or they execute but don't update the country field when a new record is added.

CREATE FUNCTION update_country () RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        UPDATE cars SET country = 'France' WHERE id = New.id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql; --The trigger used to update a table.

CREATE TRIGGER update_country_col BEFORE UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE update_country();

The above scripts executes but does not add France to the country column. The function was adapted from a statement that I found out on the web.

Postgres 9.1.

I know that the answer is going to be so simple!

Please show your PostgreSQL version, the code of the trigger you've written, the associated CREATE TRIGGER statement, the statement hat you're using to test it and the exact text of any error message. Right now this isn't far from a "what colour is my hat" question. What hat? I can't see any hat! — Craig Ringer
Postgres 9.1. CREATE FUNCTION update_country () RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'UPDATE') THEN UPDATE cars SET country = 'France' WHERE id = New.id; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; --The trigger used to update a table. CREATE TRIGGER update_country_col BEFORE UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE update_country(); The above scripts executes but does not add France to the country column. The Function was adapted from a statement that I found out on the web. — geomiles

alno alno · Accepted Answer · 2013-03-11T15:43:31

In update triggers you should modify NEW record. Also, you may need to return NEW record from procedure.

So, you should use following procedure instead of yours:

CREATE FUNCTION update_country () RETURNS TRIGGER AS $$
  BEGIN
    IF (TG_OP = 'UPDATE') THEN
     NEW.country = 'France';
    END IF;
    RETURN NEW; 
  END; $$ LANGUAGE plpgsql;

Postgres - update statement as a trigger

1 Answers