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

Откуда:
Сообщений: 679
Всем привет!

Помогите решить правильно задачку.

Есть историческая таблица, куда прописывается id клиента и его бонусы. Они могут быть как положительные так и отрицательные.
Для данной таблички создан аггренированный view с sum(bonus) и group by client_id.

Нужно запретить вставку/обновление/удаление записей в исторической таблице, которые могут привести к отрицательному значению бонусов у клиентов.

Как реализовать правильно проверку значения баланса, чтобы не допустить отрицательное значение баланса ?

На уровне триггера after для таблицы или view-ера или есть другое красивое решение?

Решение нужно для mssql 2008 r2
5 авг 13, 01:15    [14661852]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
aleks2
Guest
CONSTRAINT напиши.
5 авг 13, 06:24    [14661936]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
aleks2
CONSTRAINT напиши.


Он приемлем для вставки и обновления.
Для удаления записи из исходной таблицы подойдет ?
5 авг 13, 07:52    [14661993]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Check constraint не срабатывает при удалении записей, поэтому не подходит. Далее, для представления нельзя создать триггер after, можно только instead of. Который не будет срабатывать при модификации данных в таблице. Поэтому триггер на представление тоже не подходит.

Итого остаётся триггер на таблице.
5 авг 13, 09:37    [14662131]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
srtuijg
Guest
в таблицу clients запилить колонку total_bonus.

в триггере after на таблице бонусов проверять значение с total_bonus, если не устраивает, кидаем ексепшн, если все чики пики, изменяем значение total_bonus. в таблице clients.

вьюху переписываем на тупую выборку из таблицы clients двух колонок clientid,total_bonus.
5 авг 13, 09:53    [14662181]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Barkan
Member

Откуда: Дремучее заМКАДье
Сообщений: 503
Testor1,

А я бы порекомендовал написать хранимые процедуры, через которые и делать все операции
с исторической таблицей.
5 авг 13, 12:10    [14663034]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
srtuijg
в таблицу clients запилить колонку total_bonus.

в триггере after на таблице бонусов проверять значение с total_bonus, если не устраивает, кидаем ексепшн, если все чики пики, изменяем значение total_bonus. в таблице clients.

вьюху переписываем на тупую выборку из таблицы clients двух колонок clientid,total_bonus.


Будет работать быстрее на селект, но не надежней
5 авг 13, 21:56    [14666571]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
Гость333
Check constraint не срабатывает при удалении записей, поэтому не подходит. Далее, для представления нельзя создать триггер after, можно только instead of. Который не будет срабатывать при модификации данных в таблице. Поэтому триггер на представление тоже не подходит.

Итого остаётся триггер на таблице.


Скорее всего это и есть единственный "оптимальный" вариант
5 авг 13, 21:57    [14666574]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Testor1, srtuijg

Откройте для себя всё разнообразие индексов:
+ фильтрованные
+ агрегирующие

+
USE tempdb
GO
CREATE TABLE dbo.TBonus (
	 ID	Int	IDENTITY
	 CONSTRAINT PK_TBonus PRIMARY KEY
	,Client	Int	NOT NULL
	,Amount	Money	NOT NULL
)
CREATE INDEX IX_Client ON dbo.TBonus (Client)
GO
CREATE VIEW dbo.VBonus WITH SCHEMABINDING AS
SELECT	 B.Client
	,Count_Big(*)	AS [Count]
	,Sum(B.Amount)	AS Total
FROM	dbo.TBonus	B
GROUP BY B.Client
GO
CREATE UNIQUE CLUSTERED INDEX PK_VBonus ON dbo.VBonus(Client)
GO
INSERT dbo.TBonus VALUES (1,+10)
INSERT dbo.TBonus VALUES (1,-20)
INSERT dbo.TBonus VALUES (1,+5)
INSERT dbo.TBonus VALUES (1,-3)
GO
SELECT * FROM dbo.TBonus
SELECT * FROM dbo.VBonus
GO
DROP VIEW  dbo.VBonus;
DROP TABLE dbo.TBonus;

Т.е. на вьюшки можно вешать индексы, более того - уникальные.
Т.о. ограничения можно ставить не только на одну таблицу, но и на несколько.

