Триггер MySQL для нацеливания на атрибут столбца

Я работаю с перекрывающимися отношениями супер/подтипа, связанными с людьми в моей БД. Я хотел бы, чтобы перекрывающиеся подтипы вставляли новые строки, когда супертип получает новую строку. Я приложил свой 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 ;

mysql triggers subtype supertype
person Slyme    schedule 01.11.2018    source источник
comment
Если столбец «Сотрудник» содержит букву «e», а эта запись является «Сотрудник», что вставляется в столбец «Пользователь», если не «u»?   -  person J2112O    schedule 01.11.2018
comment
u вставляется в пользовательский столбец.   -  person Slyme    schedule 01.11.2018
comment
Итак, если я правильно понимаю, при INSERT в таблицу Person столбец сотрудника всегда будет «e», а столбец пользователя будет «u»? Кроме того, user является ключевым словом/зарезервированным словом в MySQL, поэтому имя столбца может вызвать проблемы. См. документацию здесь dev. mysql.com/doc/refman/5.7/en/ .   -  person J2112O    schedule 01.11.2018
comment
Да, это правильное предположение.   -  person Slyme    schedule 01.11.2018
comment
Будет ли каждая запись, помещенная в таблицу Person, иметь соответствующие значения как в таблицах Employee, так и в таблицах Users?   -  person J2112O    schedule 01.11.2018
comment
Да, они будут. Я думал о денормализации в таблицах сотрудников/участников, это более уместно? Я создал super/sub, чтобы упростить взаимодействие с другой таблицей, к которой обе имеют отношение.   -  person Slyme    schedule 01.11.2018
comment
Возможно так. Мне было любопытно узнать значения сотрудников и пользователей, потому что если бы они оба всегда были «e» и «u», то они всегда были бы истинными, и это не было бы хорошим тестом в логике ЕСЛИ.   -  person J2112O    schedule 01.11.2018
comment
Если человек является сотрудником, у него будет e лично, но NULL у участника. Будут ли тогда операторы if работать?   -  person Slyme    schedule 01.11.2018
comment
Я думал, что если у человека будет соответствующая строка в обеих таблицах «Сотрудник» и «Пользователь», независимо от того, какими были значения сотрудника и пользователя, просто покончите с условием IF и выполните необходимые вставки в обе таблицы в BEGIN/END блокировать.   -  person J2112O    schedule 01.11.2018
comment
Всем привет! Что такое поле participant? Я не вижу его в таблице PERSON. Также у вас есть вложенные операторы IF - это правильно? Также я думаю, что вы забыли добавить НОВОЕ ключевое слово для своих чеков (NEW.employee и т. д.)   -  person Anton    schedule 01.11.2018
comment
Я изменил таблицу после публикации, извините за путаницу.   -  person Slyme    schedule 01.11.2018

Ответы (2)


arrow_upward
0
arrow_downward

Это может сработать для вас.

Во-первых, я думаю, что атрибут AUTO_INCREMENT для столбцов EMPLOYEE.eperson_id и PARTICIPANT.pperson_id не нужен.

Поскольку оба этих столбца имеют FOREIGN KEYS и ссылаются на столбец person_id таблицы PERSON, они должны иметь и будут получать свои значения из этого столбца через TRIGGER в любом случае, поэтому нет необходимости автоматически увеличивать их в таблицах. Так что я бы изменил это.

Этот TRIGGER должен работать с заполнением обеих таблиц EMPLOYEE и PARTICIPANT после INSERT в таблице 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 ;

Надеюсь, это поможет вам.

person J2112O    schedule 01.11.2018
comment
Я только что попробовал этот метод. Оба подтипа получают новую строку человека, а не сотрудник или участник, получающий новую строку на основе значений «e» или «p» в таблице людей. Что еще нужно для того, чтобы каждый подтип мог получать только свои атрибутивные данные? - person Slyme; 02.11.2018
comment
Должно быть, я неправильно понял ответы на свои вопросы ранее. У меня сложилось впечатление, что обе таблицы EMPLOYEE и PARTICIPANT будут заполнены после и вставлены в таблицу PERSON независимо от этих значений. Итак, если присутствует значение «e», означает ли это, что строка не является УЧАСТНИКОМ, а только СОТРУДНИКОМ? - person J2112O; 02.11.2018
comment
Да, это именно то, что я надеялся реализовать. Извините за путаницу. - person Slyme; 02.11.2018
comment
Не нужно извиняться. Путаница на мне. Никаких забот. :) - person J2112O; 02.11.2018

arrow_upward
0
arrow_downward

В итоге я нашел два метода решения моей проблемы. В итоге я изменил своих «сотрудников» и «участников» на типы данных 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 ;
person Slyme    schedule 02.11.2018