PostgreSQL任务:计算平均值的触发器

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(10, 2)
);

CREATE OR REPLACE FUNCTION calculate_average()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO average (value) VALUES (NEW.amount);
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE average SET value = (SELECT AVG(amount) FROM sales);
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE average SET value = (SELECT AVG(amount) FROM sales);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_average
AFTER INSERT OR UPDATE OR DELETE ON sales
FOR EACH ROW
EXECUTE FUNCTION calculate_average();