How can I fetch parent table's value within a trigger?

Question

I have a PARENT table and a CHILD table.

PARENT
------
ID BIGINT PRIMARY KEY
NAME VARCHAR(255) NOT NULL
CHILD
-----
ID BIGINT PRIMARY KEY
NAME VARCHAR(255) NOT NULL
PARENT_ID BIGINT NOT NULL

I want to create a trigger which does

• when a CHILD row inserted or updated
• check the NEW.NAME
• and if the NEW.NAME is NULL or EMPTY
• use PARENT's NAME as NEW.NAME

How can I do that?

Answer 1

Tables:

create table PARENT
(
ID BIGINT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL
);
create table CHILD
(
ID BIGINT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL,
PARENT_ID BIGINT NOT NULL
);

Trigger:

DELIMITER $$
 CREATE TRIGGER namecheck BEFORE INSERT ON CHILD
 FOR EACH ROW
BEGIN
 DECLARE parentname VARCHAR(255);
 IF NEW.NAME IS NULL OR NEW.NAME=''
 THEN
 SELECT NAME
 INTO @parentname
 FROM PARENT 
 WHERE ID=NEW.PARENT_ID;
 SET NEW.NAME = @parentname;
 END IF; 
END;
$$
DELIMITER ;

Test:

mysql> insert into PARENT values ( 1,'phil' );
Query OK, 1 row affected (0.01 sec)
mysql> insert into CHILD values(1,'',1);
Query OK, 1 row affected (0.02 sec)
mysql> select * from CHILD;
+----+------+-----------+
| ID | NAME | PARENT_ID |
+----+------+-----------+
| 1 | phil | 1 |
+----+------+-----------+
1 row in set (0.00 sec)
mysql>

There's no error checking etc, but I'll leave that as an exercise for you.

Answer 2

You can use IFNULL(see here) for NULL only or IF(see here) when you're going to include empty strings when setting the value for NEW.NAME.

BEFORE INSERT when you have to check the record first before inserting the record.

You can try the below query:

DELIMITER $$
DROP TRIGGER IF EXISTS `checkIfNull`$$
CREATE TRIGGER `checkIfNull` BEFORE INSERT ON `CHILD` FOR EACH ROW BEGIN
 SELECT `NAME` INTO @v_name FROM PARENT WHERE `ID` = new.PARENT_ID;
 SET new.NAME = IF(new.NAME IS NULL OR new.NAME = '',@v_name,new.NAME);
END;$$
DELIMITER ;

The IF part checks if the new NAME contains a value, if YES the value would be as it is, if NO it will use the name of the PARENT_ID from the PARENT table.