| Subject | Re: [firebird-support] Prevent overlaping dates in concurent environment |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2018-09-12T13:04:18Z |
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 t.tyrakowski@... [firebird-support] <firebird-support@yahoogroups.com>:
On 12.09.2018 at 13:29, Omacht András aomacht@... [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.