What about a separate table for dates with one row per date and a UNIQUE constraint that you populate from a (AFTER INSERT/DELETE) trigger on your real table?
HTH, Set Den ons. 12. sep. 2018 kl. 11:49 skrev liviuslivius liviusliv...@poczta.onet.pl [firebird-support] < firebird-support@yahoogroups.com>: > > > Hi, > > is there a good way to prevent overlaping dates to be inserted to the > table? > > You know DATE_FROM, DATE_TO and you can have > 2018-09-10 to 2018-09-20 > and > 2018-09-15 to 2018-09-22 > they ovelap on 15,16,17,18,19 and 20 > > > No my real sample but simple to understand is: > > customer and meeting planning. > Currently we lock customer record and only one user can do > update/insert/delete operation at a time. > But what if we need multiple users insert/update data in the same time? > How to prevent inserting overlaping dates then? > > I know that we can write trigger and check overlap there but what if two > users do insert and still not commited transaction? > User1 do insert, trigger is fired and check that no overlaping dates > exists - and this transaction is not commited yet > User2 do insert, trigger is fired and check that no overlaping dates > exists because user 1 still not commit transaction and user2 transaction do > not see recrods from user1. > And at this point, if user1 commit and user2 commit, then overlaping dates > can be inserted into database. > > regards, > Karol Bieniaszewski > > > >