автор
для представления нельзя создать триггер after, можно только instead of. Который не будет срабатывать при модификации данных в таблице.
Отключённый триггер на таблице также не будет срабатывать.
А также при отключении опции "nested trigger" можно также "умудриться" чтобы не сработало.

Серебряной пули нет. Но подход правильный.
автор
А я бы порекомендовал написать хранимые процедуры, через которые и делать все операции
Привет процедурномыслящим. Но при этом +1 к предыдущему пункту.

Testor1
Скорее всего это и есть единственный "оптимальный" вариант
Без агрегирующего индекса не думаю что будет оптимальней, как по скорости, так и по занимаемому месту.
6 авг 13, 10:05    [14667648]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
KRS544
Member

Откуда:
Сообщений: 497
Не агрегируйте данные, которые приводят к отрицательному результату - т.е. надо переделать View...
6 авг 13, 11:01    [14668013]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
KRS544
Не агрегируйте данные, которые приводят к отрицательному результату - т.е. надо переделать View...
Что за бред? Зачем вообще превращать из базы помойку?


PS: Не надо таких советов, бомжатников надо выкидывать из города, пока это не стало эпидемией.
Хотя уже поздно ...
6 авг 13, 11:47    [14668351]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
Mnior
Testor1, srtuijg

Откройте для себя всё разнообразие индексов:
+ фильтрованные
+ агрегирующие

+
USE tempdb
GO
CREATE TABLE dbo.TBonus (
	 ID	Int	IDENTITY
	 CONSTRAINT PK_TBonus PRIMARY KEY
	,Client	Int	NOT NULL
	,Amount	Money	NOT NULL
)
CREATE INDEX IX_Client ON dbo.TBonus (Client)
GO
CREATE VIEW dbo.VBonus WITH SCHEMABINDING AS
SELECT	 B.Client
	,Count_Big(*)	AS [Count]
	,Sum(B.Amount)	AS Total
FROM	dbo.TBonus	B
GROUP BY B.Client
GO
CREATE UNIQUE CLUSTERED INDEX PK_VBonus ON dbo.VBonus(Client)
GO
INSERT dbo.TBonus VALUES (1,+10)
INSERT dbo.TBonus VALUES (1,-20)
INSERT dbo.TBonus VALUES (1,+5)
INSERT dbo.TBonus VALUES (1,-3)
GO
SELECT * FROM dbo.TBonus
SELECT * FROM dbo.VBonus
GO
DROP VIEW  dbo.VBonus;
DROP TABLE dbo.TBonus;

Т.е. на вьюшки можно вешать индексы, более того - уникальные.
Т.о. ограничения можно ставить не только на одну таблицу, но и на несколько.

автор
для представления нельзя создать триггер after, можно только instead of. Который не будет срабатывать при модификации данных в таблице.
Отключённый триггер на таблице также не будет срабатывать.
А также при отключении опции "nested trigger" можно также "умудриться" чтобы не сработало.

Серебряной пули нет. Но подход правильный.
автор
А я бы порекомендовал написать хранимые процедуры, через которые и делать все операции
Привет процедурномыслящим. Но при этом +1 к предыдущему пункту.

Testor1
Скорее всего это и есть единственный "оптимальный" вариант
Без агрегирующего индекса не думаю что будет оптимальней, как по скорости, так и по занимаемому месту.


Спасибо за пост. Не совсем разобрался.

Уникальный индекс я использую и активно, для ограничений значений столбца на уникальность.
В твоем примере уникальный индекс на айди клиента на вьюху CREATE UNIQUE CLUSTERED INDEX PK_VBonus ON dbo.VBonus(Client).
Для быстроты выборки как понимаю и возможности спользования WITH(NOEXPAND)

Вопрос, а где аггрегирующий индекс и ограничение на значение суммы баланса >0 ?

Про фильтрацию по индексу да слышал, но пока не использовал. Как понимаю фильтрация в индексе используется, только для того чтобы проиндексировать определенные значения столбца/ов, а не все значения и такая фильтрация не работает как ограничения.

