How To Prevent Trigger Function from Creating Duplicative Rows

Question

Goal: Create a trigger function to update a table, payment_detail. The table payment_detail comprises of an inner join of two other tables (customer & payment). The trigger function is focused only on updating payment_detail when an UPDATE operation occurs on the payment table.

Step 1: Create detail table

CREATE TABLE IF NOT EXISTS payment_detail AS
SELECT customer.customer_id, 
       customer.first_name, 
       customer.last_name, 
       payment.payment_id, 
       payment.amount, 
       to_char(payment.payment_date, 'Mon YYYY') AS month_yr
FROM payment
INNER JOIN customer ON payment.customer_id = customer.customer_id;

The table above works great. Next, I create a trigger function to automatically update the above table (I know, VIEWS would be better than updating a table, but that's not the problem):

Step 2: Create trigger function

CREATE OR REPLACE FUNCTION update_payment_detail()
       RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
       UPDATE payment_detail
              SET
              customer_id = NEW.customer_id,
              first_name = customer.first_name,
              last_name = customer.last_name,
              payment_id = NEW.payment_id,
              amount = NEW.amount,
              month_yr = to_char(NEW.payment_date, 'Mon YYYY')
              FROM
                     customer
              WHERE customer.customer_id = NEW.customer_id;
              RETURN NULL;
END;
$$

CREATE TRIGGER update_payment_detail
       AFTER UPDATE
       ON payment
       FOR EACH ROW
       EXECUTE PROCEDURE update_payment_detail();

Step 3: Test the trigger function Now, in order to test the trigger function, I update the payment table as follows:

UPDATE payment
SET amount = 4.35
WHERE payment_id = 32126 AND customer_id = 1;

View the payment_detail table to verify updated record:

SELECT * FROM payment_detail WHERE customer_id = 1;

The result is a single record (the same exact record I updated above) being repeated throughout the entire payment_detail table and that's the problem. Why does it do that? There should only be one such record, among many other unique records. And if I DROP the payment_detail at this point and then re-create it and then just run the SELECT * FROM payment_detail statement above, the payment_detail table comes out perfectly fine with just the one record updated. So it's not clear what is happening here. How could I resolve this?

"The table payment_detail comprises of an inner join of two other tables (customer & payment)": then don't try to populate a redundant table with a trigger. Use a view. — Laurenz Albe
– Laurenz Albe, Commented Dec 17, 2021 at 6:43

Julius Tuskenis · Accepted Answer · 2021-12-20 07:29:45Z

1

Take a look at your update script:

UPDATE payment_detail
              SET
              customer_id = NEW.customer_id,
              first_name = customer.first_name,
              last_name = customer.last_name,
              payment_id = NEW.payment_id,
              amount = NEW.amount,
              month_yr = to_char(NEW.payment_date, 'Mon YYYY')
              FROM
                     customer
              WHERE customer.customer_id = NEW.customer_id;

It says you update all payment_detail records with the data from customer of currently updated record. So on every update you update all the rows in payment_detail while you should update only the rows matching current payment (add AND payment_detail.payment_id = OLD.payment_id to your WHERE part).

EDIT 1 So the result UPDATE statement would look like:

UPDATE payment_detail SET
  customer_id = NEW.customer_id,
  first_name = customer.first_name,
  last_name = customer.last_name,
  payment_id = NEW.payment_id,
  amount = NEW.amount,
  month_yr = to_char(NEW.payment_date, 'Mon YYYY')
FROM
  customer
WHERE
  customer.customer_id = NEW.customer_id
  AND payment_detail.payment_id = OLD.payment_id;

You have to use OLD.payment_id(not NEW) in WHERE clause to handle cases where payment ID changes.

edited Dec 20, 2021 at 7:29

answered Dec 17, 2021 at 6:57

Julius Tuskenis

1,63010 silver badges15 bronze badges

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

4 Comments

user330315 Over a year ago

This is still wrong as there is no join condition between customer and payment_detail. It should simply be: where payment_detail.customer_id = new.customer_id and no FROM clause

user13717463 Over a year ago

Interestingly enough, when I add AND payment_detail.payment_id = NEW.payment_id to the WHERE clause, the payment_detail table almost comes out perfect this time. Except one thing: The first_name and last_name populates someone else's name, despite the fact that there's only one customer for the given customer_id. Why would that happen?

Julius Tuskenis Over a year ago

@a_horse_with_no_name with no FROM clause you will not be able to change the customer name in this "denormalized" table. Also filtering by new.customer_id only is wrong as you will change all the clients payment history while modifying a single payment. We should only change the info of the payment that was changed. I've modified my answer to include final update statement.

Julius Tuskenis Over a year ago

@user13717463 I guess I was not clear enough: You should add a payment_detail.payment_id = OLD.payment_id to your WHERE part, not replace it. Otherwise I can't imagine how the customer info might not match the customer_id. If you have added the condition and still get the wrong customer info - maybe you could build an example at dbfiddle.uk ? I'd look into it.

Collectives™ on Stack Overflow

How To Prevent Trigger Function from Creating Duplicative Rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related