Oracle 9 PL/SQL触发器创建求助：重复与字符长度校验问题

我来帮你完善这个触发器，针对你遇到的更新操作重复数据检查和插入/更新字符长度限制问题，咱们一步步把代码补全：

完整触发器代码 #

CREATE OR REPLACE TRIGGER check_author 
BEFORE UPDATE OR INSERT ON bk_author 
FOR EACH ROW 
DECLARE
    v_count NUMBER; -- 用于统计重复记录数
BEGIN
    -- 第一步：统一检查字符长度限制（插入/更新都要验证）
    -- 检查fname长度不超过10
    IF LENGTH(:NEW.fname) > 10 THEN
        RAISE_APPLICATION_ERROR(-20004, 'Author first name cannot exceed 10 characters');
    END IF;
    -- 如果有lname字段，也可以添加类似检查（根据实际需求调整长度）
    IF LENGTH(:NEW.lname) > 20 THEN
        RAISE_APPLICATION_ERROR(-20005, 'Author last name cannot exceed 20 characters');
    END IF;

    -- 第二步：处理重复数据检查
    IF INSERTING THEN
        -- 插入时：检查Author ID是否已存在（如果authorid是唯一键）
        SELECT COUNT(*) INTO v_count 
        FROM bk_author 
        WHERE authorid = :NEW.authorid;
        
        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Author ID already exists in the system');
        END IF;

        -- 可选：检查姓名组合是否重复（如果业务要求姓名唯一）
        SELECT COUNT(*) INTO v_count 
        FROM bk_author 
        WHERE fname = :NEW.fname AND lname = :NEW.lname;
        
        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20002, 'Author name (first + last) already exists');
        END IF;
    ELSIF UPDATING THEN
        -- 更新时：检查新的Author ID是否与其他记录重复（排除当前记录的原ID）
        SELECT COUNT(*) INTO v_count 
        FROM bk_author 
        WHERE authorid = :NEW.authorid AND authorid != :OLD.authorid;
        
        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20003, 'New Author ID is already used by another record');
        END IF;

        -- 可选：更新时检查新姓名是否与其他记录重复（排除当前记录的原姓名）
        SELECT COUNT(*) INTO v_count 
        FROM bk_author 
        WHERE fname = :NEW.fname AND lname = :NEW.lname 
          AND (fname != :OLD.fname OR lname != :OLD.lname);
        
        IF v_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20006, 'New Author name is already used by another record');
        END IF;
    END IF;
END;
/

关键细节说明 #

• 字符长度检查：把长度验证放在最前面，不管是插入还是更新都执行，避免重复代码。你可以根据实际表结构，添加其他字段的长度限制。
• 更新时的重复检查：必须加上authorid != :OLD.authorid（或姓名的排除条件），否则当你只是更新该记录的其他字段（比如修改fname但authorid不变），触发器会误判为重复。
• 错误代码：使用-20000到-20999之间的自定义错误码，避免与Oracle内置错误码冲突，同时错误提示要清晰易懂。
• 可选检查：姓名组合的重复验证是基于业务需求的，如果你的系统允许同名作者，可以去掉这部分代码。

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