---
Отфильтрованный индекс — это оптимизированный некластеризованный индекс, особенно подходящий для запросов, осуществляющих выборку из хорошо определенного набора данных. Он использует предикат фильтра для индексирования части строк в таблице. Хорошо спроектированный отфильтрованный индекс позволяет повысить производительность запросов, снизить затраты на обслуживание и хранение индексов по сравнению с полнотабличными индексами.
6 авг 13, 23:56    [14672928]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
KRS544
Не агрегируйте данные, которые приводят к отрицательному результату - т.е. надо переделать View...


Вопрос не в том что аггрегировать, а в том как запретить изменения которые к этому могут привести.
7 авг 13, 00:10    [14672982]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Testor1
Для быстроты выборки как понимаю и возможности спользования WITH(NOEXPAND)
WITH(NOEXPAND)
1. включает использование индексов на view для не Enterprise версий
2. заставляет использовать только их (в случае явного указания view?)
А версию вашего сервера я не знаю - поэтому на всякий случай напомнил.
В Enterprise индексы сработают даже без указания view - главное чтобы запросы были логически идентичными (подмножеством).

Testor1
Вопрос, а гдеограничение на значение суммы баланса >0 ?
А что сложно написать самому, в триггере?

На самом деле в памяти есть ссылки, как умудрялся делать сложные ограничения (констраинты), но эти линки битые.
Скоре всего это остатки эйфории об наложении уникальности на вьюшках.
Ибо логика вопит что нелинейные вещи в агрегатках невозможны в принципе.
И вывернуться так чтобы при <0 появлялись дубли не получится.

Testor1
Как понимаю фильтрация в индексе используется, только для того чтобы проиндексировать определенные значения столбца/ов, а не все значения и такая фильтрация не работает как ограничения.
+ Работает:
USE tempdb
GO
CREATE TABLE dbo.TEnum (
	 ID		Int	IDENTITY
	 CONSTRAINT PK_TEnum		PRIMARY KEY
	,Name		NVarChar(250)	NOT NULL
	,[Default]	Bit		NOT NULL
	 CONSTRAINT DF_TEnum_Default	DEFAULT 0
)
CREATE UNIQUE INDEX UQ_Default ON dbo.TEnum ([Default]) WHERE ([Default] = 1)
INSERT dbo.TEnum (Name) VALUES ('A'),('B'),('C'),('D');
GO
UPDATE E SET [Default] = 1 FROM dbo.TEnum E WHERE E.ID = 2; SELECT * FROM dbo.TEnum E;	-- Ок
GO
UPDATE E SET [Default] = 1 FROM dbo.TEnum E WHERE E.ID = 3; SELECT * FROM dbo.TEnum E;	-- Error
GO
UPDATE	E
SET	[Default] = CASE WHEN E.ID = 3 THEN 1 ELSE 0 END
FROM	dbo.TEnum E; SELECT * FROM dbo.TEnum E;	-- Ок
GO
DROP TABLE dbo.TEnum;


PS: Рассыпать соль - плохая примета - к ссоре.
А откуда пошло? А потому что N веков назад она была такой дорогой, что рассыпав, гарантированно получаешь звездюлей.
Вот вся хитрость приметы. Хоть премию Дарвина вручай.
Так вот, думайте головой, а не цитируйте "best practices". Иначе кроме симулякров в голове больше ничего не будет.
7 авг 13, 01:33    [14673201]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
Mnior
Testor1
Для быстроты выборки как понимаю и возможности спользования WITH(NOEXPAND)
WITH(NOEXPAND)
1. включает использование индексов на view для не Enterprise версий
2. заставляет использовать только их (в случае явного указания view?)
А версию вашего сервера я не знаю - поэтому на всякий случай напомнил.
В Enterprise индексы сработают даже без указания view - главное чтобы запросы были логически идентичными (подмножеством).

Testor1
Вопрос, а гдеограничение на значение суммы баланса >0 ?
А что сложно написать самому, в триггере?

На самом деле в памяти есть ссылки, как умудрялся делать сложные ограничения (констраинты), но эти линки битые.
Скоре всего это остатки эйфории об наложении уникальности на вьюшках.
Ибо логика вопит что нелинейные вещи в агрегатках невозможны в принципе.
И вывернуться так чтобы при <0 появлялись дубли не получится.

