PostgreSQL触发器更新行触发非空约束错误排查与修正

表结构 #

sign_id                 INT NOT NULL,
employee_id             INT NOT NULL,
sign_term_in            CHARACTER(3) NOT NULL,
sign_data_in            TIMESTAMP NOT NULL,
sign_term_out           CHARACTER(3),
sign_data_out           TIMESTAMP,
sign_time_minutes       INT,

主键为sign_id。

需求说明 #

• 插入employee_id和sign_term_in时，自动生成sign_id（最大值+1）和当前时间的sign_data_in，其余字段为null；
• 插入employee_id和sign_term_out时，更新对应员工未签出的记录，填充sign_term_out、当前时间的sign_data_out，并计算sign_time_minutes（签出与签到时间差的分钟数）；
• 约束规则：不可同时填签到签出字段；未签出时无法再次签到；已签出时无法再次签出。

现有实现代码 #

检查函数 #

CREATE OR REPLACE FUNCTION erp.fn_check_sign_inserted_values(new_sign erp.tb_sign_employees)
RETURNS BOOLEAN AS $$
BEGIN
    IF new_sign.sign_term_in IS NULL AND new_sign.sign_term_out IS NULL THEN
        RAISE EXCEPTION 'Cal informar de sign_term_in o sign_term_out';
    END IF;

    IF new_sign.sign_term_in IS NOT NULL AND new_sign.sign_term_out IS NOT NULL THEN
        RAISE EXCEPTION 'Cal informar de sign_term_in o sign_term_out, no els dos';
    END IF;

    IF new_sign.sign_term_in IS NOT NULL THEN
        IF new_sign.employee_id IS NULL THEN
            RAISE EXCEPTION 'Cal informar del id del empleat';
        END IF;

        IF EXISTS (
            SELECT 1
            FROM erp.tb_sign_employees
            WHERE employee_id = new_sign.employee_id
            AND sign_term_out IS NULL
        ) THEN
            RAISE EXCEPTION E'Ja hi ha un registre d''entrada sense sortida';
        END IF;
    END IF;

    IF new_sign.sign_term_out IS NOT NULL THEN
        IF new_sign.employee_id IS NULL THEN
            RAISE EXCEPTION 'Cal informar del id del empleat';
        END IF;

        IF NOT EXISTS (
            SELECT 1
            FROM erp.tb_sign_employees
            WHERE employee_id = new_sign.employee_id
            AND sign_term_out IS NULL
        ) THEN
            RAISE EXCEPTION E'Ja hi ha un registre d''entrada sense sortida';
        END IF;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

触发器函数与触发器 #

CREATE OR REPLACE FUNCTION erp.tg_sign_inserted()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT erp.fn_check_sign_inserted_values(NEW) THEN
        RETURN NULL;
    END IF;

    IF NEW.sign_term_in IS NOT NULL THEN
        INSERT INTO erp.tb_sign_employees (sign_id, employee_id, sign_term_in, sign_data_in)
        SELECT COALESCE(MAX(sign_id), 0) + 1, NEW.employee_id, NEW.sign_term_in, current_timestamp
        FROM erp.tb_sign_employees;
    ELSIF NEW.sign_term_out IS NOT NULL THEN
        UPDATE erp.tb_sign_employees
        SET sign_term_out = NEW.sign_term_out,
            sign_data_out = current_timestamp,
            sign_time_minutes = extract(epoch from current_timestamp - sign_data_in) / 60
        WHERE employee_id = NEW.employee_id
        AND sign_term_out IS NULL
        AND sign_term_in IS NOT NULL; -- Agregamos esta condición para actualizar solo las filas con sign_term_in ya definido.
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_insert_sign_employee
BEFORE INSERT ON erp.tb_sign_employees
FOR EACH ROW
EXECUTE FUNCTION erp.tg_sign_inserted()

问题现象 #

执行签到语句：

INSERT INTO erp.tb_sign_employees(employee_id, sign_term_in)
VALUES(1, 'Terminal1')

可正常生成记录；但执行签出语句：

INSERT INTO erp.tb_sign_employees(employee_id, sign_term_out)
VALUES(1, 'Terminal1')

时，未更新已有签到记录，反而尝试创建新行（sign_id为null），触发sign_id非空约束错误，同时未正确填充sign_data_out。

预期行为示例 #

假设当前最大sign_id为1，9点执行签到插入后生成记录：

2, 1, Terminal1, 2023-03-06 09:14:00, null, null, null

