How can I fetch parent table's value within a trigger? 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?
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&gt; insert into PARENT values ( 1,'phil' );
Query OK, 1 row affected (0.01 sec)

mysql&gt; insert into CHILD values(1,'',1);
Query OK, 1 row affected (0.02 sec)

mysql&gt; select * from CHILD;
+----+------+-----------+
| ID | NAME | PARENT_ID |
+----+------+-----------+
|  1 | phil |         1 |
+----+------+-----------+
1 row in set (0.00 sec)

mysql&gt;


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