Testor1
Как понимаю фильтрация в индексе используется, только для того чтобы проиндексировать определенные значения столбца/ов, а не все значения и такая фильтрация не работает как ограничения.
+ Работает:
USE tempdb
GO
CREATE TABLE dbo.TEnum (
	 ID		Int	IDENTITY
	 CONSTRAINT PK_TEnum		PRIMARY KEY
	,Name		NVarChar(250)	NOT NULL
	,[Default]	Bit		NOT NULL
	 CONSTRAINT DF_TEnum_Default	DEFAULT 0
)
CREATE UNIQUE INDEX UQ_Default ON dbo.TEnum ([Default]) WHERE ([Default] = 1)
INSERT dbo.TEnum (Name) VALUES ('A'),('B'),('C'),('D');
GO
UPDATE E SET [Default] = 1 FROM dbo.TEnum E WHERE E.ID = 2; SELECT * FROM dbo.TEnum E;	-- Ок
GO
UPDATE E SET [Default] = 1 FROM dbo.TEnum E WHERE E.ID = 3; SELECT * FROM dbo.TEnum E;	-- Error
GO
UPDATE	E
SET	[Default] = CASE WHEN E.ID = 3 THEN 1 ELSE 0 END
FROM	dbo.TEnum E; SELECT * FROM dbo.TEnum E;	-- Ок
GO
DROP TABLE dbo.TEnum;


PS: Рассыпать соль - плохая примета - к ссоре.
А откуда пошло? А потому что N веков назад она была такой дорогой, что рассыпав, гарантированно получаешь звездюлей.
Вот вся хитрость приметы. Хоть премию Дарвина вручай.
Так вот, думайте головой, а не цитируйте "best practices". Иначе кроме симулякров в голове больше ничего не будет.



У меня Ent версия.

Триггер написать не проблема, но мне не нравится AFTER. Для массовых изменений это тяжелый триггер.
После того как будет произведен инсерт но не закомичен, нужно будет делать селект выборку из view для значений из INSERTED. В случае если хоть какое-то значение сумм бонусов меньше нуля, то потребуется откатить ВСЕ изменения. Мне кажеться это не самое оптимальное решение.

Можно использовать вариант INSTEAD OF и сравнивать значение суммы из VIEW со значением из INSERTED. Если первое меньше, чем второе в абсолютном значении (для <0), то не делать вставку конкретного значения или вообще всех строк. НО при таком решении я лишаюсь возможности использовать relations, а меня это не устраивает.

Что касается примера. Я его прекрасно понимаю и ничего нового для себя я не узнал.
Мне не понятно, как его использовать для проверки аггрегированной суммы бонусов на конкретном клиенте?
Для проверки уникальности значений - он подходит идеально.
7 авг 13, 08:51    [14673506]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Testor1
Триггер написать не проблема, но мне не нравится AFTER. Для массовых изменений это тяжелый триггер.
После того как будет произведен инсерт но не закомичен, нужно будет делать селект выборку из view для значений из INSERTED. В случае если хоть какое-то значение сумм бонусов меньше нуля, то потребуется откатить ВСЕ изменения. Мне кажеться это не самое оптимальное решение.

Можно использовать вариант INSTEAD OF и сравнивать значение суммы из VIEW со значением из INSERTED. Если первое меньше, чем второе в абсолютном значении (для <0), то не делать вставку конкретного значения или вообще всех строк. НО при таком решении я лишаюсь возможности использовать relations, а меня это не устраивает.
Так, давайте разбираться с тараканами.
1. "Проверка Перед действием, а не После"
Ошибочность этого таракана становится очевидным после того как приходит понимания природы транзакционности.
Откуда следует что невозможно добиться консестивности данных с проверкой ДО действия, а не ПОСЛЕ.
Параллельные транзакции по отдельности могут давать Ok (плюс баланса) при проверке ДО, но вместе они выйдут в Error (минус баланса). Это понятно надеюсь?!
Тогда приходится блокировать запись с намерением на изменение, перед проверкой, чтобы упорядочить изменения. А такая дополнительная необходимость сводит на нет весь смысл "а давайте проверять ДО" и остаются только минусы - чрезмерная сложность кода и огромная подверженность ошибкам при нуле профита, те же "держать ВСЕ изменения", "откатить придётся ВСЁ", как ни крути.

