手机维修店数据库客户维修工单数量限制触发器创建问询

Hey there! Let's tackle this problem step by step. First, I noticed a few small syntax issues in your table definitions that might cause problems later—let's fix those first, then build the trigger you need.

第一步：修正表结构的语法错误 #

Your original tables have minor syntax issues (like spaces in table names, missing commas, unlinked foreign keys). Here's the corrected version with proper constraints to ensure data integrity:

-- 客户表
CREATE TABLE CUSTOMERS (
    CUSTOMER_CPR NUMBER(9) PRIMARY KEY,
    FIRST_NAME VARCHAR2(30) NOT NULL,
    MID VARCHAR2(30), -- 补充缺失的逗号
    LAST_NAME VARCHAR2(30) NOT NULL,
    EMAIL VARCHAR2(30) NOT NULL UNIQUE,
    HOME_TEL NUMBER(8) UNIQUE,
    HOUSE_NO VARCHAR2(30) NOT NULL,
    ROAD_NO VARCHAR2(30) NOT NULL,
    BLOCK_NO VARCHAR2(30) NOT NULL,
    CITY VARCHAR2(30) NOT NULL
);

-- 手机表
CREATE TABLE MOBILES (
    MOB_ID NUMBER(8,0) PRIMARY KEY,
    MODEL VARCHAR2(30) NOT NULL,
    MOB_DECRIPTION VARCHAR2(100),
    SERIAL_NUM VARCHAR2(30) UNIQUE,
    MAKE VARCHAR2(50),
    customer_cpr NUMBER(9),
    -- 添加外键关联客户表
    FOREIGN KEY (customer_cpr) REFERENCES CUSTOMERS(CUSTOMER_CPR)
);

-- 维修工单表（修正表名空格问题，改为REPAIR_JOB）
CREATE TABLE REPAIR_JOB (
    JOB_NUM NUMBER PRIMARY KEY,
    customer_cpr NUMBER(9),
    EMP_ID NUMBER(8,0) NOT NULL,
    DATE_RECEIVED DATE,
    DATE_TO_RETURN DATE,
    ITEM_ID NUMBER,
    -- 添加外键关联客户表和手机表
    FOREIGN KEY (customer_cpr) REFERENCES CUSTOMERS(CUSTOMER_CPR),
    FOREIGN KEY (ITEM_ID) REFERENCES MOBILES(MOB_ID)
);

第二步：创建限制工单数量的触发器 #

We need a BEFORE INSERT trigger that checks how many unfinished repair jobs a customer has before allowing a new one. Unfinished jobs are those where DATE_TO_RETURN is either NULL (not yet repaired) or later than the current date (not yet returned to the customer).

Here's the trigger code:

CREATE OR REPLACE TRIGGER TRG_LIMIT_CUSTOMER_JOBS
BEFORE INSERT ON REPAIR_JOB
FOR EACH ROW
DECLARE
    v_unfinished_jobs NUMBER;
BEGIN
    -- 查询该客户当前未完成的维修工单数量
    SELECT COUNT(*)
    INTO v_unfinished_jobs
    FROM REPAIR_JOB
    WHERE customer_cpr = :NEW.customer_cpr
      AND (DATE_TO_RETURN IS NULL OR DATE_TO_RETURN > SYSDATE);

    -- 如果未完成工单数量 >=3，抛出错误阻止插入
    IF v_unfinished_jobs >= 3 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            '该客户当前已有3个未完成的维修工单，无法新增新的维修请求，请先完成已有工单'
        );
    END IF;
END;
/

代码解释 #

• Trigger Timing: BEFORE INSERT ensures we check the condition before the new job is added, preventing invalid data from being saved.
• FOR EACH ROW: Runs the check for every new repair job record being inserted.
• v_unfinished_jobs: A variable to store the count of the customer's ongoing repair jobs.
• RAISE_APPLICATION_ERROR: Custom error message that will be thrown if the customer reaches the 3-job limit, making the insert fail gracefully.

额外注意点 #

• If you consider a job "finished" once DATE_TO_RETURN is set (regardless of whether it's past the current date), you can adjust the WHERE clause to just DATE_TO_RETURN IS NULL.
• For high-concurrency scenarios (e.g., two staff members trying to add a job for the same customer at the exact same time), you might want to add a table-level lock or use a sequence with a check constraint to avoid race conditions. But for most small repair store use cases, the trigger above should work perfectly.

备注：内容来源于stack exchange，提问作者Sebti Safa