lundi 20 juin 2016

Increment/decrement column when insert or update in another table

So consider this: I have two tables, first is "machines" and second "rentals". I have a boolean column in machines called "inRental" which is 0 when machine is not rented and 1 when it is. I also have boolean column in rentals called "finished" which is 0 when machine is still in rental and 1 when rental is finished (i don't delete rental entries because I want to track history of rentals). So what I want is, increment or set column 'machines.inRental' to 1 when a new rental entry for that machine is inserted in 'rentals'. For example when I create new rental I do:

INSERT INTO rentals VALUES (0,:machineID,0) 

When this happens I want to:

UPDATE machines SET inRental=1 WHERE MachineID=:machineID

Similar I want when rental is finished:

UPDATE rentals SET finished=1 WHERE MachineID=:machineID

and

UPDATE machines SET inRental=0 WHERE MachineID=:machineID

Tables are like this:

RentalID  MachineID    finished   ...
.....................................
1         5            1                 
2         6            1                   
3         7            0                      


MachineID  inRental   ...
.........................
5          0                             
6          0                               
7          1                                 

I guess this is achieved with trigger, but how? I also heard triggers are bad and it is better to avoid them. Is there a better way? I know that it maybe looks that column inRental is redundant and that I could just do JOINS, but I use a lot of times table 'machines' in subquery with other tables and I find it a little ineffective and messy to have to make JOINs in subqueries, or isn't it?

Aucun commentaire:

Enregistrer un commentaire