Updated Record Not Showing Up in a Table After a Trigger Update-CodePudding

I created a table, payment_detail, based on two other tables (customer and payment) as follows:

CREATE TABLE IF NOT EXISTS payment_detail AS
SELECT customer.customer_id,
   customer.first_name || ' ' || customer.last_name AS customer_name,
   to_char(payment_date, 'Mon YYYY') AS month_yr,
   SUM(payment.amount) AS total_amount,
   COUNT(payment.amount) AS payments_count
FROM payment
INNER JOIN customer ON payment.customer_id = customer.customer_id
GROUP BY 1,3
ORDER BY
   MAX(date_trunc('year', payment_date)) DESC,
   MAX(date_trunc('month', payment_date)) DESC,
   MAX(amount) DESC;

The resulting table looks like this:

Goal: Create a trigger function to update the payment_detail table upon an UPDATE and an INSERT operation.

Trigger for the UPDATE operation:

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

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

The problem with the above Trigger is that, although it updates the payment table successfully, the updated record no longer shows up in the payment_detail table. For instance, if I perform an UPDATE as follows:

UPDATE payment
SET amount = 9.00
WHERE customer_id = 60 AND payment_id = 32034;

The payment_detail table no longer shows the record for 'Mildred Bailey' as pictured above, in the first row of the table. But if I DROP TABLE payment_detail; and re-run the table creation, then the record appears.

How would I prevent this from happening? I think the answer is rooted in the condition of my WHERE clause, but I could use some guidance on this if that's the case.

Next, is the trigger for the INSERT operation:

CREATE OR REPLACE FUNCTION insert_payment_detail()
   RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
   INSERT INTO payment_detail (customer_id, month_yr, total_amount, payments_count)
   SELECT
      NEW.customer_id,
      to_char(NEW.payment_date, 'Mon YYYY') AS month_yr,
      SUM(NEW.amount) AS total_amount,
      COUNT(NEW.amount) AS payments_count
   FROM customer, payment
   WHERE customer.customer_id = NEW.customer_id AND
   to_chair(payment.payment_date, 'Mon YYYY') = to_char(NEW.payment_date, 'Mon YYYY');
RETURN NULL;
END;
$$

CREATE TRIGGER insert_payment_detail
   AFTER INSERT ON payment
   FOR EACH ROW EXECUTE PROCEDURE insert_payment_detail();

If I apply an INSERT as follows:

INSERT INTO payment (customer_id staff_id, rental_id, amount, payment_date)
VALUES (60, 2, 14741, 4.00, now());

I verify that the payment table holds the new record; however when I run SELECT * FROM payment_detail, it does not show the new record. So in both my UPDATE and INSERT trigger functions, the targeted table just omits the updated/inserted record, until I delete and recreate the table.

What am I doing wrong?

CodePudding user response：

Trigger(s) for UPDATE

(a) You may have an error in your trigger function update_payment_detail(), try to replace

customer_name = first_name || ' ' || last_name

customer_name = NEW.first_name || ' ' || NEW.last_name

(b) The columns of the table payment_detail are copied from both tables customer and payment, so you probably need 2 different trigger functions for update :

CREATE OR REPLACE FUNCTION update_payment_detail()
   RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
   UPDATE payment_detail SET
--   customer_id = NEW.customer_id, -- NEW.customer_id is not possible here if it is a column of the table customer; NEW only refers to the columns of the table payment called by the trigger !
--   customer_name = NEW.first_name || ' ' || NEW.last_name, -- idem as above if these columns come from the table customer
   month_yr = to_char(NEW.payment_date, 'Mon YYYY')
--FROM
--   customer
WHERE
--   customer.customer_id = NEW.customer_id AND
   payment_detail.customer_id = OLD.customer_id ;
RETURN NULL;
END;
$$

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

CREATE OR REPLACE FUNCTION update_customer_detail()
   RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
   UPDATE payment_detail SET
   customer_id = NEW.customer_id,
   customer_name = NEW.first_name || ' ' || NEW.last_name
--   month_yr = to_char(NEW.payment_date, 'Mon YYYY') -- NEW.payment_date is not possible here if it is a column of the table payment; NEW only refers to the columns of the table customer called by the trigger !
--FROM
--   customer
WHERE
   customer.customer_id = OLD.customer_id
--   payment_detail.customer_id = OLD.customer_id ;
RETURN NULL;
END;
$$

CREATE TRIGGER update_customer_detail
   AFTER UPDATE
   ON customer
   FOR EACH ROW
   EXECUTE PROCEDURE update_customer_detail();

Trigger for INSERT

CREATE OR REPLACE FUNCTION insert_payment_detail()
   RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
   INSERT INTO payment_detail (customer_id, month_yr, total_amount, payments_count)
   SELECT
      NEW.customer_id,
      to_char(NEW.payment_date, 'Mon YYYY'),
      SUM(amount),
      COUNT(*)
   FROM payment
   WHERE customer_id = NEW.customer_id AND
   to_char(payment_date, 'Mon YYYY') = to_char(NEW.payment_date, 'Mon YYYY');
RETURN NULL;
END;
$$

CREATE TRIGGER insert_payment_detail
   AFTER INSERT ON payment
   FOR EACH ROW EXECUTE PROCEDURE insert_payment_detail();

(d) Depending on the primary key of the table payment_detail, it is possible that you may have some conflicts when inserting 2 rows in table payment with the same customer_id and the same to_char(payment_date, 'Mon YYYY') value. In order to prevent any conflict, you can add the clause ON CONFLICT DO UPDATE ...

(e) Finally, the table payment_detail could be replaced by a view as proposed by @Adrian Klaver