Hey, guys. We're using CB Queries and need to have two 'UPDATE' queries happen in the same trigger (after registration), with the second one specifically executing after the first. It doesn't seem that CB Queries supports the use of two queries in one trigger or sql transactions. Any solutions to this? Thanks again!

The idea is that we want to update a database field and then immediately after increment a counter stored in another field. We would just create another trigger that executed along with the first but it has to happen in order and could cause problems if multiple users were triggering simultaneously.

We found a potential solution, but I can see how it might not work in the system. Any experts please advise on whether you think this will work.

We have two CB queries that activate on the same trigger, OnAfterUserRegistration, the first is:


UPDATE jos_comprofiler
SET cb_memberid = (SELECT counter
FROM jos_member_id_counter
ORDER BY counter DESC LIMIT 1)
WHERE id = [cb:userdata field="id" /]

which updates the cb_memberid field for the user just created with the counter field found in jos_member_id_counter

The second query is to increment the counter:

UPDATE jos_member_id_counter SET counter = counter + 1;

Since the second was created after the first, they do execute in the correct order. However this seems like it will very likely break if multiple users are trying to register simultaneously.

Is there any other method you can think of to accomplish this same task? Ideally if we could execute both queries within the first trigger there's little chance a query could get executed in between the two of them, but doing it the way we are now it seems like there's a hole.

Any advice you could give would be most appreciated!