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;