2. "Частичность множества записей"
Помимо выводов и предыдущего пункта. Надо понимать что одна команда на множество записей - это целостность бизнес логики.
Науя и где вы видели (в нормальных системах), что логически совершенно не связанные вещи делаются скопом? (давайте мы кривые руки выкинем, *nix way тоталитарен)
Если что-то делается целым, оно должно пройти целым, иначе оно должно целым откатить.

А вот эта вот ваша "лишь бы пропихнуть" не имеет совершенно никакой логики и основания.
Надо чётко понимать где и что происходит. В 99% системы всё кристально, чисто и чётко.
Единственное про что тут может идти речь - всякие массовые внешние говно-данные, это 1% на который нельзя сказать - By Default поведение в системе.

Да, с говном надо думать как оптимальнее, надо смотреть вероятности событий и как оптимальнее решить.
Но в большинстве, здравомыслящий человек в 99% случаев борется с источником говна, а не пытается его проглотить.
В остальном делает процедурки с костылями (не триггера) которые чистят, но всё равно в большинстве - полный откат.
Надеюсь дискуссия не пойдёт в эту больную тему.

Возвращаясь к предыдущему. В остальном надо или не делать скопом, то что не должно делаться скопом, или смотреть вероятности событий и принимать иные решения под такие задачи. В большинстве, такие "хитрые" задачи означают подводные камни в самом бизнес решении (предметной области) и говорят что это решение скорее ошибочно (опять же говно-решение).

"Не мир подстраивается под восприятие, а восприятие под мир". Транзакционность это такие же базовые законы вселенной, а не прихоть MS. "Если Закон нельзя выполнить/соблюдать - это означает неправильность Закона, а не некомпетентность его исполнителей". <Картинко Гэндальфа>
Testor1
Что касается примера. Мне не понятно, как его использовать для проверки аггрегированной суммы бонусов на конкретном клиенте?
Для проверки уникальности значений - он подходит идеально.
1. Каких ещё проверок уникальности? Вы о чём вообще?
2. Очень топорно: вам не нужно будет каждый раз агрегировать суммы (те десятки.сотни/тысячи строк) что бы проверить что она ушла в минус, во вью она уже посчитана и надо тупо её проверить на отрицательность. Всё.
Я не предлагал "что-то новое в подходе", я просто напомнил, что не забывайте о возможностях оптимизации, что надо выносить блоки в отдельные сущности для дальнейшего гибкого подхода.

КО
7 авг 13, 10:46    [14674127]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
Mnior
Testor1
Триггер написать не проблема, но мне не нравится AFTER. Для массовых изменений это тяжелый триггер.
После того как будет произведен инсерт но не закомичен, нужно будет делать селект выборку из view для значений из INSERTED. В случае если хоть какое-то значение сумм бонусов меньше нуля, то потребуется откатить ВСЕ изменения. Мне кажеться это не самое оптимальное решение.

Можно использовать вариант INSTEAD OF и сравнивать значение суммы из VIEW со значением из INSERTED. Если первое меньше, чем второе в абсолютном значении (для <0), то не делать вставку конкретного значения или вообще всех строк. НО при таком решении я лишаюсь возможности использовать relations, а меня это не устраивает.
Так, давайте разбираться с тараканами.
1. "Проверка Перед действием, а не После"
Ошибочность этого таракана становится очевидным после того как приходит понимания природы транзакционности.
Откуда следует что невозможно добиться консестивности данных с проверкой ДО действия, а не ПОСЛЕ.
Параллельные транзакции по отдельности могут давать Ok (плюс баланса) при проверке ДО, но вместе они выйдут в Error (минус баланса). Это понятно надеюсь?!
Тогда приходится блокировать запись с намерением на изменение, перед проверкой, чтобы упорядочить изменения. А такая дополнительная необходимость сводит на нет весь смысл "а давайте проверять ДО" и остаются только минусы - чрезмерная сложность кода и огромная подверженность ошибкам при нуле профита, те же "держать ВСЕ изменения", "откатить придётся ВСЁ", как ни крути.

