创建触发器对比两表日期实现Plan日期在所属Project区间校验

现有代码的错误点 #

• 字段类型错误：Plan表中endDate字段类型误写为DARE，正确应为DATE
• 约束逻辑错误：表级CHECK约束仅能校验当前行的字段值，无法直接跨表引用Project表的startDate、endDate字段，此处写在Plan表CHECK里的跨表校验规则完全不生效
• 默认值错误：Plan表的startDate、endDate默认值设为空格不符合DATE类型要求，会触发插入默认值报错，需要调整为合法日期值
• 触发器逻辑错误：现有触发器写了无意义的Project表自连接，未关联要插入的Plan行的外键projectID，也没有日期范围判断和错误抛出逻辑

修正后的实现步骤 #

1. 修正建表语句 #

首先删掉Plan表中无效的跨表CHECK规则，修正字段类型和默认值：

%%sql
DROP TABLE IF EXISTS Project;
CREATE TABLE Project (
    projectID varchar(255) NOT NULL UNIQUE,
    name varchar(255) NOT NULL DEFAULT ' ',
    leader varchar(255) NOT NULL DEFAULT ' ',
    budget varchar(255) NOT NULL DEFAULT '0',
    startDate DATE NOT NULL DEFAULT '2000-12-31',
    endDate DATE NOT NULL DEFAULT '2000-12-31'
    CHECK (JulianDay(startDate) <= JulianDay(endDate)),
    PRIMARY KEY (projectID)
);

%%sql
DROP TABLE IF EXISTS Plan;
CREATE TABLE Plan (
    pID varchar(255) NOT NULL UNIQUE,
    projectID varchar(255) DEFAULT NULL,
    name varchar(255) NOT NULL DEFAULT ' ',
    startDate DATE NOT NULL DEFAULT '2000-12-31',
    endDate DATE NOT NULL DEFAULT '2000-12-31'
    CHECK (JulianDay(startDate) <= JulianDay(endDate)),
    PRIMARY KEY (pID, projectID),
    FOREIGN KEY (projectID) REFERENCES Project(projectID)
);

2. 编写正确的触发器 #

需要同时覆盖INSERT和UPDATE场景，避免插入或修改Plan时日期超出所属Project范围，以下为适配SQLite的触发器实现（从你使用的JulianDay函数判断为SQLite环境）：

插入校验触发器

CREATE TRIGGER beforeInsertInPlan BEFORE INSERT ON Plan FOR EACH ROW
BEGIN
    SELECT RAISE(FAIL, 'Plan的起止日期不能超出所属Project的日期范围')
    WHERE EXISTS (
        SELECT 1 FROM Project
        WHERE Project.projectID = NEW.projectID
        AND (
            JulianDay(NEW.startDate) < JulianDay(Project.startDate)
            OR JulianDay(NEW.endDate) > JulianDay(Project.endDate)
        )
    );
END;

更新校验触发器

CREATE TRIGGER beforeUpdateInPlan BEFORE UPDATE ON Plan FOR EACH ROW
BEGIN
    SELECT RAISE(FAIL, 'Plan的起止日期不能超出所属Project的日期范围')
    WHERE EXISTS (
        SELECT 1 FROM Project
        WHERE Project.projectID = NEW.projectID
        AND (
            JulianDay(NEW.startDate) < JulianDay(Project.startDate)
            OR JulianDay(NEW.endDate) > JulianDay(Project.endDate)
        )
    );
END;

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