Trigger Function Doesn't Update the Specified Table-CodePudding

I have a table, payment_detail, that is comprised of an inner join from two other tables (customer & payment), created as follows:

CREATE TABLE IF NOT EXISTS payment_detail AS
SELECT customer.customer_id, customer.first_name, customer.last_name, payment.payment_id, payment.amount)
FROM payment
INNER JOIN customer ON payment.customer_id = customer.customer_id;

Now I want to create a trigger function that will update this detail table. This is my current trigger function:

CREATE OR REPLACE FUNCTION update_payment_detail()
   RETURNS TRIGGER
   LANGUAGE PLPGSQL
   AS
$$
BEGIN
   UPDATE payment_detail
      SET
      customer_id = customer.customer_id,
      first_name = customer.first_name,
      last_name = customer.last_name,
      payment_id = payment.payment_id,
      amount = payment.amount
      FROM
         payment
         INNER JOIN customer ON payment.customer_id = customer.customer_id
      WHERE customer.customer_id = payment_detail.customer_id;
      RETURN NULL;
END;
$$

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

So, the way it's supposed to work is that when an INSERT or UPDATE operation occurs on the payment table, the trigger function is invoked and the update_payment_detail() procedure is executed, which should result in the payment_detail table being updated. I test the trigger by inserting a new row into the payment table:

INSERT INTO payment(customer_id, staff_id, rental_id, amount, payment_date)
VALUES (1, 2, 1849, 4.25, now());

The payment table updates perfectly fine; I can see the inserted row. However, the table targeted for the trigger to update, payment_detail, does not show the new row. So the trigger fails to update the payment_detail table. How could I resolve this, based on the code above?

CodePudding user response：

(a) Should you insert a new row in table payment_detail when inserting a new row in table payment ? If so, you have to create two separated trigger functions insert_payment_detail() and update_payment_detail().

(b) In the trigger function you have to refer to the fields of the current row which is inserted/updated in table payment by using the NEW variable, see the manual.

As a result, the trigger functions may be :

CREATE OR REPLACE FUNCTION insert_payment_detail()
   RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
   INSER INTO payment_detail (customer_id, first_name, last_name, payment_id, amount )
      SELECT NEW.customer_id, customer.first_name, customer.last_name, NEW.payment_id, NEW.amount
      FROM customer
      WHERE customer.customer_id = NEW.customer_id;
      RETURN NULL;
END;
$$ ;

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

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
      FROM customer
      WHERE 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();

(c) Instead of duplicating the data from tables customer and payment into table payment_detail, you could create a view payment_detail, see the manual :

CREATE OR REPLACE VIEW payment_detail (customer_id, first_name, last_name, payment_id, amount ) AS
SELECT customer.customer_id, customer.first_name, customer.last_name, payment.payment_id, payment.amount)
FROM payment
INNER JOIN customer ON payment.customer_id = customer.customer_id ;