12点执行签出插入后，该记录应更新为：

2, 1, Terminal1, 2023-03-06 09:00:00, Terminal2, 2023-03-06 12:00:00, 180

其中180为时间差的分钟数。

问题分析 #

1. 签出逻辑错误：BEFORE INSERT触发器中，处理签出时执行UPDATE后仍然RETURN NEW，导致原INSERT操作继续执行。由于插入的行未提供sign_id，触发非空约束。正确做法是签出时不执行INSERT，直接返回NULL终止原插入操作。
2. 错误提示信息错误：签出时的异常提示文本错误，应该提示“没有未签出的签到记录”，而非和签到相同的提示。
3. 并发风险：手动用MAX(sign_id)+1生成主键存在并发插入时的主键冲突风险，建议改用序列，但暂时先修复核心逻辑问题。

修正后的代码 #

修正检查函数（修复错误提示） #

CREATE OR REPLACE FUNCTION erp.fn_check_sign_inserted_values(new_sign erp.tb_sign_employees)
RETURNS BOOLEAN AS $$
BEGIN
    IF new_sign.sign_term_in IS NULL AND new_sign.sign_term_out IS NULL THEN
        RAISE EXCEPTION 'Cal informar de sign_term_in o sign_term_out';
    END IF;

    IF new_sign.sign_term_in IS NOT NULL AND new_sign.sign_term_out IS NOT NULL THEN
        RAISE EXCEPTION 'Cal informar de sign_term_in o sign_term_out, no els dos';
    END IF;

    IF new_sign.sign_term_in IS NOT NULL THEN
        IF new_sign.employee_id IS NULL THEN
            RAISE EXCEPTION 'Cal informar del id del empleat';
        END IF;

        IF EXISTS (
            SELECT 1
            FROM erp.tb_sign_employees
            WHERE employee_id = new_sign.employee_id
            AND sign_term_out IS NULL
        ) THEN
            RAISE EXCEPTION E'Ja hi ha un registre d''entrada sense sortida';
        END IF;
    END IF;

    IF new_sign.sign_term_out IS NOT NULL THEN
        IF new_sign.employee_id IS NULL THEN
            RAISE EXCEPTION 'Cal informar del id del empleat';
        END IF;

        IF NOT EXISTS (
            SELECT 1
            FROM erp.tb_sign_employees
            WHERE employee_id = new_sign.employee_id
            AND sign_term_out IS NULL
        ) THEN
            RAISE EXCEPTION E'No hi ha cap registre d''entrada sense sortida per a aquest empleat';
        END IF;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

修正触发器函数（终止签出时的插入操作） #

CREATE OR REPLACE FUNCTION erp.tg_sign_inserted()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT erp.fn_check_sign_inserted_values(NEW) THEN
        RETURN NULL;
    END IF;

    IF NEW.sign_term_in IS NOT NULL THEN
        -- 生成签到记录，自动填充sign_id和sign_data_in
        NEW.sign_id = COALESCE((SELECT MAX(sign_id) FROM erp.tb_sign_employees), 0) + 1;
        NEW.sign_data_in = current_timestamp;
        -- 清空其他字段确保为null
        NEW.sign_term_out = NULL;
        NEW.sign_data_out = NULL;
        NEW.sign_time_minutes = NULL;
    ELSIF NEW.sign_term_out IS NOT NULL THEN
        -- 执行签出更新操作
        UPDATE erp.tb_sign_employees
        SET sign_term_out = NEW.sign_term_out,
            sign_data_out = current_timestamp,
            sign_time_minutes = extract(epoch from current_timestamp - sign_data_in) / 60
        WHERE employee_id = NEW.employee_id
        AND sign_term_out IS NULL
        AND sign_term_in IS NOT NULL;
        
        -- 返回NULL终止原INSERT操作，避免创建新行
        RETURN NULL;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

触发器保持不变 #

CREATE TRIGGER tg_insert_sign_employee
BEFORE INSERT ON erp.tb_sign_employees
FOR EACH ROW
EXECUTE FUNCTION erp.tg_sign_inserted()

修正说明 #

1. 签到逻辑优化：不再手动执行INSERT，而是直接修改NEW对象的字段，由BEFORE INSERT触发器自动插入，避免重复插入。
2. 签出逻辑修复：执行UPDATE后返回NULL，终止原INSERT操作，防止创建无主键的新行。
3. 错误提示修正：签出时的异常提示更准确，便于排查问题。

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