Keep a table column at the earliest related value of a column in another table

I have two related tables. Lets call them event_types and occurrences with the following columns:

event_types
-------------
id VARCHAR(30)
name VARCHAR(50)
first_event Timestamp

and

occurrences
-------------
id VARCHAR(30)    
event_type_id VARCHAR(30)
timestamp Timestamp
postal_number Integer
city VARCHAR(50)
street VARCHAR(50)

I want to add a trigger which updates the first_event in case a new occurrence comes up with an earlier timestamp.

Use case: The reason is that I need to query this event_type table frequently with conditions on the first_event and sort it by this field to get the ones inside a time frame.

I tried with something like this:

CREATE OR REPLACE FUNCTION set_to_minimum_time() RETURNS TRIGGER AS
$$
    BEGIN
        IF NEW.timestamp < SELECT first_event from event_types where id=NEW.event_type_id THEN
            UPDATE event_types 
            SET first_event = NEW.timestamp
            WHERE id = NEW.event_type_id
        END IF
    END;
$$ 
LANGUAGE PLPGSQL;

CREATE TRIGGER after_trigger_time_updater
    AFTER INSERT OR UPDATE OR DELETE
    ON occurrences
    FOR EACH ROW
    EXECUTE PROCEDURE set_to_minimum_time();

This fails already due to the select statement in the if clause with syntax error. So how can I achieve such an update via trigger?

I am using Postgres > 9.5 and 10.

One further note: Such updates on occurrences won't come very often.

Solution

You could avoid the syntax error by putting parentheses around the SELECT statement.

But you made another mistake: you defined the trigger ON DELETE, and there is no NEW record in that case. It doesn't make sense to have this trigger ON DELETE anyway, does it?

The SQL statement in the trigger could be simpler:

UPDATE event_types 
SET first_event = NEW.timestamp
WHERE id = NEW.event_type_id
  AND first_event < NEW.timestamp;

That performs the check in the same statement.