CODEWITHSUNDEEP
postgresql
user13717463
user13717463

Reputation: 97

How To Prevent Trigger Function from Creating Duplicative Rows

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?

Upvotes: 0

Views: 48

Answers (1)

Julius Tuskenis
Julius Tuskenis

Reputation: 1610

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.

Upvotes: 1

Related Questions