Re: updating row on other table after trigger event

[irek kordirko]

A trigger might look like this:

delimiter $$
drop trigger after_del_user_key $$
create trigger after_del_user_key
after delete on `user_keys`
for each row
begin
    update `keys` set used = used - 1
    where id = old.id_key;
end;
$$
delimiter ;

mysql> select * from user_keys;
+---------+--------+
| id_user | id_key |
+---------+--------+
|       1 |      1 |
|       2 |      1 |
|       1 |      2 |
|       2 |      2 |
+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from `keys`;
+----+------+------+
| id | key  | used |
+----+------+------+
|  1 | x    |   -1 |
|  2 | y    |    0 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> delete from `user_keys` where id_user = 1 and id_key = 2;
Query OK, 1 row affected (0.11 sec)

mysql> select * from `keys`;
+----+------+------+
| id | key  | used |
+----+------+------+
|  1 | x    |   -1 |
|  2 | y    |   -1 |
+----+------+------+
2 rows in set (0.00 sec)