Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Проблема с пересечением временных интервалов  [new]
Aazzz
Member

Откуда:
Сообщений: 4
Здравствуйте.
Возникла проблема в, казалось бы, простой задаче. Итак, есть таблица, содержащая 3 столбца: неуникальный идентификатор (BandName), дата начала некоторого события (StartDate), дата окончания этого события (EndDate). Стоит задача, чтобы временные интервалы от начала до окончания события не пересекались у записей с одинаковыми идентификаторами.
Для решения этой задачи попробовал создать триггер, в котором при попытке добавления записи, пересекающейся с существующими записями, сервер генерирует ошибку. Привожу листинг триггера:
CREATE TRIGGER TR_Tour
ON Tour
FOR INSERT
AS
	DECLARE @Band nvarchar(50);
	DECLARE @LastStartDate datetime;
	DECLARE @LastEndDate datetime;
	DECLARE @NewStartDate datetime;

	SELECT @Band = BandName FROM inserted;

	SELECT @LastStartDate = MAX( StartDate ) FROM Tour WHERE BandName = @Band;
	SELECT @LastEndDate = MAX( EndDate ) FROM Tour WHERE BandName = @Band;
	SELECT @NewStartDate = StartDate FROM inserted;
	
	IF ( ( @LastStartDate IS NOT NULL ) AND ( @LastEndDate IS NOT NULL ) AND ( @NewStartDate >= @LastStartDate ) AND ( @NewStartDate <= @LastEndDate ) )
	BEGIN
		RAISERROR( 'Гастроли не должны пересекаться во времени между собой', 16, 1 );
		ROLLBACK;
	END
GO
Помогите, пожалуйста, справиться с данной проблемой.
19 ноя 09, 23:22    [7953681]     Ответить | Цитировать Сообщить модератору
 Проблема с пересечением временных интервалов  [new]
Aazzz
Member

Откуда:
Сообщений: 4
Прошу прощения, забыл указать основные моменты.
Версия СУБД: Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Запрос на создание триггера выполняется успешно, но при попытке внести записи в таблицу, триггер срабатывает абсолютно всегда, даже в том случае, когда таблица пуста и вносится первая запись. Даже в этом случае триггер считает, что событие, содержащееся в записи, пересекается с существующими записями в таблице (хотя таблица совершенно пуста).
19 ноя 09, 23:31    [7953705]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
LLL1
Guest
Aazzz,

Насколько я понимаю, Вы не учитываете тот факт, что при выполнении триггера таблица (Tour) уже содержит добавляемую строку. Ее то и находит триггер при добавлении первой строки.
Советую добавить в таблицу уникальный первичный ключ.
Кроме того, само условие не выглядит правильным - фактически Вы смотрите только на запись с максимальными StartDate, EndDate
20 ноя 09, 01:13    [7953953]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
Во первых - в таблицах inserted и deleted может быть много записей.
Во вторых - в таблице должен быть первичный ключ - комбинация полей, однозначно идентифицирующих запись.
Допустим, такой ключ у вас - это все три поля, хотя это далеко не лучший вариант.
Тогда примерно так:

if exists(select *
	from inserted i join Tour t on i.BandName=t.BandName
	where (i.StartDate<>t.StartDate or i.EndDate<>t.EndDate) -- исключаем записи, соединенные "сами с собой" - по хорошему здесь нужно использовать первичный ключ
		and ((i.StartDate<=t.EndDate and i.EndDate>=t.StartDate)
			or (i.StartDate>i.EndDate) -- это чтоб не ввели неправильно даты
			)
	)
begin
	raiserror( 'айайай!', 16, 1 );
	rollback
end
20 ноя 09, 01:20    [7953961]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
aleks2
Guest
Зачем триггер, дарагой? Констрейнт вполне справится и MS рекомендует...
20 ноя 09, 08:25    [7954248]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
Nick_nsk
Member

Откуда:
Сообщений: 97
А вариант, когда оператор разносит гастроли в таком порядке вы не учитываете?

1. 22.11.2009 - 25.11.2009
1. 03.01.2010 - 13.01.2010
2. 12.12.2009 - 15.12.2009

Констрейнт поможет блокировать ошибочный ввод:

24.11.2009 - 15.11.2009
20 ноя 09, 08:44    [7954284]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
muk07
Member

Откуда: Челябинск
Сообщений: 1842
Добавьте ф-ю и думать будет легко
CREATE FUNCTION [dbo].[CrossInterval] (@x1 datetime, @x2 datetime, @y1 datetime, @y2 datetime)  
RETURNS bit  AS  
-- временные отрезки @x1 - @x2 и @y1 - @y2 пересекаются
BEGIN 
if (@x1 is null and @y1<=@x2) return 1
if (@y1 is null and @x1<=@y2) return 1
if (@x2 is null and @y2>=@x1) return 1
if (@y2 is null and @x2>=@y1) return 1
if (@x1>=@y1 and @x1<=@y2) return 1
if (@x2>=@y1 and @x2<=@y2) return 1
if (@y1>=@x1 and @y1<=@x2) return 1
if (@y2>=@x1 and @y2<=@x2) return 1
return 0
END
20 ноя 09, 09:13    [7954351]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
muk07
Member

