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

Откуда: Воронеж
Сообщений: 275
Хотелось бы сделать триггер на таблицу вида


Столбцы
ID_Line DateTime Time
1 01.01.05 10:00
1 01.01.05 12:00 120
2 01.01.05 10:00
2 01.01.05 11:00 60
2 01.01.05 13:00 120
1 01.01.05 14:00 120

Time на данный момент считается процедурой вида
UPDATE    dbo.tbl_ShiftProtocol
SET              Time =
(SELECT     Time AS od
 FROM          (SELECT     a.id, DATEDIFF(mi, a.dtFirst, a.DtEnd) AS time
FROM          (SELECT     h.id, h.ID_Line, dtFirst = ISNULL
 ((SELECT     DATEADD(millisecond, 1, MAX(h1.DateTime))
FROM         tbl_ShiftProtocol h1
WHERE     h.ID_Line = h1.ID_Line AND h1.DateTime < h.DateTime), h.DateTime), DtEnd = h.DateTime
FROM          tbl_ShiftProtocol h) a
 WHERE      a.dtFirst < a.DtEnd) AS od
                            WHERE      tbl_ShiftProtocol.id = od.id) 

Как перегнать такую процедуру в триггер?
1 фев 05, 15:45    [1291067]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
Smirnov Anton
Member

Откуда: Н.Новгород
Сообщений: 3220
а может на словах объяснить, что надо сделать
1 фев 05, 15:51    [1291099]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
_bob
Member

Откуда: Москва
Сообщений: 1654
то же самое, только добавить джоин с таблицей inserted по ключевым полям и исходные данные тоже брать из inserted (структура у неё та же, что и у таблицы, на которой триггер)

НО!!!! если сейчас дата сделана с помощью ХП, оставь как есть, триггер замедлит Insert/update
------------------
Best regards, _bob
1 фев 05, 15:52    [1291104]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
GliderAlex
Member

Откуда: Воронеж
Сообщений: 275
Но процедура делает каждый раз при запуске пересчёт всей таблицы... а это 70 000 записей.
1 фев 05, 16:53    [1291468]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
Smirnov Anton
Member

Откуда: Н.Новгород
Сообщений: 3220
а как у вас происходят вставки в таблицу, какие особенности?порциями - поодиночке?только с одним ID_Line?только с одинаковым временем?
конкретнее: может быть типа такого
insert into ...
select 1 as ID_Line, '20050201 15:40'... union
select 2 as ID_Line, '20050201 14:40'... union
для некоторых типов вставки можно придумать достаточно быстрый триггер
1 фев 05, 17:11    [1291554]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
GliderAlex
Member

Откуда: Воронеж
Сообщений: 275
Вставляются строки по одной, изменяются тоже(если изменяются). Вообще то это отчётность работы производственных линий. Те. Линия с ID_Line = 1 работала с 8 до 9, стояла до 11 и т.д. Data-Time это время окончания операции и начало следующей. Т.е. время затраченное на операцию = DataTime(предыдущей операции) - DataTime

Возможно изменение DataTime строки, тогда пересчитывается Time строки и Time следующей строки...
Удаление, тоже пересчитывается Time строки следующей за удалённой с таким ID_Line
Вставка строки, тогда считается Time строки.
1 фев 05, 18:47    [1292035]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
GliderAlex
Member

Откуда: Воронеж
Сообщений: 275
Извините ошибка
Т.е. время затраченное на операцию = DataTime - DataTime(предыдущей операции)
1 фев 05, 18:49    [1292041]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
aleks2
Guest
GliderAlex
Но процедура делает каждый раз при запуске пересчёт всей таблицы... а это 70 000 записей.


Во, герой... 70 000 записей... народ похваляется миллионами.

Хто-ж тебя заставляет пересчитывать ВСЕ? Только лень... хотя лень сделала из обезьяны человека.

сделай
create function dbo.PrevID(@ID_Line int, @DateTime DateTime)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
RETURN (SELECT top 1 ID 
	FROM FROM dbo.tbl_ShiftProtocol 
	WHERE ID_Line = @ID_Line AND [DateTime] < @DateTime ORDER BY [DateTime] DESC)

END

добавь в таблицу dbo.tbl_ShiftProtocol
два поля
pid int DEFAULT null,
_pid AS dbo.PrevID(ID_Line, DateTime)
создай индексы
CREATE UNIQUE INDEX IX_tbl_ShiftProtocol_ID_Line_DateTime
ON dbo.tbl_ShiftProtocol (ID_Line, DateTime DESC) 

CREATE UNIQUE INDEX IX_tbl_ShiftProtocol_PID_PID
ON dbo.tbl_ShiftProtocol (PID, _PID)
 
CREATE UNIQUE INDEX IX_tbl_ShiftProtocol_PID
ON dbo.tbl_ShiftProtocol (PID)

и твоя процедура будет

