Trigger function updates all rows instead of one (PostgreSQL)

Question

Using PostgreSQL. What I have is: 3 tables

1.Customer2c with columns: CustomerID,PersonID,Number_Of_Items`.

2.SalesOrderHeader2c with columns: SalesOrderID,CustomerID.

SalesOrderDetail2c with columns: SalesOrderDetailID,SalesOrderID,OrderQty

I want to create a trigger function, that will trigger whenever someone uses

INSERT INTO 'SalesOrderDetail2c' table

and that is going to get the OrderQty that was inserted and update the correspondent Number_Of_Items field with it.

My trigger is working, but the problem is that whenever I insert a new value to the SalesOrderDetail2c, the function gets the OrderQty value and updates all the rows of Number_Of_Items with it, instead of updating just the correspondent one.

Any help appreciated. What I have so far is this (It may be copletely wrong, dont judge please!):

CREATE OR REPLACE FUNCTION FunctionTrigger2c() RETURNS TRIGGER AS
$BODY$
BEGIN
    UPDATE Customer2c
    SET Number_Of_Items = 
    (SELECT OrderQty
    FROM SalesOrderDetail2c
    INNER JOIN SalesOrderHeader2c ON (SalesOrderDetail2c.SalesOrderID = SalesOrderHeader2c.SalesOrderID)
    INNER JOIN Customer2c ON (SalesOrderHeader2c.CustomerID = Customer2c.CustomerID)
    ORDER BY SalesOrderDetailID DESC LIMIT 1
    )
    FROM SalesOrderHeader2c
    WHERE SalesOrderHeader2c.CustomerID = Customer2c.CustomerID
    ;
    RETURN NEW;
    END;
$BODY$ 
language plpgsql;

CREATE TRIGGER Trigger2c
     AFTER INSERT ON SalesOrderDetail2c
     FOR EACH ROW
     EXECUTE PROCEDURE FunctionTrigger2c();

I'll admit that I am not entirely clear on what you are trying to do; however, I wonder if you should be referencing NEW (see: postgresql.org/docs/current/static/plpgsql-trigger.html) to get the value that was just inserted/updated. — Nick
– Nick, Commented Jun 7, 2016 at 17:12

Orestes Polyzos · Accepted Answer · 2016-06-07 17:27:37Z

1

I had to use .new as @Nicarus mentioned above! Thanks again by the way.

This is the new code and now it changes only the correspondent value.

CREATE OR REPLACE FUNCTION FunctionTrigger2c() RETURNS TRIGGER AS
$BODY$
BEGIN
    UPDATE Customer2c
    SET Number_Of_Items = 
    (SELECT new.OrderQty
    FROM SalesOrderDetail2c
    order by salesorderdetailid desc limit 1
    )
    FROM SalesOrderheader2c
    WHERE (SalesOrderheader2c.salesorderID = new.salesorderID) and (salesorderheader2c.customerid = customer2c.customerid)
    ;
    RETURN NEW;
    END;
$BODY$ 
language plpgsql;

CREATE TRIGGER Trigger2c
     AFTER INSERT ON SalesOrderDetail2c
     FOR EACH ROW
     EXECUTE PROCEDURE FunctionTrigger2c();

answered Jun 7, 2016 at 17:27

Orestes Polyzos

332 silver badges6 bronze badges

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

1 Comment

Adam Over a year ago

It looks like you are setting Number_Of_Items to be OrderQty of last added SalesOrderDetail. Should't it be the sum of all SalesOrderDetails? Please, take a look at my answer.

Cardo Kambla · Accepted Answer · 2019-11-05 20:15:10Z

I am pointing out why your original trigger did not work. The reason it updated every row is because of your UPDATE statement. After you inserted something, it triggered the update. When the update ran it got these things:

What table I am gonna update
What value am I gonna set on what column of the given table
What rows I am gonna implement this update on (FROM + WHERE)

The problem lies in the last part, basically calling FROM in an UPDATE statement is like calling a SELECT statement, it tried to process every row from the FROM clause tables with the given WHERE value.

PostgreSQL documentation about UPDATE statement from_list parameter states that "A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement." (https://www.postgresql.org/docs/9.1/sql-update.html)

Basically, your update statement went through every row in SalesOrderHeader2c which conveniently matched with every row of Customer2c (with SalesOrderHeader2c.CustomerID = Customer2c.CustomerID), updating the value of every row in Customer2c with the firstly found Number_Of_Items value from the UPDATE SET statement. This is why you got the same value for every row in Customer2c.

The reason the new trigger works is because the update statement is done with the correctly selected rows.

Adam · Accepted Answer · 2016-06-07 20:54:19Z

Here is my, quite general trigger that is used do count/sum values in dependent tables. You should take care of all order changes, ie not only INSERT but also DELETE and UPDATE. You also should read @Nicarus comment about using NEW (and OLD) in triggers.

I have altered it to match your schema, but didn't test it...

CREATE OR REPLACE FUNCTION FunctionTrigger2c()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            UPDATE
                Customer2c
            SET
                Number_Of_Items = Number_Of_Items + NEW.OrderQty
            FROM
                SalesOrderHeader2c
            WHERE
                Customer2c.CustomerID = SalesOrderHeader2c.CustomerID AND
                SalesOrderHeader2c.SalesOrderID = NEW.SalesOrderID AND
                SalesOrderDetailID = NEW.SalesOrderDetailID;

        WHEN 'UPDATE' THEN
            UPDATE
                Customer2c
            SET
                Number_Of_Items = Number_Of_Items - OLD.OrderQty
            FROM
                SalesOrderHeader2c
            WHERE
                Customer2c.CustomerID = SalesOrderHeader2c.CustomerID AND
                SalesOrderHeader2c.SalesOrderID = OLD.SalesOrderID AND
                SalesOrderDetailID = OLD.SalesOrderDetailID;

            UPDATE
                Customer2c
            SET
                Number_Of_Items = Number_Of_Items + NEW.OrderQty
            FROM
                SalesOrderHeader2c
            WHERE
                Customer2c.CustomerID = SalesOrderHeader2c.CustomerID AND
                SalesOrderHeader2c.SalesOrderID = NEW.SalesOrderID AND
                SalesOrderDetailID = NEW.SalesOrderDetailID;

        WHEN 'DELETE' THEN
            UPDATE
                Customer2c
            SET
                Number_Of_Items = Number_Of_Items - OLD.OrderQty
            FROM
                SalesOrderHeader2c
            WHERE
                Customer2c.CustomerID = SalesOrderHeader2c.CustomerID AND
                SalesOrderHeader2c.SalesOrderID = OLD.SalesOrderID AND
                SalesOrderDetailID = OLD.SalesOrderDetailID;
    END CASE;

    RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

UPDATE also work well if you change the SalesOrderID :)

Notice, that this trigger returns NULL and it should be set as AFTER INSERT OR UPDATE OR DELETE:

CREATE TRIGGER Trigger2c
  AFTER INSERT OR UPDATE OR DELETE
  ON SalesOrderDetail2c
  FOR EACH ROW
  EXECUTE PROCEDURE FunctionTrigger2c();

You are correct, I forgot to add the previous value. and your schema is much more flexible. However, my instructions are to make that specific trigger only for INSERT INTO.

Collectives™ on Stack Overflow

Trigger function updates all rows instead of one (PostgreSQL)

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related