How to change other entries values when an entry is saved/updated

Question

I have a channel with some fields. One of them is an on/off value (let's say toggle field type). I want to be sure that I have no more than one entry with on (or true) value for that specific field. In other words, when an entry is saved or updated, I should check value of that specific field and if it is turned on, I must turn off that field value of any previous entry which has true value for that field. What I have tried so far was:

1. Trying to create a custom fieldtype by extending toggle field, but in post_save method I do not have access to current entry DB id to update other entries except the current one. The only thing I have is the saved value of the field.
2. Trying database triggers, so that if the new value of the updated/inserted record is 1 (or true), update any other record which has value type of 1 (from 1/true to 0/false). It was not possible since mysql does not let you run queries on the same table as the one you are writing trigger for.

Thanks in advance

Answer 1

Can you explain further why you need to accomplish this? It seems like an odd functionality to implement. Could you just make one entry sticky at a time?

You could create an extension, and I can explain further in another answer if you really want to do this, but honestly I can't see why you wouldn't just use a single special entry status for the "active" or "tagged" entry, and just switch the old entry to just "open" or "closed" when you create a new entry you want tagged with this toggle idea. I also don't like the idea of avoiding a a proper audit trail by bypassing the Channel Entry API altogether and bulk updating DB fields in this manner.

Perhaps if you could explain why you need this functionality, we possibly can provide you with a simpler answer than what it seems you'd like to do.