Mysql – trigger to delete records with same value from another table after inserting record in another table

MySQLtrigger

i have two tables normal and abnormal,i want to create a trigger that will be able to delete records from normal table once i insert the same details in the abnormal table for example if a person is sick and we insert his or her details in the abnormal table,the moment i submit the insert values in the abnormal table a trigger should delete the persons record in the normal table with a message deleted from normal table because this person is sick.

mysql> desc normal;
+----------+-------------+------+-----+---------+
| Field    | Type        | Null | Key | Default | 
+----------+-------------+------+-----+---------|
| Id       | int(11)     | NO   | PRI | 0       |       
| FirstName| varchar(20) | YES  |     | NULL    |       
| lastName | varchar(20) | YES  |     | NULL    |       
+----------+-------------+------+-----+---------+
3 rows in set (0.00 sec)

mysql> desc abnormal;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Id       | int(11)     | NO   | PRI | 0       |       |
| FirstName| varchar(20) | YES  |     | NULL    |       |
| lastName | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

lets say i do a select statement on normal table 

mysql> select * from normal;
+----+----------+----------+
| Id | firstName | lastName|
+----+----------+----------+
|  1 | Micheal  | jacobs   |
+----+----------+----------+
|  2 | benjamin | clayto   |
+----+----------+----------+
|  3 | jackson  | boxer    |
+----+----------+----------+

then i insert values that exist in normal i insert then into abnormal table

insert into abnormal values('1','Micheal','jacobs', 'under treatment');
mysql> select * from abnormal;
+----------+---------+---------------+
|firstName | lastName| extra         |
+----------+---------+---------------+
| micheal  | jacobs  |under treatment|
+----------+---------+---------------+

the end result on normal table should not have the record for micheal it should be deleted

mysql> select * from normal;
+----+----------+----------+
| Id | firstName | lastName|
+----+----------+----------+
|  2 | benjamin | clayto   |
+----+----------+----------+
|  3 | jackson  | boxer    |
+----+----------+----------+

Best Answer

Try this:

DELIMITER $$
CREATE TRIGGER tgr_1 AFTER INSERT ON abnormal
FOR EACH ROW
BEGIN
  DELETE FROM normal where id = NEW.id;
END $$
Related Question