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

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.

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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related