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

r55awzrz 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(260)

我正在处理与数据库中的人的重叠超级/子类型关系。我想做的是让重叠的子类型在父类型获得新行时插入新行。我已附上我的lrd，以澄清关系。lrd我想创建一个触发器，根据person表中employee/user的属性将新的person行插入到正确的子类型中。到目前为止，我尝试的代码在将行插入到person中并注明“employee列不存在”时给出了一个错误。我假设这是因为这段代码试图将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 ;

mysql triggers subtype supertype

来源：https://stackoverflow.com/questions/53099154/mysql-trigger-to-target-an-attribute-of-a-column

2条答案

按热度按时间

5jvtdoz21#

最后我想出了两种方法来解决我的问题。最后，我将“employee”和“participant”更改为boolean/tinyint数据类型。

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

在那次改动之后，我决定试着把一个触发器分成两个。这是成功的。
类型1

DELIMITER //
CREATE TRIGGER employee_creator
    AFTER INSERT ON PERSON 
    FOR EACH ROW 
BEGIN
    IF  (NEW.employee = 1 ) THEN
            INSERT INTO EMPLOYEE
        SET eperson_id = NEW.person_id,
            last_name = NEW.last_name,
                enterprise_email = NULL,
                manager_id = NULL;
    END IF;
END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER participant_creator
    AFTER INSERT ON PERSON 
    FOR EACH ROW 
BEGIN
    IF  (NEW.participant =0 )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//
DELIMITER ;

在完成第一个选项后，我意识到elseif将允许我不拆分这两个选项，而创建一个触发器。
类型2

DELIMITER //
CREATE TRIGGER employee_creator
    AFTER INSERT ON PERSON 
    FOR EACH ROW 
BEGIN
    IF  (NEW.employee = 1 ) THEN
            INSERT INTO EMPLOYEE
        SET eperson_id = NEW.person_id,
            last_name = NEW.last_name,
                enterprise_email = NULL,
                manager_id = NULL;

    ELSEIF  (NEW.participant =0 )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//
DELIMITER ;

赞(0）回复(0）举报 2021-06-18

y4ekin9u2#

这也许对你有用。
首先，我认为 AUTO_INCREMENT 列上的属性 EMPLOYEE.eperson_id 以及 PARTICIPANT.pperson_id 不需要。
因为这两列都是 FOREIGN KEYS 并引用了 person_id 表的列 PERSON ，它们需要并将从该列通过 TRIGGER 所以不需要在表中自动增加它们。所以我要改变这一点。
这个 TRIGGER 应该填充两个表 EMPLOYEE 以及 PARTICIPANT 之后 INSERT 在table上 PERSON :

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 ;

希望这对你有帮助。

赞(0）回复(0）举报 2021-06-18

我来回答

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

2条答案

相关问题

热门标签

最新问答