1

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

Try this:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.