2. "Частичность множества записей"
Помимо выводов и предыдущего пункта. Надо понимать что одна команда на множество записей - это целостность бизнес логики.
Науя и где вы видели (в нормальных системах), что логически совершенно не связанные вещи делаются скопом? (давайте мы кривые руки выкинем, *nix way тоталитарен)
Если что-то делается целым, оно должно пройти целым, иначе оно должно целым откатить.

А вот эта вот ваша "лишь бы пропихнуть" не имеет совершенно никакой логики и основания.
Надо чётко понимать где и что происходит. В 99% системы всё кристально, чисто и чётко.
Единственное про что тут может идти речь - всякие массовые внешние говно-данные, это 1% на который нельзя сказать - By Default поведение в системе.

Да, с говном надо думать как оптимальнее, надо смотреть вероятности событий и как оптимальнее решить.
Но в большинстве, здравомыслящий человек в 99% случаев борется с источником говна, а не пытается его проглотить.
В остальном делает процедурки с костылями (не триггера) которые чистят, но всё равно в большинстве - полный откат.
Надеюсь дискуссия не пойдёт в эту больную тему.

Возвращаясь к предыдущему. В остальном надо или не делать скопом, то что не должно делаться скопом, или смотреть вероятности событий и принимать иные решения под такие задачи. В большинстве, такие "хитрые" задачи означают подводные камни в самом бизнес решении (предметной области) и говорят что это решение скорее ошибочно (опять же говно-решение).

"Не мир подстраивается под восприятие, а восприятие под мир". Транзакционность это такие же базовые законы вселенной, а не прихоть MS. "Если Закон нельзя выполнить/соблюдать - это означает неправильность Закона, а не некомпетентность его исполнителей". <Картинко Гэндальфа>
Testor1
Что касается примера. Мне не понятно, как его использовать для проверки аггрегированной суммы бонусов на конкретном клиенте?
Для проверки уникальности значений - он подходит идеально.
1. Каких ещё проверок уникальности? Вы о чём вообще?
2. Очень топорно: вам не нужно будет каждый раз агрегировать суммы (те десятки.сотни/тысячи строк) что бы проверить что она ушла в минус, во вью она уже посчитана и надо тупо её проверить на отрицательность. Всё.
Я не предлагал "что-то новое в подходе", я просто напомнил, что не забывайте о возможностях оптимизации, что надо выносить блоки в отдельные сущности для дальнейшего гибкого подхода.

КО


С чем-то согласен в плане подхода и триггеров.
Что касается процедур - согласен, что так правильней, НО использование проверок на уровне триггеров не раз спасало от ошибочных операций админов при решении инцидентов на уровне таблиц.

По поводу примера. Сорри если я вас не понимаю. Я понимаю про аггрегацию во вью и это все работает как вы пишите.
Но не могу понять как ваш пример можно использовать для ограничение операций с бонусами в моей задаче? Есть возможность переделать ваш пример под мою задачу ?
8 авг 13, 00:03    [14678628]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Testor1
Что касается процедур - согласен, что так правильней, НО использование проверок на уровне триггеров не раз спасало от ошибочных операций админов при решении инцидентов на уровне таблиц.
Ну дык именно что AFTER триггера и делают проверку апосля, а процедуры могут убирать заведомо левые данные.

Testor1
Но не могу понять как ваш пример можно использовать для ограничение операций с бонусами в моей задаче?
Он не решает проблемы контроля - это просто оптимизационый механизм - не более.
Testor1
Есть возможность переделать ваш пример под мою задачу ?
+ Как вариант
USE tempdb
GO
CREATE TABLE dbo.TBonus (
	 ID	Int	IDENTITY
	 CONSTRAINT PK_TBonus PRIMARY KEY
	,Client	Int	NOT NULL
	,Amount	Money	NOT NULL
)
CREATE INDEX IX_Client ON dbo.TBonus (Client)
GO
CREATE VIEW dbo.VBonus WITH SCHEMABINDING AS
SELECT	 B.Client
	,Count_Big(*)	AS [Count]
	,Sum(B.Amount)	AS Total
FROM	dbo.TBonus	B
GROUP BY B.Client
GO
CREATE UNIQUE CLUSTERED INDEX PK_VBonus ON dbo.VBonus(Client)
GO
CREATE TRIGGER trBonusCheck ON dbo.TBonus
AFTER INSERT,UPDATE,DELETE AS BEGIN
	SET NOCOUNT ON;

	IF Exists(
		SELECT	*
		FROM	Inserted	I
		JOIN	dbo.VBonus	T ON T.Client = I.Client
		WHERE	T.Total < 0
	) OR Exists(
		SELECT	*
		FROM	Deleted		D
		JOIN	dbo.VBonus	T ON T.Client = D.Client
		WHERE	T.Total < 0
	) BEGIN
		ROLLBACK
		RAISERROR('Бонус уходит в минус',18,1)
		RETURN
	END
END
GO
INSERT dbo.TBonus VALUES (1,+10)
GO
INSERT dbo.TBonus VALUES (1,-20)
GO
INSERT dbo.TBonus VALUES (2,+5)
GO
INSERT dbo.TBonus VALUES (2,-3)
GO
SELECT * FROM dbo.TBonus
SELECT * FROM dbo.VBonus
GO
DROP VIEW  dbo.VBonus;
DROP TABLE dbo.TBonus;
Но лучше уж DELETE вообще запретить - тогда на одно условие меньше. Или разделить на два триггера INSERT,UPDATE и DELETE
Можно конечно повесить индекс и вообще убрать использование псевдотаблиц Inserted/Deleted - но он не оправдан.
А фильтрованные (некластеризованные) индексы на VIEW нельзя вешать.
С одной стороны непонятно - ибо только кластерный должен быть линейным.
С другой - фильтрованные индексы могут быть отброшены для модификации оптимизатором - но это не повод для "не реализации".
Вот если реализуют, то вам и триггера не понадобятся.
+ Вот почти работающий пример
CREATE VIEW dbo.VBonus WITH SCHEMABINDING AS
SELECT	 B.Client
	,Count_Big(*)	AS [Count]
	,Sum(B.Amount)	AS Total
	,Sum(0)		AS [Err]
FROM	dbo.TBonus	B
GROUP BY B.Client
GO
CREATE UNIQUE CLUSTERED INDEX PK_VBonus ON dbo.VBonus(Client)
CREATE UNIQUE INDEX UX_VBonus ON dbo.VBonus(Err) WHERE (Total < 0)
GO
Ограничение только в WHERE (Total < 0)

Отличный повод для создания Suggestion. Вот вы Testor1 буржуйский знаете? Вот вы напишите туда простенький отзыв, а мы обязательно поддержим. Так что с вас спрос.
Если нет аккаунта - то прошу присутствующих накалякать простенький текст, а я уже отошлю, с примером.

Вот этот Support for more complex WHERE clause in filtered indexes внушает оптимизм, ибо его не закрыли.

Создаём, плюсуем.
Если сделают - это будет бомба.
8 авг 13, 15:19    [14681697]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mnior
Вот этот Support for more complex WHERE clause in filtered indexes внушает оптимизм, ибо его не закрыли.
Это к тому что его намного сложнее реализовать, чем предлагаемый.
8 авг 13, 15:21    [14681713]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
цукапцукацук
Guest
Mnior,
в твоем примере

Mnior
CREATE VIEW dbo.VBonus WITH SCHEMABINDING AS
SELECT	 B.Client
	,Count_Big(*)	AS [Count]
	,Sum(B.Amount)	AS Total
	,Sum(0)		AS [Err]
FROM	dbo.TBonus	B
GROUP BY B.Client
GO
CREATE UNIQUE CLUSTERED INDEX PK_VBonus ON dbo.VBonus(Client)
CREATE UNIQUE INDEX UX_VBonus ON dbo.VBonus(Err) WHERE (Total < 0)
GO



