Skip navigation links

MySQL Forums :: Triggers :: updating row on other table after trigger event


Advanced Search

Re: updating row on other table after trigger event
Posted by: irek kordirko ()
Date: April 12, 2012 06:05PM

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)

Options: ReplyQuote


Subject Views Written By Posted
updating row on other table after trigger event 1740 gustavo morales 04/12/2012 08:50AM
Re: updating row on other table after trigger event 878 irek kordirko 04/12/2012 06:05PM
Re: updating row on other table after trigger event 612 gustavo morales 04/13/2012 07:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.