Why wouldn't my suggestion work, Tomasz? When Karol inserts his first row into his main table it will contain DATE_FROM 2018-09-10 and DATE_TO 2018-09-20 and the trigger would insert 11 rows (one for each date) into my suggested table. When the next row is inserted with DATE_FROM 2018-09-15 and DATE_TO 2018-09-22, the trigger would fail inserting 8 rows due to the unique constraint.
The one place where I know my suggestion was incorrect, is that the trigger needs to be AFTER UPDATE as well (I only said AFTER INSERT/DELETE), with a change of dates, both DELETE and INSERT must be done. Other than that I would expect it to work (although it does make things a bit slower than not having this additional table). Set Den ons. 12. sep. 2018 kl. 14:49 skrev Tomasz Tyrakowski [email protected] [firebird-support] < [email protected]>: > On 12.09.2018 at 13:29, Omacht András [email protected] [firebird-support] > wrote: > > Create a (reserved_dates) table with date field, and make that field > unique.. When a user inserts a date into the reservation table a trigger > immadiate inserts this date to the reserved_dates table too. Then the > unique key will stop secound insert instead of the first transaction is not > commited. > > This won't work. All dates may be different and the intervals may still > overlap. > >
