mysqltriggerssubtypesupertype

MySQL trigger to target an attribute of a column


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 ; 

Solution

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