Откуда: Челябинск
Сообщений: 1842
у меня тут, в отличие от вашей задачи, могут быть полуоткрытые интервалы
x2==null означает бесконечно удалённое будущее
20 ноя 09, 09:16    [7954363]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Вот честно, не понимаю я, о каком констрейнте тут говорят
Видимо опять я бол не дочитал
Если можно, дайте, пожалуйста, ссылку на те самые "рекомендации MS" для этого случая...
20 ноя 09, 09:29    [7954415]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Паганель
Вот честно, не понимаю я, о каком констрейнте тут говорят
Видимо опять я бол не дочитал
Если можно, дайте, пожалуйста, ссылку на те самые "рекомендации MS" для этого случая...
Видимо, CHECK CONSTRAINT с вызовом скалярной UDF, которая возвращает некий признак на основе SELECTа из этой таблицы
20 ноя 09, 09:32    [7954428]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
mike909
Member

Откуда:
Сообщений: 662
muk07,
Просто условие для вычисления
DECLARE @x1 datetime, @x2 datetime, @y1 datetime, @y2 datetime

set @x1 = '20090101'
set @x2 = '20090201'
set @y1 = '20090202'
set @y2 = '20090205'

select 'Не пересекаются' as [Result]
where (ISNULL(@x2, cast('29990101' as datetime)) < ISNULL(@y1, cast(0 as datetime))) OR 
      (ISNULL(@x1, cast(0 as datetime)) > ISNULL(@y2,cast('29990101' as datetime)))
union all
select 'Пересекаются' as [Result]
where not ( 
      (ISNULL(@x2, cast('29990101' as datetime)) < ISNULL(@y1, cast(0 as datetime))) OR 
      (ISNULL(@x1, cast(0 as datetime)) > ISNULL(@y2,cast('29990101' as datetime))) )

P.S. А с констрейнтами лучше не играться - они не для этого случая
20 ноя 09, 09:42    [7954472]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
aleks2
Guest
Всех то делов...
CREATE FUNCTION dbo.Intervals_Check
(@id int)
RETURNS bit
AS
BEGIN
if exists(select * FROM dbo.Intervals I INNER JOIN (select * FROM dbo.Intervals WHERE id=@id) X ON I.id<>X.id AND X.BandName=I.BandName AND X.StartDate<I.EndDate AND I.StartDate<X.EndDate) return 1	
RETURN 0
END
GO

CREATE TABLE [Intervals] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[BandName] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[StartDate] [datetime] NULL ,
	[EndDate] [datetime] NULL ,
	CONSTRAINT [CK_Intervals] CHECK ([dbo].[Intervals_Check]([id]) = convert(bit,0))
) ON [PRIMARY]
GO
CREATE  UNIQUE  INDEX [IX_Intervals] ON [dbo].[Intervals]([BandName], [StartDate]) ON [PRIMARY]
GO

Только надо честно сказать - пользователям такие шняги не ндравятся...
20 ноя 09, 09:53    [7954530]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
aleks2
Guest
mike909
P.S. А с констрейнтами лучше не играться - они не для этого случая

Святее папы желаете быть?
20 ноя 09, 09:55    [7954544]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
aleks2
Guest
Ну и забыл заколбасить primary key в таблице...
CREATE TABLE [Intervals] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[BandName] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[StartDate] [datetime] NULL ,
	[EndDate] [datetime] NULL ,
	CONSTRAINT [PK_Intervals] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] ,
	CONSTRAINT [CK_Intervals] CHECK ([dbo].[Intervals_Check]([id]) = convert(bit,0))
) ON [PRIMARY]
GO
20 ноя 09, 09:58    [7954575]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
aleks2
Всех то делов...
insert into Intervals(BandName, StartDate, EndDate)
select 'name1', '20091117', '20091119' union all
select 'name1', '20091120', '20091120'
update Intervals set StartDate = '20091118' where StartDate = '20091120'

select * from Intervals

id          BandName                                           StartDate               EndDate
----------- -------------------------------------------------- ----------------------- -----------------------
1           name1                                              2009-11-17 00:00:00.000 2009-11-19 00:00:00.000
2           name1                                              2009-11-18 00:00:00.000 2009-11-20 00:00:00.000

(2 row(s) affected)
20 ноя 09, 10:08    [7954654]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
aleks2
Guest
Паганель
aleks2
Всех то делов...
insert into Intervals(BandName, StartDate, EndDate)
select 'name1', '20091117', '20091119' union all
select 'name1', '20091120', '20091120'
update Intervals set StartDate = '20091118' where StartDate = '20091120'

select * from Intervals

id          BandName                                           StartDate               EndDate
----------- -------------------------------------------------- ----------------------- -----------------------
1           name1                                              2009-11-17 00:00:00.000 2009-11-19 00:00:00.000
2           name1                                              2009-11-18 00:00:00.000 2009-11-20 00:00:00.000

