Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрет пересекающихся временных периодов  [new]
Verbovsky Innokenty
Member

Откуда: Moscow
Сообщений: 160
Помогите, плиз, в SQL SERVER 2000 запретить в таблице с полями
DateStart, DateEnd (DateStart<DateEnd)
пересечение периодов, то бишь ситуацию, когда
DateStart2 < DateStart1 < DateEnd2
например
DateStart, DateEnd
2005-01-01 2005-12-31
2005-01-03 2006-02-01
Записи могут вводиться скопом, не обязательно по одной
22 июл 05, 12:55    [1727605]     Ответить | Цитировать Сообщить модератору
 Re: Запрет пересекающихся временных периодов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37100
Писать триггер на вставку.
22 июл 05, 13:02    [1727636]     Ответить | Цитировать Сообщить модератору
 Re: Запрет пересекающихся временных периодов  [new]
vma_mnt
Member

Откуда: Новокузнецк
Сообщений: 602
Здесь уже разбирали пример.

Посмотри
if object_id('dbo.t') is not null drop table dbo.t
go
if object_id('dbo.f_DateIntervalIsOverlapped') is not null drop function dbo.f_DateIntervalIsOverlapped
go

CREATE TABLE T (F_Key int identity primary key, InsuredID int, DateB datetime, DateE datetime)
GO

CREATE FUNCTION dbo.f_DateIntervalIsOverlapped
(@ID int)
RETURNS bit
AS
BEGIN 
if exists(select * from dbo.t a inner join dbo.t b on  b.insuredid = a.insuredid
and (a.datee >= b.dateb and a.dateb <= b.datee) and b.f_key <> a.F_key WHERE a.InsuredID = @ID)
RETURN 1
RETURN 0
END
GO

ALTER TABLE T ADD CONSTRAINT [CK_T_DateIntervalIsOverlapped] CHECK ([dbo].[f_DateIntervalIsOverlapped](InsuredID)=0)
GO

/*valid records*/
INSERT INTO T (InsuredID, DateB, DateE) SELECT 1,' 20040601', '20040630'
INSERT INTO T (InsuredID, DateB, DateE) SELECT 1, '20040401', '20040430'
INSERT INTO T (InsuredID, DateB, DateE) SELECT 1, '20040201', '20040228'
go

SELECT * FROM T
go

/*not valid record*/
INSERT INTO T (InsuredID, DateB, DateE) SELECT 1, '20040510', '20040610'
go

SELECT * FROM T
go

if object_id('dbo.t') is not null drop table dbo.t
go
if object_id('dbo.f_DateIntervalIsOverlapped') is not null drop function dbo.f_DateIntervalIsOverlapped
go
22 июл 05, 16:36    [1728848]     Ответить | Цитировать Сообщить модератору
 Re: Запрет пересекающихся временных периодов  [new]
Verbovsky Innokenty
Member

Откуда: Moscow
Сообщений: 160
Спасибо!
То, что надо
23 июл 05, 00:24    [1729903]     Ответить | Цитировать Сообщить модератору
 На вставку работает, а на обновление - нет  [new]
Verbovsky Innokenty
Member

Откуда: Moscow
Сообщений: 160
Сделал функцию проверки f_DateIntervalIsOverlapped и сделал на ее основе Constraint
Не знаете в чем может быть дело: при вводе новый данных база ругается на пересекающиеся интервалы, а при изменении старых - нет
10 авг 05, 11:19    [1773829]     Ответить | Цитировать Сообщить модератору
 Re: На вставку работает, а на обновление - нет  [new]
Breakneck
Member

Откуда: Kiev
Сообщений: 2454
Verbovsky Innokenty
при вводе новый данных база ругается на пересекающиеся интервалы, а при изменении старых - нет

Скрипт по созданию Constraint и функцию в студию
10 авг 05, 13:17    [1773961]     Ответить | Цитировать Сообщить модератору
 Re: Запрет пересекающихся временных периодов  [new]
Verbovsky Innokenty
Member

