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

Откуда: Гималай
Сообщений: 2101
Приветствую всех.
Данные крутятся на SQL Server 2008 R2,
есть таблица ежедневных остатков:
TblUserDayInc
User_ID DT Inc Sales Pays Out
1 2012-02-25 1 500 200 -299
1 2012-02-26 -299 200 150 -349
1 2012-02-29 -349 5 500 146


Где
User_ID идентификатор пользователя
DT день
Inc входящий остаток на начало дня
Sales продажи
Pays оплаты
Out исходящий остаток, calculated column (Out = Inc - Sales + Pays)

Primary index на User_ID, DT

если на какой-либо день продажи или оплаты нету, то на тот день не создается запись остатков, записи остатков создаются только на следующий день.
Теперь, если делается оплата задним числом, или корректируются данные продажи, нужно пересчитать остатки.
Т.е. как только изменяются остатки на какую-либо прошлую дату, автоматом должны пересчитаться записи на следующие дни.
Реализовал это через триггер update на таблице TblUserDayInc:
CREATE TRIGGER [dbo].[TR_TblUserDayInc_UPDATE]
   ON  [dbo].[TblUserDayInc]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	
	IF EXISTS (SELECT udi.* FROM TblUserDayInc udi WITH (NOLOCK) INNER JOIN inserted ins ON udi.User_ID=ins.User_ID WHERE udi.DT>ins.DT)
		UPDATE
			TblUserDayInc
		SET
			Inc=ins.Out
		FROM
			TblUserDayInc udi INNER JOIN
			inserted ins ON udi.User_ID=ins.User_ID
		WHERE
			udi.DT=(SELECT MIN(DT) FROM TblUserdayInc WHERE User_ID=ins.User_ID AND DT>ins.DT)
END


т.е. при обновлении проверяется, есть ли записи новее чем обновляемая запись, если да, то он обновляет запись следующую за обновляемым
но оно правильно работает, если включены рекурсивные триггеры. Но я не хотел включать рекурсивные триггеры на всю базу.

Так вот, вопрос в следующем, какой вариант лучше?
Рекурсивные триггеры на всю базу, и вышеуказанным способом или же использовать курсор и отключить рекурсивное обновление?

Спасибо за внимание.
27 фев 12, 07:08    [12154648]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Забыл написать еще, конечно может быть есть и другие более оптимальные и элегантные решения.
Но, пока нашел только эти 2 варианта: рекурсия триггеров или курсор
27 фев 12, 07:11    [12154649]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
orunbek,
SELECT  udi.Inc AS 'before update'
      , udi.Inc - ins.Sales + ins.Pays AS 'after update'
    FROM TblUserDayInc udi
         INNER JOIN inserted ins ON udi.User_ID = ins.User_ID
                                AND udi.DT > ins.DT

или я что-то не так понял....
27 фев 12, 07:44    [12154668]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Guf,

я ваш ответ тоже не понял?
before и after update зачем нужен?
27 фев 12, 07:47    [12154676]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
orunbek,

Для демострации. Я не обычно не пишу сразу инсерт/апдейт/делет, а то потом начнется "АААА! у меня все данные удалились!!!" Этот селект легко превращается в апдейт.
Просто, как я понял, если у тебя меняется одна строка, то нужно изменить все последующие строки, но на одну и туже величину. И рекурсия не нужна, да?
Допустим меняем значение Sales в твоем примере на 10, тогда
В () - старое/deleted значение, в [] - новое/inserted значение, в {} - одна и таже величина
User_IDDTInc (before update)SalesPaysInc [after update]
12012-02-251(500)[510](200)[200] 1 - т.к. вход. остаток на эту дату не меняется
12012-02-26-299200150(-299)+{(500)-[510]+(200)-[200]} = [-309]
12012-02-29-3495500(-349)+{(500)-[510]+(200)-[200]} = [-359]

Вот написал пример и понял, что накосячил. :-((( должно быть как-то так:
SELECT  udi.Inc AS 'before update'
      , udi.Inc + del.Sales - ins.Sales + del.Pays - ins.Pays AS 'after update'
    FROM TblUserDayInc udi
         INNER JOIN (inserted ins
                     INNER JOIN deleted del ON ins.User_ID = del.User_ID
                                           AND ins.DT      = del.DT
                    ) ON udi.User_ID = ins.User_ID
                     AND udi.DT      > ins.DT

Опять же не проверял...
P.S. Еще нужно учесть, что если мы говорим о внесении измений задним числом и принимаем во внимание "если на какой-либо день продажи или оплаты нету, то на тот день не создается запись остатков", то нужно писать аналогичный тригер на инсерт.
27 фев 12, 08:31    [12154724]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
Вот косячник, конечно же
SELECT  udi.Inc AS 'before update'
      , udi.Inc + del.Sales - ins.Sales + ins.Pays - del.Pays AS 'after update'
27 фев 12, 08:33    [12154727]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Не нужно никаких рекурсивных триггеров:
create trigger dbo.TR_TblUserDayInc_UPDATE
on dbo.TblUserDayInc
after update
as 
begin
 set nocount on;
	
 update udi
  set
   Inc = udi.Inc - d.Sales + i.Sales - d.Pays + i.Pays
 from
  inserted i join
  deleted d on d.User_ID = i.User_ID and d.DT = i.DT join
  TblUserDayInc udi on udi.User_ID = i.User_ID and udi.DT > i.DT

end;
27 фев 12, 09:46    [12154971]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Guf,

спасибо за идею, передалал под вариант изменение значения Inc следующих записей на сумму изменения
CREATE TRIGGER [dbo].[TR_TblUserDayInc_UPDATE]
   ON  [dbo].[TblUserDayInc]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	
	IF EXISTS (SELECT udi.* FROM TblUserDayInc udi WITH (NOLOCK) INNER JOIN inserted ins ON udi.User_ID=ins.User_ID WHERE udi.DT>ins.DT) BEGIN
		UPDATE
			TblUserDayInc
		SET
			Inc=udi.Inc+((ins.Out-del.Out))
		FROM
			TblUserDayInc udi INNER JOIN
			inserted ins ON udi.User_ID=ins.User_ID INNER JOIN
			deleted del ON ins.User_ID=del.User_ID and ins.DT=del.DT
		WHERE
			udi.DT>ins.DT
	END
END
27 фев 12, 10:13    [12155095]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
orunbek,

EXISTS зачем? У тебя уже есть точно такое же условие в запросе на апдейт. Если не найдется ни одной записи удовлетворяющей условию, то будет обновлено 0 записей. Иначе обновятся только те записи, которые удовлетворяют условию. EXISTS не нужен.
27 фев 12, 10:19    [12155138]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
orunbek,

А, да, еще подумал о том, что за один апдейт может быть обовленно сразу несколько записей. Не уверен что наше решение отработает в этом случае правильно, надо проверять...
UPDATE TblUserDayInc t
    SET t.Pays = t.Pays + 10
    WHERE t.User_ID = 1
      AND t.DT IN ('20120225', '20120229')
27 фев 12, 10:26    [12155168]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный триггер или курсор?  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Guf
orunbek,

EXISTS зачем? У тебя уже есть точно такое же условие в запросе на апдейт. Если не найдется ни одной записи удовлетворяющей условию, то будет обновлено 0 записей. Иначе обновятся только те записи, которые удовлетворяют условию. EXISTS не нужен.


ну да, в принципе
EXISTS ставил для того чтобы предварительно проверить, может быть обновляется запись за вчера
соответственно не нужно выполнять операцию UPDATE
но сейчас убрал уже
спасибо за подсказку
27 фев 12, 10:55    [12155303]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить