On 12.09.2018 at 14:40, Omacht András aoma...@mve.hu [firebird-support]
wrote:
> Tested on 2.5.8, dialect 1:
>
>
>
> CREATE TABLE RESERVED_DATE (
>
> RES_DATE DATE NOT NULL
>
> );
>
> ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY
> (RES_DATE);
>
>
>
> CREATE TABLE MEETINGS (
>
> DATE_FROM DATE NOT NULL,
>
> DATE_TO DATE NOT NULL
>
> );
>
>
>
> CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
>
> ACTIVE BEFORE INSERT POSITION 0
>
> as
>
> declare variable curr_date date;
>
> begin
>
> curr_date = new.date_from;
>
> while (curr_date <= new.date_to) do
>
> begin
>
> insert into reserved_date (res_date) values (:curr_date);
>
> curr_date = dateadd(1 day to curr_date);
>
> end
>
> end
>
>
>
> run on first transaction:
>
>
>
> insert into MEETINGS (DATE_FROM, DATE_TO)
>
> values ('2018.09.01', '2018.09.10')
>
>
>
> run parallel on secound transaction:
>
> insert into MEETINGS (DATE_FROM, DATE_TO)
>
> values ('2018.09.08', '2018.09.15')
>
>
>
> violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table
> "RESERVED_DATE".
>
> Problematic key value is ("RES_DATE" = '8-SEP-2018').
>
> At trigger 'MEETINGS_BI' line: 9, col: 7.
OK, what I didn't get was that you intended to put _all_ subsequent
dates from within an interval to the auxiliary table.
Well, that way it should work. And I wouldn't be too worried about the
size of the table, unless the intervals would be hundreds of years long.
Tomasz
--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__
