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