Postgresql - trigger adds twice

Question

I have many triggers in my tables, but one behaves not exactly like I want it.

This one counts products and price and returns total price for order - works ok:

CREATE OR REPLACE FUNCTION ustawwartosczamowienia() RETURNS TRIGGER AS $$

DECLARE

przed NUMERIC;
po NUMERIC;
ile NUMERIC;


BEGIN
IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN

SELECT cena*zamowienieilosc INTO ile FROM zamowienie_zawiera INNER JOIN zamowienie on zamowienie.id=zamowienie_idzamowienie inner join egzemplarz on zamowienie_zawiera.egzemplarz_idegzemplarz=egzemplarz.id inner join produkt on egzemplarz.produkt_idprodukt = produkt.id WHERE zamowienieilosc = new.zamowienieilosc;

SELECT wartosczamowienia INTO przed FROM zamowienie WHERE zamowienie.id = new.zamowienie_idzamowienie;


po := przed+ile;
UPDATE zamowienie SET wartosczamowienia=po WHERE zamowienie.id = new.zamowienie_idzamowienie;

ELSE
RAISE NOTICE 'Nie ma czegoś takiego';

END IF;

RETURN NEW;

END
$$
LANGUAGE 'plpgsql';


CREATE TRIGGER zamowieniewartosc
AFTER INSERT OR UPDATE
ON zamowienie_zawiera
FOR EACH ROW
EXECUTE PROCEDURE ustawwartosczamowienia();

And then I see that somebody purchased goods for f.e. 1024 totally. So I want add this money to this one's personal account to give them special offers for loyal customers later. I wrote similar trigger:

CREATE OR REPLACE FUNCTION kontododaj() RETURNS TRIGGER AS $$

DECLARE

przed NUMERIC;
po NUMERIC;
ile NUMERIC;

klient_idklient RECORD;


BEGIN
IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN

SELECT sumazamowien INTO przed FROM klient WHERE klient.id = new.klient_idklient; 

SELECT wartosczamowienia INTO ile FROM zamowienie WHERE wartosczamowienia =  new.wartosczamowienia; 

po := przed + ile;

UPDATE klient SET sumazamowien=po WHERE klient.id = new.klient_idklient;

ELSE
RAISE NOTICE 'Nie ma czegoś takiego';

END IF;
RETURN NEW;

END
$$
LANGUAGE 'plpgsql';


CREATE TRIGGER kontoplus
AFTER INSERT OR UPDATE
ON zamowienie
FOR EACH ROW
EXECUTE PROCEDURE kontododaj();

Then I look at somebody's account and they have 2048 instead 1024. My trigger adds money twice. What should I change?

user8839064 · Accepted Answer · 2017-10-26 17:06:06Z

2

A very possible reason would be you have created two triggers on one table zamowienie. Multiple triggers can be assigned to one table in PostgresSQL. This is different to other database I used before. I just encountered this issue and resolve it.

By: SELECT * FROM pg_trigger; you can see the triggers assigned to your tables. Drop the unnecessary triggers, it would be OK.

edited Oct 26, 2017 at 17:06

user8839064

173 bronze badges

answered Sep 30, 2017 at 15:55

jamestjtang

212 bronze badges

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

Comments

Tomasz Siorek · Accepted Answer · 2013-06-16 10:08:13Z

1

It will be uneasy to find the reason without knowing better the structure of your database, ie. DDL of the tables and code of other triggers that may come into play.

You wrote "I have many triggers in my tables" and I would suspect some chaining reaction between them. If this is the case, I would recommend using non-trigger procedures to implement similar logic.

By the way, there can be a logical error in the function kontododaj(). But before pointing particular place, let me simplify the code. From the kontododaj() body:

IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN

    SELECT  sumazamowien
    INTO    przed
    FROM    klient
    WHERE   klient.id = new.klient_idklient; 

    SELECT  wartosczamowienia
    INTO    ile
    FROM    zamowienie
    WHERE   wartosczamowienia = new.wartosczamowienia; 

    po := przed + ile;

    UPDATE  klient
    SET     sumazamowien = po
    WHERE   klient.id = new.klient_idklient;

ELSE
    ...

The above snippet is equivalent (in therms of logic) to:

IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN

    UPDATE  klient
    SET     sumazamowien = sumazamowien + new.wartosczamowienia
    WHERE   klient.id = new.klient_idklient;

ELSE
    ...

Now, I doubt this code should be executed after update. Consider situation when field "wartosczamowienia" (value of the order) with value 1024 is set to the same value 1024. This will result in adding 1024 to "sumazamowien" (total value of the orders), which is wrong. Maybe you should try this way:

IF      TG_OP = 'INSERT'
THEN

    UPDATE  klient
    SET     sumazamowien = sumazamowien + new.wartosczamowienia
    WHERE   klient.id = new.klient_idklient;

ELSIF   TG_OP = 'UPDATE'
    AND new.wartosczamowienia <> old.wartosczamowienia
THEN

    UPDATE  klient
    SET     sumazamowien = sumazamowien + ( new.wartosczamowienia - old.wartosczamowienia )
    WHERE   klient.id = new.klient_idklient;

ELSE
    ...

edited Jun 16, 2013 at 10:08

answered Jun 16, 2013 at 9:57

Tomasz Siorek

7215 silver badges10 bronze badges

2 Comments

Denis de Bernardy Over a year ago

+1. The fixed trigger (new - old for the update case) seems like the correct diagnosis.

złyVlojk Over a year ago

It was correct diagnosis indeed. I've been fighting with it for many hours and none of my friends knows Postgresql. You helped me. Dzięki, Tomek :)

Collectives™ on Stack Overflow

Postgresql - trigger adds twice

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related