DELETE AFTER - TRIGGER

Question

I have a problem with a trigger. Here is my trigger :

CREATE TRIGGER calcul_auto_ht AFTER INSERT OR UPDATE OR DELETE ON ligne_commande
FOR EACH ROW EXECUTE PROCEDURE f_montantht();

And here is the function mentioned in the trigger :

CREATE FUNCTION f_montantht() RETURNS TRIGGER AS $montantht$
declare
montant numeric(7,2) := 0; 
montant_par_ligne record; 
remise integer;

begin

for montant_par_ligne
in (select (quantite * prixpdt) as "montant_ligne" from commande c
left join ligne_commande lc
on lc.codecommande = c.codecommande
join produit pd
on pd.codepdt = lc.codepdt
where c.codecommande = NEW.codecommande)

loop
montant := montant + montant_par_ligne.montant_ligne;
end loop;

select into remise coderemise
from commande
where codecommande = NEW.codecommande;
if remise is not null then
montant := montant * (1-remise/100.);
end if;

UPDATE commande
SET montantht = montant
WHERE NEW.codecommande = codecommande;

return NULL;
END;
$montantht$ LANGUAGE 'plpgsql';

The trigger is working normally when I update or I add values in ligne_commande but it doesn't work when I want to delete a row in ligne_commande. In fact I can't see any changes but if I do an insert or an update right after I will be able to see the results of the delete (but I don't want to always need to do that..)

I really don't know why, do you have any idea ?

Thank you :)

Answer 1

ON DELETE triggers do not have a NEW object. You have to reference values using OLD.codecommande in that case. More info: https://www.postgresql.org/docs/current/plpgsql-trigger.html

Try replacing uses of NEW.codecommande with something like this:

CASE WHEN TG_OP = 'DELETE' THEN OLD.codecommande ELSE NEW.codecommande END

If that works, you can make it a better trigger function by moving this logic to a variable which you declare prior to BEGIN, like this (although I'm not sure about your data type):

DECLARE
    _codecommande text := CASE WHEN TG_OP = 'DELETE' THEN OLD.codecommande ELSE NEW.codecommande END;
BEGIN
...

If you also need to support TRUNCATE, you'll need to adjust the logic appropriately.