postgresql - 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!

Answer 1

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;