MySQL 触发器以列的属性为目标

Question

我正在处理与数据库中的人员有关的重叠超/子类型关系。我想做的是当父类(super class)型获得新行时让重叠的子类型插入新行。我已附上我的 LRD 以澄清这种关系。 LRD我想创建一个触发器，根据人员表中的属性员工/用户将新人员行插入到正确的子类型中。到目前为止，我尝试的代码在将行插入人员时出现错误，指出“员工列不存在”。我认为这是因为这段代码试图将 if 语句用于实际上不存在的子类型。

如有任何反馈，我将不胜感激。表详细信息

    CREATE TABLE PERSON
(person_id int(10) not null AUTO_INCREMENT,
first_name varchar(15) not null,
last_name varchar(15) not null,
employee char(1),
participant char(1),
CONSTRAINT person_pk PRIMARY KEY (person_id))
ENGINE=InnoDB;

CREATE TABLE EMPLOYEE
(eperson_id int(10) not null AUTO_INCREMENT,
enterprise_email varchar(30),
manager_id int(10),
CONSTRAINT employee_pk PRIMARY KEY (eperson_id),
CONSTRAINT employee_fk1 FOREIGN KEY(eperson_id) REFERENCES PERSON(person_id) ON update cascade,
CONSTRAINT employee_fk2 FOREIGN KEY(manager_id) REFERENCES EMPLOYEE(eperson_id) ON update cascade)
ENGINE=InnoDB;

 CREATE TABLE PARTICIPANT
(pperson_id int(10) not null AUTO_INCREMENT,
city varchar(30),
state varchar(2),
zip int(5),
sign_up_date date,
termination_date date,
CONSTRAINT participant_pk PRIMARY KEY (pperson_id),
CONSTRAINT participant_fk FOREIGN KEY(pperson_id) REFERENCES PERSON(person_id) ON update cascade)
ENGINE=InnoDB;

触发代码

 DELIMITER //
    CREATE TRIGGER subtype_creator
    AFTER INSERT ON PERSON 
    FOR EACH ROW 
BEGIN
    IF  (employee = ‘e’ ) THEN
    INSERT INTO EMPLOYEE
    SET eperson_id = NEW.person_id,
        last_name = NEW.last_name,
        enterprise_email = NULL,
        manager_id = NULL;
   IF  (participant = ‘p’ )THEN
  INSERT INTO PARTICIPANT
    SET pperson_id = NEW.person_id,
    city=NULL,
    state = NULL,
    zip = NULL,
    sign_up_date =NULL,
    termination_date = NULL;
    END IF;
END IF;
END//
DELIMITER ; 

Answer 1

这可能对你有用。

首先，我认为不需要在列 EMPLOYEE.eperson_id 和 PARTICIPANT.pperson_id 上添加 AUTO_INCRMENT 属性。

由于这两列都是 FOREIGN KEYS 并且引用回表 PERSON 的 person_id 列，因此它们需要具有，并且无论如何，都将通过 TRIGGER 从该列获取它们的值，因此无需在表中自动递增它们。所以我会改变这一点。

此 TRIGGER 应该适用于在表 PERSON 上的 INSERT 之后填充表 EMPLOYEE 和 PARTICIPANT :

DELIMITER //
    CREATE TRIGGER subtype_creator
    AFTER INSERT ON PERSON 
    FOR EACH ROW 
BEGIN
    INSERT INTO EMPLOYEE(eperson_id, enterprise_email, manager_id)
    VALUES(NEW.person_id, NULL, NULL);
    INSERT INTO PARTICIPANT(pperson_id, city, state, zip, sign_up_date, termination_date)
    VALUES(NEW.person_id, NULL, NULL, NULL, NULL, NULL);
END//
DELIMITER ;

希望这对您有帮助。