(2 row(s) affected)


Фигня. Просто я уже давненько не практиковался - подзабыл некоторые тонкости.

CREATE FUNCTION dbo.Intervals_CheckEx
(@id int, @BandName nvarchar(50), @StartDate datetime, @EndDate datetime)
RETURNS bit
AS
BEGIN
if exists(select * FROM dbo.Intervals I WHERE I.id<>@id AND I.BandName=@BandName AND @StartDate<I.EndDate AND I.StartDate<@EndDate) return 1	
RETURN 0
END
GO
CREATE TABLE [Intervals] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[BandName] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[StartDate] [datetime] NULL ,
	[EndDate] [datetime] NULL ,
	CONSTRAINT [PK_Intervals] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] ,
	CONSTRAINT [CK_Intervals] CHECK ([dbo].[Intervals_CheckEx]([id], [BandName], [StartDate], [EndDate]) = convert(bit,0))
) ON [PRIMARY]
GO
20 ноя 09, 10:25    [7954804]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
2 aleks2
А не объясните, чем в данном случае констрейнт (с UDF) лучше триггера ?

На мой взгляд, у триггера в данном случае преимущества:
1. будет быстрее при массовой вставке
2. Можно вывести человеческий текст ошибки
3. Ну и вообще, не касаясь данной задачи, триггеры гораздо более гибкий инструмент, и в случае наличия более-менее сложной логики без них никак.
20 ноя 09, 11:30    [7955280]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
aleks2
Guest
ura,

1. Я ж сказал: MS рекомендует использовать констрейнты для реализации бизнес-логики, где это возможно.

2. Просто вы не умеете это делать.

3. Я ж не призываю: "Долой триггера!"
20 ноя 09, 11:34    [7955310]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
aleks2

1. Я ж сказал: MS рекомендует использовать констрейнты для реализации бизнес-логики, где это возможно.

Это не аргумент, в данном случае использование UDF сводит на нет преимущества констрейнта (оно собственно, только одно - чуть большее быстродействие, опять же, при условии отсутствия UDF)

aleks2
2. Просто вы не умеете это делать.

Любопытно... научите. Естественно сообщение должно быть на уровне сервера, а не клиента...
20 ноя 09, 12:53    [7956034]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
ura
Естественно сообщение должно быть на уровне сервера, а не клиента...
Это еще почему?
Разве сервер показывает сообщение пользователю?
Это работа клиента
20 ноя 09, 12:56    [7956048]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
я б писал триггер в даном случае мимниму по 2м причинам
1. При изменении логики - легче и безболезненей менять
2. При новом констрене с новыми чеками,могут быть проблеммы при перестроении со старыми даными
-------------------------------------
Jedem Das Seine
20 ноя 09, 13:10    [7956151]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
Паганель
ura
Естественно сообщение должно быть на уровне сервера, а не клиента...
Это еще почему?
Разве сервер показывает сообщение пользователю?
Это работа клиента

Это зависит от того, на каком уровне реализована бизнес-логика приложения. Если она реализована на уровне ХП - то рациональнее формировать текст ошибки на сервере.
20 ноя 09, 13:15    [7956174]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
ura
Паганель
ura
Естественно сообщение должно быть на уровне сервера, а не клиента...
Это еще почему?
Разве сервер показывает сообщение пользователю?
Это работа клиента

Это зависит от того, на каком уровне реализована бизнес-логика приложения. Если она реализована на уровне ХП - то рациональнее формировать текст ошибки на сервере.
А что мешает сформировать сообщение в ХП в данном случае? При срабатывании CHECK CONSTRAINTа в стиле aleks2?
20 ноя 09, 13:18    [7956200]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
aleks2
Guest
ura
aleks2

1. Я ж сказал: MS рекомендует использовать констрейнты для реализации бизнес-логики, где это возможно.

Это не аргумент, в данном случае использование UDF сводит на нет преимущества констрейнта (оно собственно, только одно - чуть большее быстродействие, опять же, при условии отсутствия UDF)

aleks2
2. Просто вы не умеете это делать.

Любопытно... научите. Естественно сообщение должно быть на уровне сервера, а не клиента...


1. Голословное утверждение. Это очень сильно зависит от конкретного написания триггера и конкретной UDF.

2. Лень. Ваши взгляды на способы формирования сообщений об ошибках слишком неправильны. Но, впрочем, где-то тута уже раздували по этому поводу - я тогда участвовал...
20 ноя 09, 13:21    [7956223]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с пересечением временных интервалов  [new]
aleks2
Guest
2. Впрочем... могете ознакомиться с MS Access ADP. Там в конструкторе таблицы для ограничения можно задать любое сообщение. ADO получает его при срабатывании ограничения.
Профайлером посмотрите, как оно это делает.

Только это, все одно, неправильный подход...
20 ноя 09, 13:27    [7956277]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить