将active列1/0转为是/否并通过触发器自动更新的技术问询

核心需求：将数据表中active列的数值1转换为“是”、0转换为“否”，通过触发器实现自动更新，但目前执行更新后，Detailed_customers和Summary_customers表无法显示对应的“是/否”值。

现有代码存在的问题 #

1. 存储过程语法错误：WHERE子句用逗号分隔条件（WHERE active = 1, store_id = 1），正确语法应为AND；插入列不匹配（Detailed_customers最后一列是last_update，但SELECT中取的是store.store_id；Summary_customers的return_date列重复插入rental.rental_date）。
2. 未实现active值转换：存储过程的INSERT语句直接插入原始的1/0，未转换为“是/否”。
3. 触发器配置错误：触发器为语句级（FOR EACH STATEMENT），无法逐行处理数据；调用的active_status()函数未定义；触发器类型为AFTER UPDATE，无法在更新时修改当前行的active值。

修正后的完整实现 #

1. 创建触发器函数（实现active值转换） #

CREATE OR REPLACE FUNCTION active_status()
RETURNS TRIGGER AS $$
BEGIN
    -- 转换active值：1→"是"，0→"否"
    NEW.active := CASE NEW.active
        WHEN '1' THEN '是'
        WHEN '0' THEN '否'
        ELSE NEW.active
    END;
    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

2. 创建行级触发器（插入/更新时自动转换） #

-- 针对Detailed_customers的插入/更新触发器
CREATE TRIGGER Detailed_active_convert
BEFORE INSERT OR UPDATE ON Detailed_customers
FOR EACH ROW
EXECUTE FUNCTION active_status();

-- 针对Summary_customers的插入/更新触发器
CREATE TRIGGER Summary_active_convert
BEFORE INSERT OR UPDATE ON Summary_customers
FOR EACH ROW
EXECUTE FUNCTION active_status();

3. 修正存储过程refreshed_data() #

CREATE OR REPLACE PROCEDURE refreshed_data()
LANGUAGE PLPGSQL
AS $$
BEGIN
    DELETE FROM Detailed_customers;
    DELETE FROM Summary_customers;

    -- 插入Detailed_customers，同时转换active值
    INSERT INTO Detailed_customers(
        customer_id, first_name, last_name, active, rental_date, return_date, last_update
    )
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        CASE c.active WHEN 1 THEN '是' ELSE '否' END AS active,
        r.rental_date,
        r.return_date,
        CURRENT_TIMESTAMP AS last_update
    FROM customer c
    INNER JOIN rental r ON c.customer_id = r.customer_id
    INNER JOIN store s ON s.store_id = c.store_id
    WHERE c.active = 1 AND s.store_id = 1
    GROUP BY c.customer_id, c.first_name, c.last_name, c.active, r.rental_date, r.return_date
    ORDER BY c.first_name ASC;

    -- 插入Summary_customers，同时转换active值
    INSERT INTO Summary_customers(
        customer_id, active, rental_date, return_date
    )
    SELECT 
        c.customer_id,
        CASE c.active WHEN 1 THEN '是' ELSE '否' END AS active,
        r.rental_date,
        r.return_date
    FROM customer c
    INNER JOIN rental r ON c.customer_id = r.customer_id
    INNER JOIN store s ON s.store_id = c.store_id
    WHERE c.active = 1 AND s.store_id = 1
    GROUP BY c.customer_id, c.active, r.rental_date, r.return_date;
END;
$$;

4. 执行验证 #

CALL refreshed_data();
SELECT * FROM Summary_customers;
SELECT * FROM Detailed_customers;

关键说明 #

• 使用BEFORE INSERT OR UPDATE触发器，确保数据写入表之前完成active值转换。
• 存储过程中通过CASE直接转换active值，避免依赖触发器也能插入正确数据（双重保障）。
• 修正了存储过程中列不匹配、语法错误等问题，确保数据插入正常。

内容的提问来源于stack exchange，提问作者Jayyvonne26