Let's look at this example database:
As we can see, person depends on the city (person.city_id is a foreign key). I don't delete rows, I just set them inactive (active=0). After setting city inactive, how can I automatically set all persons who are dependent on this city inactive? Is there a better way than writing triggers?
EDIT: I am interested only in setting person's rows inactive, not setting them active.
解决方案
Here's a solution that uses cascading foreign keys to do what you describe:
mysql> create table city (
id int not null auto_increment,
name varchar(45),
active tinyint,
primary key (id),
unique key (id, active));
mysql> create table person (
id int not null auto_increment,
city_id int,
active tinyint,
primary key (id),
foreign key (city_id, active) references city (id, active) on update cascade);
mysql> insert into city (name, active) values ('New York', 1);
mysql> insert into person (city_id, active) values (1, 1);
mysql> select * from person;
+----+---------+--------+
| id | city_id | active |
+----+---------+--------+
| 1 | 1 | 1 |
+----+---------+--------+
mysql> update city set active = 0 where id = 1;
mysql> select * from person;
+----+---------+--------+
| id | city_id | active |
+----+---------+--------+
| 1 | 1 | 0 |
+----+---------+--------+
Tested on MySQL 5.5.31.