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

Question

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 |
+----+----------+----------+

Answer 1

Try this:

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