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