PostgreSQL Trigger on Insert or Update

Question

I have two tables. I want to create a trigger on the car table which will insert or delete on the fuel table depending on a certain value.

Car

id - SERIAL
fuel - BOOLEAN

Fuel

car_id -  INTEGER

I am not including any row data as the description of the trigger does not need it.

Basically, I want to create a trigger on the Car table that:

• Runs on an insert or update.
• Inserts Car.id into Fuel table if Car.fuel is true.
• If Car.fuel is false, the trigger should delete all rows in the Fuel table where Fuel.car_id = Car.id.

How would I do this?

EDIT: To clarify I am using Postgres

Solution

Since you haven't mentioned the RDBMS, I assume it is Oracle. Below is the trigger. If you are using another RDBMS, tweak the code to fit the syntax.

CREATE OR REPLACE TRIGGER TRG_CAR
   AFTER INSERT OR UPDATE ON CAR
   FOR EACH ROW

   BEGIN

     IF :new.FUEL THEN
       INSERT INTO FUEL (CAR_ID) VALUES (:new.ID);
     ELSE
       DELETE FROM FUEL WHERE CAR_ID = :new.ID;
     END IF;
   END;

Comments

[a_horse_with_no_name]

I don't think IF :new.FUEL THEN will work in Oracle as it does not have a BOOLEAN SQL datatype (only PL/SQL has). So the :NEW record can not have a boolean column. I actually think Ozzy is using Postgres (psql is Postgres' command line tool)