MYSQL 触发器中的多个事件

Question

如何为这两个 CHECK 约束创建触发器

create table section ( course_id varchar(8), 
    sec_id varchar(8), 
    semester varchar(6) 
    check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
    year numeric(4,0) 
    check (year > 1701 and year < 2100), 
    building varchar(15), 
    room_number varchar(7), 
    time_slot_id varchar(4), 
    primary key (course_id, sec_id, semester, year), 
    foreign key (course_id) references course on delete cascade, 
    foreign key (building, room_number) references classroom on delete set null);

================================================== =========================

除了解决方案。

Answer 1

乌扎尔，

正如文章“Validating data using a TRIGGER”中所述，您应该使用 BEFORE INSERT 触发器来验证您的数据，如下所示:

-- validate semester
CREATE TRIGGER `semester_validate_semester_insert`
    BEFORE INSERT
    ON `section`
    FOR EACH ROW
    BEGIN
            IF NEW.`semester` NOT IN ('Fall', 'Winter', 'Spring', 'Summer') THEN
        SIGNAL SQLSTATE VALUE '45000'
            SET MESSAGE_TEXT = '[table:section] - `semester` column is not valid';
    END IF;
END;

和

-- validate year
CREATE TRIGGER `semester_validate_year_insert`
    BEFORE INSERT
    ON `section`
    FOR EACH ROW
    BEGIN
            IF NEW.`year` <= 1701 OR NEW.`year` >= 2100) THEN
        SIGNAL SQLSTATE VALUE '45000'
            SET MESSAGE_TEXT = '[table:section] - `YEAR` column is not valid';
    END IF;
END;

如果您有任何疑问，请告诉我!