MySQL Forums
Forum List  »  Triggers

updating row on other table after trigger event
Posted by: gustavo morales
Date: April 12, 2012 08:50AM

Hi thanks for your time.

I have the following tables:
create table `user_keys`(
id_user int(5),
id_key int(5),
constraint pk_uk primary key(id_user, id_key),
constraint fk_uk_k foreign key(id_key) references `keys`(id),
constraint fk_uk_k foreign key(id_key) references `keys`(id));

create table `keys`(
id int(5),
`key` varchar(20),
`used`int(5),
constraint pk primary key(id));

I use these tables to keep track of which user has which key.
for an example's sake, imagine this tables have the following data:

USER_KEYS:
id_user______id_key
1____________ 1
2____________ 1
3____________ 2

`keys`:

id______key____________used
1_______CHVWX __________2
2_______ABCD2 __________1


Table user_keys stores a the user ID and key ID.
What I'd like to do is, when you delete a registry on user_keys for example, registry 1, have a trigger that updates table `keys` such as:
UPDATE `keys` SET used = used - 1 where $$here is the problem$$

I wonder if it's overly complicated trying to do this with a trigger, because it's rather easy with 2 queries such as:

DELETE from `user_keys` WHERE id_key = $variable and id_user = $variable2;
UPDATE `keys` SET used = used - 1 where id = $variable;

Options: ReplyQuote


Subject
Views
Written By
Posted
updating row on other table after trigger event
2664
April 12, 2012 08:50AM


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.