Insert into different table and update

Question

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    UNIQUE(username)
);

CREATE TABLE alert_triggers(
    id SERIAL PRIMARY KEY,
    uid INTEGER REFERENCES users(id),
    price_hi REAL,
    active_hi BOOLEAN DEFAULT TRUE,
    price_lo REAL,
    active_lo BOOLEAN DEFAULT TRUE
);

CREATE TABLE alerts (
    id SERIAL PRIMARY KEY,
    uid INTEGER REFERENCES users(id),
    event INTEGER NOT NULL
);

CREATE TABLE prices (
    stamp DATETIME PRIMARY KEY,
    price REAL
);

Upon inserting a new price, I want to perform the following four operations:

1. if the PRICE is greater than or equal to the PRICE_HI and ACTIVE_HI is TRUE, then ACTIVE_HI should be set to FALSE and the alerts table should be inserted with event ID 1.
2. if the PRICE is less than the PRICE_HI, then ACTIVE_HI should be set to TRUE.
3. if the PRICE is less than or equal to the PRICE_LO and ACTIVE_LO is TRUE, then ACTIVE_LO should be set to FALSE and the alerts table should be inserted with event ID 2.
4. if the PRICE is greater than the PRICE_LO, then ACTIVE_LO should be set to TRUE.

For this to happen, I need to simultaneously update and query one table and insert the query results into a different table.

Events:

Price Above PRICE_HI       1
Price Below PRICE_LO       2 

This is where I'm getting stuck. I want to perform something like this:

INSERT INTO alerts (
   uid,
   event
) SELECT uid,1
FROM (
    UPDATE alert_triggers
        SET active_hi = FALSE
        WHERE active_hi = TRUE AND price_hi <= 5.0
    RETURNING uid
);