I"m doing an assignment for school that essentially wants you to make up a business problem for a dvd rental store and then use sql to solve the problem. The business problem i have came up with is to identify the top spending customers. The assignment requires you to create a "details" table and a summary table. The details table extracts the needed data from the customer and then the summary table holds the results, In my case the customers full_name, email, and amount spent. The issue I am having is you have to use a trigger function so that when something is changed in the details table it automatically updates the summary table. For the sake of transparency I don't really understand trigger functions. After two days of attempting to debug I cannot figure out why i was able to create the trigger, but it doesn't update the summary table as expected. Any help is much appreciated. TIA.

​

--Creates details table.

CREATE TABLE details (
customer_id INT NOT NULL,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
email VARCHAR(50) NOT NULL,
amount NUMERIC(6, 2),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

--Extracts raw data from customer table & payment table into details table.

INSERT INTO details (customer_id, first_name, last_name, email, amount)
SELECT customer.customer_id,
       customer.first_name,
       customer.last_name,
       customer.email,
       payment.amount
FROM customer
JOIN payment ON customer.customer_id = payment.customer_id;

--Transforms first_name, last_name columns in details to one full_name 
--column and then drops first_name, last_name columns.

ALTER TABLE details
ADD COLUMN full_name VARCHAR(50);

UPDATE details
SET full_name = CONCAT(details.first_name, ' ', details.last_name);

ALTER TABLE details
DROP COLUMN first_name
DROP COLUMN last_name

-- Creates summary table.

CREATE TABLE summary (
customer_id INT NOT NULL,
full_name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
amount NUMERIC(6,2),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

-- Create a trigger to update the summary table after an update in the details table

CREATE OR REPLACE FUNCTION update_summary()
RETURNS TRIGGER AS $$
BEGIN

     --Update total_amount_spent in the summary table for the affected customer

    UPDATE summary
        SET amount = (
        SELECT SUM(amount)
        FROM details
        WHERE customer_id = NEW.customer_id
    )
    WHERE customer_id = NEW.customer_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Attach the trigger to the details table

CREATE TRIGGER update_summary
AFTER UPDATE ON details
FOR EACH ROW
EXECUTE FUNCTION update_summary();

​