Error on an SQL trigger between 2 tables, one of which has a foreign key on the other [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 4 years ago.

Improve this question

Hello I am having the same problem with a trigger, I do not understand my error. The script compiles but when I delete an element I have this error



CREATE OR REPLACE FUNCTION delete_foreign_key() RETURNS TRIGGER AS $_$

  BEGIN 
       
          delete from planning.ordrelivraison_history 
          where ordrelivraison_history.ordrelivraisonid = old.planning.ordrelivraison.ordreid;
      RETURN OLD;
   END;


$_$ LANGUAGE plpgsql;


Create trigger delete_foreign_key_in_order_delivery_history
  after delete 
     on planning.ordrelivraison_history 
    for each row
execute procedure delete_foreign_key();

------------------------------------------------------

delete
from planning.ordrelivraison

where ordreid = 1656

Voici l'erreur

ERROR: ERROR: UPDATE or DELETE on the "delivery order" table violates the foreign key constraint "Ordrelivraison_history_ordreid" from the "ordrelivraison_history" table DETAIL: The key (orderid) = (1656) is always referenced from the “ordrelivraison_history” table.

SQL state: 23503

What is the error you get? One obvious error is that you forgot the "closing" $_$ for the function body. — user330315
– user330315, Commented Oct 8, 2021 at 14:38

Belayer · Accepted Answer · 2021-10-11 23:46:57Z

1

Your trouble in you are firing the trigger at the wrong time. You declare it as AFTER delete but that makes it an after statement trigger, unfortunately the trigger function does not get the old/new rows on after statement trigger. See CREATE TRIGGER documentation. Change your trigger to:

Create trigger delete_foreign_key_in_order_delivery_history
   after delete 
      on ordrelivraison_history 
     for each row
 execute procedure delete_foreign_key();

edited Oct 11, 2021 at 23:46

answered Oct 9, 2021 at 0:39

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Error on an SQL trigger between 2 tables, one of which has a foreign key on the other [closed]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related