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