MySQL trigger puzzle

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

MySQL trigger puzzle

Post by rhecker »

SCENARIO: There is a client table and a service table and a table called clients_services that maintains which clients have selected which services. This table contains only the client_id and the service_id. Clients add and remove services from a dynamically generated list of checkboxes. After post submission, all the rows in clients_services that contain the client_id are deleted, then new rows are added for the new set of choices, based on the array created from the checkboxes.

All of the above has worked fine, but now I need to track changes (with a datetimestamp) to CLIENTS_SERVICES, and not just additions, deletions as well. It seems like a MySQL trigger would be the solution, populating a new table with the client_id, the service_id, the datetimestamp, and whether the change is an addition or deletion.

The fact that right now all the records containing the client_id are deleted before insertion of the new rows is not really the problem because I think I can let the trigger code hold off to perform the deletion after the comparison and before the insert.

The problem is that right now the query is an INSERT, not an UPDATE, so the trigger can't compare old values to new. A solution to this could be to change the clients_services table so that it contains the client_id and all the service names (0r ids) as boolean. That would allow the use of an UPDATE trigger. The problem with that solution is that the services are not static, they can be added and deleted, so the columns would be subject to change--not good.

So hopefully the above explains the puzzle I am trying to solve. The reason for this is so we can invoice based on partial months of service. The client might add a service one month and two more services ten days later, then delete a service sixty days later. When we do a monthly billing we need to charge accordingly. The number of services is 10 now but could be as many as 20 eventually.

If anyone can suggest a possible solution, I would appreciate it!
Post Reply