MySQL Forums
Forum List  »  Triggers

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
Re: updating row on other table after trigger event
1275
April 12, 2012 06:05PM


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.