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

I always assumed PSQL was the identifier for Postgres.. But yea I am using postgres :) — Ozzy
– Ozzy, Commented Feb 8, 2013 at 17:15
psql is mostly short for Postgres, that's true. But it could also be a typo meaning PL/PSQL (which is Oracle's procedural language). Postgres' procedural language is called PL/pgSQL — user330315
– user330315, Commented Feb 8, 2013 at 17:21
There are many issues to handle. What should trigger do if there is upadate, that doesn't change value of "fuel". What should it do if id is changed? What should be done if pre, or post update, fuel is null? — user80168
– user80168, Commented Feb 8, 2013 at 18:39

Orangecrush · Accepted Answer · 2013-02-08 13:00:21Z

16

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;

answered Feb 8, 2013 at 13:00

Orangecrush

1,9902 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

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)

Collectives™ on Stack Overflow

PostgreSQL Trigger on Insert or Update

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related