How to trigger AFTER UPDATE in Postgres-CodePudding

I'm trying to run a procedure/function when a table gets updated (insert/delete/update), however, the function doesn't appear to get run when the trigger occurs or the trigger doesn't get triggered on an insert.

Function and Trigger:

CREATE OR REPLACE FUNCTION fn_rental_trigger() RETURNS TRIGGER AS $$
   BEGIN
    CALL get_top_ten_rentals();
    RETURN NULL;
   END; $$
LANGUAGE plpgsql;

CREATE TRIGGER tr_new_rentals
    AFTER UPDATE ON public.rental
    EXECUTE FUNCTION public.fn_rental_trigger();

insert call:

INSERT INTO public.rental (rental_date, inventory_id, customer_id, return_date, staff_id, last_update)
VALUES (NOW(), 4030, 459, NOW()   interval '7 day', 1, NOW());

The stored procedure works as expected, and when I run it solo I get what I want. Running it from a trigger is failing with every way I attempt, so what's the correct way to run a trigger that executes a working procedure on a table update?

For context, this is based off of the dvd rental database from the postgres tutorial website.

Edit

stored procedure:

CREATE OR REPLACE PROCEDURE get_top_ten_rentals()

AS
$$

-- Start a tansaction to get the data
BEGIN
    
    -- clear out existing data to refresh list
    DELETE FROM report.top_ten_rentals;

    INSERT INTO report.top_ten_rentals (title, inventory_id, rating, length, times_rented, total)
    SELECT f.title AS title,
           r.inventory_id AS inventory_id,
           f.rating,
           fn_transform_length(f.length),
           COUNT(*) AS times_rented,
           SUM(p.amount) AS total
    FROM public.payment AS p
        JOIN public.rental AS r ON p.rental_id = r.rental_id
        JOIN public.inventory AS i ON r.inventory_id = i.inventory_id
        JOIN public.film AS f ON i.film_id = f.film_id
    GROUP BY r.inventory_id, f.title, f.rating, f.length
    ORDER BY total DESC
    LIMIT 10;

    -- Rollback when there is an exception to preserve data integrity
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
END;
$$

LANGUAGE plpgsql;

To answer Adrian Klaver's other questions:

Yes, and that's intentional but open to change.
Because it is a requirement, a silly one, but still required.

I have also attempted to run the trigger as so:

CREATE TRIGGER tr_new_rentals
    AFTER UPDATE ON public.rental
    FOR EACH ROW
      EXECUTE FUNCTION public.fn_rental_trigger();

In case running per row was needed, however, that also does not execute the procedure so the table I'm trying to update never receives any data.

CodePudding user response：

To fire your trigger for all data change events, code the event as INSERT OR UPDATE OR DELETE:

CREATE TRIGGER tr_new_rentals
AFTER INSERT OR UPDATE OR DELETE ON public.rental
FOR EACH ROW
    EXECUTE FUNCTION public.fn_rental_trigger()

Since the top 10 data depends only on data in tables, and not the event (insert, update or delete) that caused the data to change, the trigger may be safely defined as one trigger for all data change events.