Откуда: Moscow
Сообщений: 160
Сейчас попробую упростить, а пока оригинал


ALTER TABLE [dbo].[ContractPays] ADD
CONSTRAINT [Пересечение временных диапазонов в условиях оплаты] CHECK ([dbo].[fn_DateIntervalIsOverlapped_Pays]([ID]) = 0)

CREATE FUNCTION dbo.fn_DateIntervalIsOverlapped_Pays
(@ID int)
RETURNS bit
AS
BEGIN
DECLARE
@Publication smallint, @Developer int

SELECT @Developer = Developer, @Publication = Publication FROM dbo.ContractPays CR INNER JOIN Contracts ON CR.Contract = Contracts.ID WHERE CR.ID = @ID
if exists(select * from
(SELECT CR.ID, CR.DateStart,CR.DateEnd, CR.Publication, Contracts.Developer FROM dbo.ContractPays CR INNER JOIN Contracts ON CR.Contract = Contracts.ID) a inner join
(SELECT CR.ID, CR.DateStart,CR.DateEnd, CR.Publication, Contracts.Developer FROM dbo.ContractPays CR INNER JOIN Contracts ON CR.Contract = Contracts.ID) b
on b.Publication = a.Publication AND b.Developer=a.Developer
WHERE(a.DateEnd BETWEEN b.DateStart AND b.DateEnd OR a.DateStart BETWEEN b.DateStart AND b.DateEnd) and b.ID <> a.ID AND a.Publication = @Publication AND a.Developer = @Developer)
RETURN 1
RETURN 0
END
10 авг 05, 13:44    [1774108]     Ответить | Цитировать Сообщить модератору
 Re: Запрет пересекающихся временных периодов  [new]
Verbovsky Innokenty
Member

Откуда: Moscow
Сообщений: 160
в приведенном выше примере от vma_mnt таже самая проблема
10 авг 05, 13:56    [1774170]     Ответить | Цитировать Сообщить модератору
 Убедитесь сами:  [new]
Verbovsky Innokenty
Member

Откуда: Moscow
Сообщений: 160
if object_id('dbo.t') is not null drop table dbo.t
go
if object_id('dbo.f_DateIntervalIsOverlapped') is not null drop function dbo.f_DateIntervalIsOverlapped
go

CREATE TABLE T (F_Key int identity primary key, InsuredID int, DateB datetime, DateE datetime)
GO

CREATE FUNCTION dbo.f_DateIntervalIsOverlapped
(@ID int)
RETURNS bit
AS
BEGIN
if
exists(select * from dbo.t a inner join dbo.t b on b.insuredid = a.insuredid
and (a.datee >= b.dateb and a.dateb <= b.datee ) and b.f_key <> a.F_key WHERE a.InsuredID = @ID)
RETURN 1
RETURN 0
END
GO

ALTER TABLE T ADD CONSTRAINT [CK_T_DateIntervalIsOverlapped] CHECK ([dbo].[f_DateIntervalIsOverlapped](InsuredID)=0)
GO

/*valid records*/
INSERT INTO T (InsuredID, DateB, DateE) SELECT 1, '20040601', '20040630'
INSERT INTO T (InsuredID, DateB, DateE) SELECT 1, '20040401', '20040430'
INSERT INTO T (InsuredID, DateB, DateE) SELECT 1, '20040201', '20040228'
go
SELECT * FROM T
go

/*not valid record*/
INSERT INTO
T (InsuredID, DateB, DateE) SELECT 1, '20040510', '20040610'
go
/*valid record*/
INSERT INTO T (InsuredID, DateB, DateE) SELECT 1,'20000601', '20000630'
GO
/*Здесь должна была бы быть ошибка, но ее нет*/
UPDATE t SET DateB='20040510', DateE='20040610' WHERE F_Key=(SELECT MAX(F_Key) FROM T)
go
SELECT * FROM T
go
if object_id('dbo.t') is not null drop table dbo.t
go
if object_id('dbo.f_DateIntervalIsOverlapped') is not null drop function dbo.f_DateIntervalIsOverlapped
10 авг 05, 14:39    [1774459]     Ответить | Цитировать Сообщить модератору
 Функция не читает UNCOMMITTED записи  [new]
Verbovsky Innokenty
Member

Откуда: Moscow
Сообщений: 160
Функция не читает UNCOMMITTED записи, поэтому и не выдает ошибки (считывает старые правильные данные). Пришлось перенести в триггер, поскольку в функцию нельзя запихнуть
SET TRANSACTION ISOLATION LEVEL

Это ничего, что я тут сам с собой болтаю? :-)
10 авг 05, 17:08    [1775112]     Ответить | Цитировать Сообщить модератору
 Re: Функция не читает UNCOMMITTED записи  [new]
vma_mnt
Member

Откуда: Новокузнецк
Сообщений: 602
Verbovsky Innokenty
Функция не читает UNCOMMITTED записи, поэтому и не выдает ошибки (считывает старые правильные данные). Пришлось перенести в триггер, поскольку в функцию нельзя запихнуть
SET TRANSACTION ISOLATION LEVEL

Это ничего, что я тут сам с собой болтаю? :-)


Наоборот, хорошо.

Я до таких глубин не проверял, тоже буду править
10 авг 05, 17:29    [1775249]     Ответить | Цитировать Сообщить модератору
 Re: Функция не читает UNCOMMITTED записи  [new]
Glory
Member

Откуда:
Сообщений: 104760
Verbovsky Innokenty
Функция не читает UNCOMMITTED записи, поэтому и не выдает ошибки (считывает старые правильные данные). Пришлось перенести в триггер, поскольку в функцию нельзя запихнуть
SET TRANSACTION ISOLATION LEVEL

Вообще-то все констрейнты срабатывают ДО изменения данных в таблице. А триггера как раз после.
10 авг 05, 17:41    [1775326]     Ответить | Цитировать Сообщить модератору
 Re: Запрет пересекающихся временных периодов  [new]
aleks2
Guest
Констрейнтам нужно явно новые даные передавать

CREATE FUNCTION dbo.f_DateIntervalIsOverlapped
(@ID int, @Date1 datetime, @Date2 datetime)
RETURNS bit
AS
BEGIN 
if exists(select * from dbo.t a 
WHERE
a.datee >= @date1 and a.dateb <= @date2 AND  a.ID <> @ID)
RETURN 1
RETURN 0
END
10 авг 05, 18:59    [1775612]     Ответить | Цитировать Сообщить модератору
 Re: Запрет пересекающихся временных периодов  [new]
Verbovsky Innokenty
Member

Откуда: Moscow
Сообщений: 160
aleks2
Констрейнтам нужно явно новые даные передавать

CREATE FUNCTION dbo.f_DateIntervalIsOverlapped
(@ID int, @Date1 datetime, @Date2 datetime)
RETURNS bit
AS
BEGIN 
if exists(select * from dbo.t a 
WHERE
a.datee >= @date1 and a.dateb <= @date2 AND  a.ID <> @ID)
RETURN 1
RETURN 0
END

А если более одной строки изменено? Не получится
10 авг 05, 19:19    [1775645]     Ответить | Цитировать Сообщить модератору
 Re: Запрет пересекающихся временных периодов  [new]
aleks2
Guest
Verbovsky Innokenty
aleks2
Констрейнтам нужно явно новые даные передавать

CREATE FUNCTION dbo.f_DateIntervalIsOverlapped
(@ID int, @Date1 datetime, @Date2 datetime)
RETURNS bit
AS
BEGIN 
if exists(select * from dbo.t a 
WHERE
a.datee >= @date1 and a.dateb <= @date2 AND  a.ID <> @ID)
RETURN 1
RETURN 0
END

А если более одной строки изменено? Не получится


Констрейнт работает всегда на одну строку, перебирая каждую по-очереди, если количество одновременно обновленных >1.
----------
усе получится.
11 авг 05, 06:39    [1776296]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить