CODEWITHSUNDEEP
mysqlevents
Hexman
Hexman

Reputation: 327

create event delete row in mysql

How can I create an event by which I automatically delete a row when a value is changed in another table? My tables will look like this:

tbl_people
+----+-------+----------+
| ID |  NAME |  STATUS  |
+----+-------+----------+
|  1 | name1 |  active  |
|  3 | name3 |  active  |
+----+-------+----------+

tbl_activities
+----+---------+-------+
| ID | ID_NAME |  NAME |
+----+---------+-------+
|  1 |    1    | name1 |
|  2 |    3    | name3 |
+----+---------+-------+

When in tbl_people at id 3 it changes from active to inactive in tbl_activities to automatically delete the row with id 2

Thank you!

Upvotes: 0

Views: 342

Answers (1)

ololoken
ololoken

Reputation: 58

Try to use trigger

   CREATE TRIGGER bfr_upd_activity BEFORE UPDATE ON tbl_people
   FOR EACH ROW
   BEGIN
      if new.status <> 'active' then 
        delete from tbl_activities where id_name = new.id;
      end if;
   END;//

Upvotes: 2

Related Questions