CODEWITHSUNDEEP
sqlpostgresqltriggers
złyVlojk
złyVlojk

Reputation: 5

Postgresql - trigger adds twice

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?

Upvotes: 0

Views: 2321

Answers (2)

jamestjtang
jamestjtang

Reputation: 21

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.

Upvotes: 2

Tomasz Siorek
Tomasz Siorek

Reputation: 711

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
    ...

Upvotes: 1

Related Questions