UPDATE Е SET pid=T._pid, Time=DATEDIFF(mi, X.DateTime, T.DateTime)
from dbo.tbl_ShiftProtocol T INNER JOIN dbo.tbl_ShiftProtocol X 
ON T._pid=X.ID
WHERE T.pid<>T._pid

UPDATE Е SET pid=T._pid, Time=0
from dbo.tbl_ShiftProtocol T 
WHERE T._pid is null and T.pid is not null


пересчитывать только нужное.
2 фев 05, 07:12    [1292572]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
GliderAlex
Member

Откуда: Воронеж
Сообщений: 275
To alex2 А ты не мог бы пояснить мне все эти манипуляции? Потому как пока что я в них не вьезжаю. Т.е. как это всё сделать понятно, а вот что они делают не очень.

Я сделал процедуру для просчёта строк с Time ='' , но мне же ещё нужно учесть изменение строк(в моей процедуре не учитывается), и если удаление или изменение строки в середине где то, то тоже не работает
UPDATE    dbo.tbl_ShiftProtocol
SET              Time =
(SELECT     Time AS od
FROM          (SELECT     a.id, DATEDIFF(mi, a.dtFirst, a.DtEnd) AS time
FROM          (SELECT     h.id, h.ID_Line, dtFirst = ISNULL
((SELECT     DATEADD(millisecond, 1, MAX(h1.DateTime))
FROM         tbl_ShiftProtocol h1
WHERE     h.ID_Line = h1.ID_Line AND h1.DateTime < h.DateTime), h.DateTime), DtEnd = h.DateTime
FROM          tbl_ShiftProtocol h) a
WHERE      a.dtFirst < a.DtEnd) AS od
WHERE      tbl_ShiftProtocol.id = od.id)
WHERE     (Time IS NULL)
2 фев 05, 08:17    [1292640]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
aleks2
Guest
Поясняю

сделай
create function dbo.PrevID(@ID_Line int, @DateTime DateTime)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
RETURN (SELECT top 1 ID 
	FROM FROM dbo.tbl_ShiftProtocol 
	WHERE ID_Line = @ID_Line AND [DateTime] < @DateTime ORDER BY [DateTime] DESC)
END

Эта функция возвращает ID для предыдущей строки из dbo.tbl_ShiftProtocol или NULL, если такой строки нема. От этой строки ты считаешь DATEDIFF(mi, dtFirst, dtEnd).

Возможно, лучше возвращать сразу [DateTime], но это требует раздумий и экспериментов.


добавь в таблицу dbo.tbl_ShiftProtocol
два поля
pid int DEFAULT -1, -- Здесь я лопухнулся с NULL, предположим что отрицательных ID у тебя нема.
_pid AS dbo.PrevID(ID_Line, DateTime)

Эти поля позволят отслеживать удаление/добавление строк. Но не позволят отслеживать изменение [DateTime].

создай индексы
CREATE UNIQUE INDEX IX_tbl_ShiftProtocol_ID_Line_DateTime
ON dbo.tbl_ShiftProtocol (ID_Line, DateTime DESC) 

CREATE UNIQUE INDEX IX_tbl_ShiftProtocol_PID_PID
ON dbo.tbl_ShiftProtocol (PID, _PID)
 
CREATE UNIQUE INDEX IX_tbl_ShiftProtocol_PID
ON dbo.tbl_ShiftProtocol (PID)

Эти позволит функции и UPDATE работать быстрее.


и твоя процедура будет

-- Обновляем только строки для которых изменилась предшествующая строка
UPDATE Е SET pid=T._pid, Time=DATEDIFF(mi, X.DateTime, T.DateTime)
from dbo.tbl_ShiftProtocol T INNER JOIN dbo.tbl_ShiftProtocol X 
ON T._pid=X.ID
WHERE T.pid<>T._pid

-- Обновляем только строки для которых НЕМА предшествующей строки
UPDATE Е SET pid=-1, Time=0
from dbo.tbl_ShiftProtocol T 
WHERE T._pid is null and T.pid>-1


пересчитывать только нужное.
2 фев 05, 10:20    [1293046]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
GliderAlex
Member

Откуда: Воронеж
Сообщений: 275
А если изменение DateTime или удаление строки в середине?
Тогда зачем все эти сложности(
2 фев 05, 14:24    [1294275]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
GliderAlex
Member

Откуда: Воронеж
Сообщений: 275
Тогда можно использовать мою проц которая пересчитывает только строки с DateTime=Null
2 фев 05, 14:26    [1294284]     Ответить | Цитировать Сообщить модератору
 Re: Процедуру перегнать в триггер.  [new]
aleks2
Guest
GliderAlex
А если изменение DateTime или удаление строки в середине?
Тогда зачем все эти сложности(


Именно это и обеспечивается.

Если изменение DateTime могет иметь место - надо несколько изменить.
2 фев 05, 15:52    [1294694]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить