Mysql – How to fetch parent table’s value within a trigger

MySQLtrigger

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?

Best Answer

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.

Related Question