Вот если бы была возможность вместо WHERE (Total < 0) написать FAIL_WHEN(Total < 0) то было бы хорошо. а так не понял цимуса, в текущем варианте он не позволяет только одновременно нескольких клиентов с балансом < 0. А одного отрицательного пропустит же, сцкнхблпздц. :(
8 авг 13, 16:43    [14682530]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
fwfwefwefe
Guest
разве что запилить VIEW так чтобы добавлялся фиктивный клиент с Total = -1

CREATE VIEW dbo._VBonus WITH SCHEMABINDING AS
SELECT	 B.Client
	,Count_Big(*)	AS [Count]
	,Sum(B.Amount)	AS Total
	,Sum(0)		AS [Err]
FROM	dbo.TBonus	B
GROUP BY B.Client

UNION ALL

SELECT -1,  0, -1, 0
GO
CREATE UNIQUE CLUSTERED INDEX PK_VBonus ON dbo._VBonus(Client)
CREATE UNIQUE INDEX UX_VBonus ON dbo._VBonus(Err) WHERE (Total < 0)
GO

CREATE VIEW dbo.VBonus WITH SCHEMABINDING AS
SELECT	 B.Client
	,Count_Big(*)	AS [Count]
	,Sum(B.Amount)	AS Total
	,Sum(0)		AS [Err]
FROM	dbo._VBonus	B
WHERE Client <> -1
8 авг 13, 16:56    [14682648]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
fqwefqwefqwe
Guest
WHERE Client > -1
8 авг 13, 16:58    [14682664]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Уже раз 3й нарываюсь.
fwfwefwefe
разве что запилить VIEW так чтобы добавлялся фиктивный клиент с Total = -1
Не VIEW запилить, а прямиком в таблицу добавить фиктивную запись. Ибо VIEW с индексами могут быть только линейными, UNION не катит.
А так да, кривовато выходит каменный цветочек. Но главное что можно.
8 авг 13, 17:09    [14682750]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
qwkefjhwef
Guest
можно так запилить как выше предлагал, хоть агрегирующие индексы, хоть лисапед тут по мне так один хрен,

CREATE TABLE [dbo].[Client](
	[id_client] [int] NOT NULL,
	[name] [nvarchar](100) NOT NULL,
	[total] [money] NOT NULL,
 CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED 
(
	[id_client] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Transaction](
	[id_transaction] [int] NOT NULL,
	[s] [money] NOT NULL,
	[id_client] [int] NOT NULL,
 CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED 
(
	[id_transaction] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Client] ADD  CONSTRAINT [DF_Client_total]  DEFAULT ((0)) FOR [total]
GO
ALTER TABLE [dbo].[Transaction]  WITH CHECK ADD  CONSTRAINT [FK_Transaction_Client] FOREIGN KEY([id_client])
REFERENCES [dbo].[Client] ([id_client])
GO
ALTER TABLE [dbo].[Transaction] CHECK CONSTRAINT [FK_Transaction_Client]
GO
ALTER TABLE [dbo].[Client]  WITH CHECK ADD  CONSTRAINT [CK_ClientTotalNonNegative] CHECK  (([Total]>=(0)))
GO
ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [CK_ClientTotalNonNegative]
GO

create trigger [dbo].[ad_update_totals] 
   on  [dbo].[Transaction]
   after delete
as 
begin
	set nocount on;

	update tt2
	set tt2.total -= delta
	from
	(
		select id_client, sum(s) as delta
		from deleted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

GO
create trigger [dbo].[ai_update_totals] 
   on  [dbo].[Transaction]
   after insert
as 
begin
	set nocount on;

	update tt2
	set tt2.total += delta
	from
	(
		select id_client, sum(s) as delta
		from inserted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

create trigger [dbo].[au_update_totals] 
   on  [dbo].[Transaction]
   after update
as 
begin
	set nocount on;

	raiserror(N'ХРЮ!', 18, 1);
end
GO
8 авг 13, 17:31    [14682931]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
jfjfjfjfjfj
Guest
тут эта хрень ключевая

ALTER TABLE [dbo].[Client]  WITH CHECK ADD  CONSTRAINT [CK_ClientTotalNonNegative] CHECK  (([Total]>=(0)))
8 авг 13, 17:36    [14682960]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить