I'm working on a CMS, one of the features of the CMS in question is
that only one user can edit an item at any given time. I've
implemented this by having one table that holds the items, and another
table that holds locks. A lock row consists of the ID of the item
locked, a timestamp indicating when the item was locked, an interval
indicating when the locks expires and a string that holds a reason for
the item currently being locked.
I want to be able to restrict any query that updates or deletes from
the articles table so that they can only occur if there isn't a
corresponding entry in the locks table. As far as I can tell,
however, you can't join tables when doing updates or deletes. I know
on the PHP side I can attempt to do a select on the locks table and
only perform the delete if the select returns 0 rows, but I'd rather
the update or delete query itself does the checking. Can anyone help
out?