SQL Delete trigger for multiple table

Question

So I have two table with id_pastille and I want to create a trigger that each time that one id_pastille is deleted every table that has the id_pastille is deleted as well.

CREATE TABLE tp1_trajet
(
 id_trajet   int NOT NULL,
 longitude   double precision NOT NULL,
 latitude    double precision NOT NULL,
 "date"      timestamp NOT NULL,
 id_pastille int NOT NULL,
 CONSTRAINT PK_zone_geographique PRIMARY KEY ( id_trajet ),
 CONSTRAINT FK_ FOREIGN KEY ( id_pastille ) REFERENCES tp1_pastille ( id_pastille )

and let say this table

CREATE TABLE tp1_compte
(
 id_compte          int NOT NULL,
 date_achat         date NOT NULL,
 id_pastille        int NOT NULL,
 id_usager          int NOT NULL,
 probabilite_malade double precision NULL,
 CONSTRAINT PK_compte PRIMARY KEY ( id_compte ),
 CONSTRAINT UQ_compte_id_pastille UNIQUE ( id_pastille ),
 CONSTRAINT UQ_compte_id_usager UNIQUE ( id_usager ),
 CONSTRAINT FK_compte_id_pastille FOREIGN KEY ( id_pastille ) REFERENCES tp1_pastille ( id_pastille ),
 CONSTRAINT FK_compte_id_usager FOREIGN KEY ( id_usager ) REFERENCES tp1_usager ( id_usager )
);

I came up with this so far :

CREATE FUNCTION Delete_pastille() RETURNS trigger
AS $DeleteCustomerWithOrders$
BEGIN
   DELETE FROM tp1_bulle,
               tp1_compte,
               tp1_pastille,
               tp1_signes_vitaux,
               tp1_usager,
               tp1_transaction,
               tp1_trajet
   WHERE id_pastille = OLD."Id";
   RETURN OLD;
END;
$DeleteCustomerWithOrders$
   LANGUAGE plpgsql;

You need to mark your code as such. There are 3 ways to do so. 1. Highlight the code and click the {} above the entry bow. 2. Highlight the code and press Ctrl+K. 3. Place the code between lines contain ``` but otherwise blank. Also, what you places as a answer is actually part of the question ans needs to be places there. — Belayer
– Belayer, Commented May 7, 2021 at 6:04

Laurenz Albe · Accepted Answer · 2021-05-07 06:05:51Z

2

You can only delete from a single table in a DELETE statement. Try using several statements.

The best solution might be to define foreign key constraints on all but one of these columns that point to the table that contains the original copy (pastille?). If the foreign key is defined with ON DELETE CASCADE, deleting the original will automatically delete all the dependent rows.

answered May 7, 2021 at 6:05

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Comments

notsara · Accepted Answer · 2021-05-07 17:29:19Z

0

alter table tp1_trajet
drop constraint fK_,
add constraint fK_
foreign key (ID_pastille)
references tp1_pastille (ID_pastille)
on delete cascade;

alter table tp1_compte
drop constraint FK_compte_id_pastille,
add constraint FK_compte_id_pastille
foreign key (ID_pastille)
references tp1_pastille (ID_pastille)
on delete cascade;

answered May 7, 2021 at 17:29

notsara

255 bronze badges

Collectives™ on Stack Overflow

SQL Delete trigger for multiple table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related