MySQL trigger to target an attribute of a column

Question

I am working with an overlap super/subtype relationship dealing with person(s) in my DB. What I would like to do is have the overlapping subtypes insert new rows when the supertype gains a new row. I have attached my LRD to clarify the relationship. LRD I would like to create a trigger that inserts new person rows into the correct subtype based on the attributes employee/user in the person table. The code I have attempted so far gives me an error upon inserting rows into person noting "employee column does not exist". I would assume this is because this code is trying to use the if statement for the subtypes where it is in fact absent.

I would appreciate any feedback. Table Details

    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;

Trigger Code

 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 ;

If the employee column contains an 'e' and that record is an Employee, what is inserted into the user column if not a 'u'? — J2112O, Nov 01 '18 at 11:38
So if I understand correctly, on INSERT into table Person, the employee column will always be 'e' and the user column will be a 'u'? Also user is a keyword/reserved word in MySQL so that column name is going to cause problems. See the documentation here https://dev.mysql.com/doc/refman/5.7/en/keywords.html#keywords-5-7-detailed-U . — J2112O, Nov 01 '18 at 18:07
Will every record that's put into table Person have corresponding values in both tables Employee and table Users? — J2112O, Nov 01 '18 at 19:52
Yes they will. I have thought about denormalizing into employee/participant tables is that more appropriate? I created the super/sub to simplify the interaction with an other table that both have a relation to. — Slyme, Nov 01 '18 at 20:01
Possibly so. I was curious about the values of employee and user because if they both were always 'e' and 'u' then they would always be true and it wouldn't be a good test in the IF logic. — J2112O, Nov 01 '18 at 20:23
If a person is an employee they will have an e in person but a NULL in participant. Would the if statements still work then? — Slyme, Nov 01 '18 at 20:38
I was thinking that if a Person is going to have a corresponding row in both tables Employee and User no matter what the employee and user values were, just do away with the IF conditional and perform the needed inserts in both tables in the BEGIN/END block. — J2112O, Nov 01 '18 at 20:44
Hi there! What is `participant` field? I can't see it in PERSON table. Also you have nested IF statements - is it correct? Also I think you forgot to add NEW keyword for your checks (`NEW.employee` etc.) — Anton, Nov 01 '18 at 20:44

score 0 · Answer 1 · answered Nov 01 '18 at 21:55

0

This may work for you.

First off, I think to have the AUTO_INCREMENT attribute on columns EMPLOYEE.eperson_id and PARTICIPANT.pperson_id is not needed.

Since both of those columns are FOREIGN KEYS and are referencing back to the person_id column of table PERSON, they need to have, and will be getting, their values from that column through the TRIGGER anyway so no need to autoincrement them in the tables. So I would change that.

This TRIGGER should work with populating both tables EMPLOYEE and PARTICIPANT after INSERT on 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 ;

Hope this helps you.

answered Nov 01 '18 at 21:55

J2112O

557
1
7
20

I just tried this method. Both subtypes gain the new person row rather than either employee or participant gaining a new row based on the 'e' or 'p' values in the person table. What else is needed to enable each subtype to only gain their attributed data? – Slyme Nov 01 '18 at 22:07
I must have misunderstood the responses to my questions from earlier. I was under the impression that both tables EMPLOYEE and PARTICIPANT would be populated after and insert on table PERSON regardless of those values. So if the 'e' value is present, does that mean that row is not a PARTICIPANT and only an EMPLOYEE? – J2112O Nov 01 '18 at 22:15
Yes that is exactly what I was hoping to implement. Sorry for the confusion. – Slyme Nov 02 '18 at 02:21
No need to apologize. The confusion is on me. No worries at all. :) – J2112O Nov 02 '18 at 08:32

score 0 · Accepted Answer · answered Nov 02 '18 at 07:26

I ended up figuring out two methods to solve my issue. I ended up altering my 'employee' and 'participant' into boolean/tinyint data types.

    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;

After that alteration I decided to try and break up the one trigger into two. This was successful.

Type 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 ;

After inplementing that first option I realized the ELSEIF would allow me to not split the two and create a single trigger.

Type 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 ;

MySQL trigger to target an attribute of a column

2 Answers2