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

Откуда:
Сообщений: 2267
Прошу помощи в написании ограничений.

Суть: в родительской табличке Master.AllSumm лежит сумма по строкам дочерней таблицы Child.DiffSumm, какое надо написать ограничение, что бы такое условие выполнялось.

Мои "приседания" на стадии родителя валятся с ошибкой нарушения ограничений, ну собственно это и следует ожидать, ... как обычно поступают:

use tempdb 

create table [Master] (ID int primary key, AllSumm money )
create table [Child]  (ID int primary key, Master_ID int CONSTRAINT FK_Master FOREIGN KEY  
        REFERENCES [Master](ID), DiffSumm money )
go 

create function MasterCheck (@AllSumm money, @ID int )
returns bit
 as 
 begin
 declare @OK bit
 if @AllSumm = isnull((select SUM(DiffSumm) from Child where ID = @ID),0)
	set @OK = 0
 else 
	set @OK = 1
 RETURN @OK
 end 
go 

alter table [Master]  WITH CHECK add constraint CK_MasterCheck CHECK  (dbo.MasterCheck(AllSumm , ID ) = 0)
go 

insert into [Master] (ID, AllSumm) values (1, 1)
insert into [Child] (ID, Master_ID, DiffSumm) values (1, 1, 1)
go
drop function MasterCheck
drop table [Child]
drop table [Master]
go 
13 июн 13, 12:38    [14427626]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
а почему сумму надо хранить?
может, лучше по необходимости вычислять?
13 июн 13, 12:42    [14427659]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
Glory
Member

Откуда:
Сообщений: 104751
PaulWist
как обычно поступают:

Обычно пишут триггер. Потому что кроме insert/update есть еще и delete
13 июн 13, 12:42    [14427661]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
Паганель
а почему сумму надо хранить?
может, лучше по необходимости вычислять?


Сумма документа (приложения, доп. соглашения итп документа) определяется договором, под неё набираются товары и услуги (это содержание документа), на момент внесения документа известна и сумма в шапке и содержание, вопрос "а как технически" внести изначально правильные данные в две таблички, как должно выглядеть ограничение.
13 июн 13, 12:56    [14427748]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
Glory
PaulWist
как обычно поступают:

Обычно пишут триггер. Потому что кроме insert/update есть еще и delete


Пусть будет триггер, не суть,...мне не понятно вот, что:

- на клиенте юзер поправил в шапке сумму документа, так же поправил и содержание документа, предположим, что суммы по строкам и сумма в шапке правильные.

- вторым шагом эти поправленные данные надо сохранить в таблички
-- начинам сохранять шапку (с измененной суммой), срабатывает триггер/check НО ограничение начинает проверять по СУЩЕСТВУЮЩИМ данным, тем которые сейчас лежат в табличке, ведь только вторым шагом будет сохранение поправленного содержания и соотвественно сразу получим ошибку в ограничении, что сумма не совпадает.

Как обычно выходят из такой ситуации?
13 июн 13, 13:03    [14427783]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
PaulWist
Glory
пропущено...

Обычно пишут триггер. Потому что кроме insert/update есть еще и delete


Пусть будет триггер, не суть,...мне не понятно вот, что:

- на клиенте юзер поправил в шапке сумму документа, так же поправил и содержание документа, предположим, что суммы по строкам и сумма в шапке правильные.

- вторым шагом эти поправленные данные надо сохранить в таблички
-- начинам сохранять шапку (с измененной суммой), срабатывает триггер/check НО ограничение начинает проверять по СУЩЕСТВУЮЩИМ данным, тем которые сейчас лежат в табличке, ведь только вторым шагом будет сохранение поправленного содержания и соотвественно сразу получим ошибку в ограничении, что сумма не совпадает.

Как обычно выходят из такой ситуации?
Что первично-то?
Шапка или строки? Уж определитесь.
Логичнее строки, конечно.
Значит, делать шапку вычисляемой и запретить в ней сумму менять непосредственно.
13 июн 13, 13:05    [14427800]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
PaulWist
Сумма документа (приложения, доп. соглашения итп документа) определяется договором, под неё набираются товары и услуги (это содержание документа), на момент внесения документа известна и сумма в шапке и содержание
а хранить в БД сумму зачем?
13 июн 13, 13:05    [14427801]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
Glory
Member

Откуда:
Сообщений: 104751
PaulWist
Как обычно выходят из такой ситуации?

Как и обычно с родительско-дочерними отношениями.
Сначала дочерние записи приводятся в порядок, а только потом родительские.
13 июн 13, 13:05    [14427803]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
PaulWist
Как обычно выходят из такой ситуации?
В шапке документа должна быть сумма по договору, либо ссылка на договор.
Сумма документа считается через индексированное представление.
Документ должен иметь статус "Открыт"/"Закрыт".
Соответствие сумм проверяется при изменении статуса документа на "Закрыт" .
13 июн 13, 13:25    [14427911]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
iap
Что первично-то?
Шапка или строки? Уж определитесь.
Логичнее строки, конечно.
Значит, делать шапку вычисляемой и запретить в ней сумму менять непосредственно.


Первично Шапка, вроде уже написал, может не внятно, что шапка - это как раз та сумма на которую договорились, что внутри это уже вторично.

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

БОЛ говорит следующее:

автор
computed_column_expression
Выражение, определяющее значение вычисляемого столбца. Вычисляемый столбец представляет собой виртуальный столбец, физически не хранящийся в таблице, если для него не установлен признак PERSISTED. Значение столбца вычисляется на основе выражения, использующего другие столбцы той же таблицы.
13 июн 13, 13:31    [14427944]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
Паганель
PaulWist
Сумма документа (приложения, доп. соглашения итп документа) определяется договором, под неё набираются товары и услуги (это содержание документа), на момент внесения документа известна и сумма в шапке и содержание
а хранить в БД сумму зачем?


Нуу-у, а как узнать сумму документа к которой пришли Заказчик и Исполнитель, предположим, что сумму не храним, изменили что-то в содержании документа расчетная сумма поменялась - возникает вопрос: "а какова сумма Документа" - это та которая в БД посчитанная по строкам или та которая стоит на бумаге :)
13 июн 13, 13:36    [14427997]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
PaulWist
это та которая в БД посчитанная по строкам или та которая стоит на бумаге :)
так они имеют право быть неравны?
тогда зачем констрейнт?
13 июн 13, 13:39    [14428021]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
Glory
Member

Откуда:
Сообщений: 104751
Паганель
PaulWist
это та которая в БД посчитанная по строкам или та которая стоит на бумаге :)
так они имеют право быть неравны?
тогда зачем констрейнт?

Это лимит по договору.
13 июн 13, 13:40    [14428025]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
Glory
PaulWist
Как обычно выходят из такой ситуации?

Как и обычно с родительско-дочерними отношениями.
Сначала дочерние записи приводятся в порядок, а только потом родительские.


2Glory ну Вы же понимаете, что контроль должен быть двунаправленный, те триггер на Шапку и триггер на Содержание, ... хорошо на клиенте поправили в шапке и в содержании сумму, при сохранении в табличку Содержания сработает триггер на проверку итоговой суммы в Шапке, те яйца только в профиль :)
13 июн 13, 13:40    [14428027]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
Glory
Паганель
пропущено...
так они имеют право быть неравны?
тогда зачем констрейнт?

Это лимит по договору.


Glory более ясно выразился.
13 июн 13, 13:42    [14428042]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
Glory
Member

Откуда:
Сообщений: 104751
PaulWist
2Glory ну Вы же понимаете, что контроль должен быть двунаправленный, те триггер на Шапку и триггер на Содержание, ... хорошо на клиенте поправили в шапке и в содержании сумму, при сохранении в табличку Содержания сработает триггер на проверку итоговой суммы в Шапке, те яйца только в профиль :)

Это смотря как править.
UPDATE может быть и DELETE+INSЕRT-ом
13 июн 13, 13:42    [14428045]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
Glory
Паганель
пропущено...
так они имеют право быть неравны?
тогда зачем констрейнт?

Это лимит по договору.
то есть условия "равно" должно быть условие "не больше"?

тогда я тоже за мат.вьюху
13 июн 13, 13:43    [14428051]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
invm
PaulWist
Как обычно выходят из такой ситуации?
В шапке документа должна быть сумма по договору, либо ссылка на договор.
Сумма документа считается через индексированное представление.
Документ должен иметь статус "Открыт"/"Закрыт".
Соответствие сумм проверяется при изменении статуса документа на "Закрыт" .


Хорошее предложение, действительно хорошее пердложение... теперь осталось бизнес "уломать" под него :)
13 июн 13, 13:48    [14428088]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
PaulWist
Как обычно выходят из такой ситуации?
При хранении остатков обычно выбирают один из двух вариантов:

1. Процедура (или какой то другой код, который сохраняет данные, например, код клиентского приложения) корректно меняет и подчинённые, и родительские таблицы. Делается это в транзакции, что бы не разъехалось при ошибке.

2. На подчинённые таблицы вешаются триггеры, которые меняют суммарные значения в родительских таблицах при любом изменении данных в подчинённых таблицах. Тогда данные из клиента можно менять как угодно, не заботясь о согласовании данных в каких либо других таблицах.

Принципиально другой подход - не хранить отдельно остатки, ну это уже совсем другая тема.
13 июн 13, 13:48    [14428093]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
alexeyvg
При хранении остатков обычно выбирают один из двух вариантов:
Да, собственно исходный вопрос (про ограничения) решается с этим расчитанным хранящимся значением. Допустим, есть расчитанная сумма, отдельно записан лимит по договору, ну и накладываем ограничение "сумма > лимит"
13 июн 13, 13:51    [14428115]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
Паганель
Glory
пропущено...

Это лимит по договору.
то есть условия "равно" должно быть условие "не больше"?

тогда я тоже за мат.вьюху


ОК, а как тогда должна выглядеть такая вьюха?
13 июн 13, 13:52    [14428124]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
PaulWist
Паганель
пропущено...
то есть условия "равно" должно быть условие "не больше"?

тогда я тоже за мат.вьюху


ОК, а как тогда должна выглядеть такая вьюха?
Читаем внимательно:

Конструирование индексированных представлений
Создание индексированных представлений
13 июн 13, 14:08    [14428260]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
iap
PaulWist
пропущено...


ОК, а как тогда должна выглядеть такая вьюха?
Читаем внимательно:

Конструирование индексированных представлений
Создание индексированных представлений


Единственное не понял как должно выглядеть ограничение для индексированной вьюхи, можно Вас попросить привести пример для табличек в исходном скрипте.
13 июн 13, 14:44    [14428612]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
PaulWist
Единственное не понял как должно выглядеть ограничение для индексированной вьюхи, можно Вас попросить привести пример для табличек в исходном скрипте.
А Вы писали здесь SELECT?
Что-то не вижу...
13 июн 13, 14:54    [14428755]     Ответить | Цитировать Сообщить модератору
 Re: Ограничение на родительскую и дочерние таблички  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
iap
PaulWist
Единственное не понял как должно выглядеть ограничение для индексированной вьюхи, можно Вас попросить привести пример для табличек в исходном скрипте.
А Вы писали здесь SELECT?
Что-то не вижу...


ОК, вот скрипт

use tempdb 

create table [Master] (ID int primary key, AllSumm money )
create table [Child]  (ID int primary key, Master_ID int CONSTRAINT FK_Master FOREIGN KEY  
        REFERENCES [Master](ID), DiffSumm money )
go 

create view MasterChild as
select [Master].[ID], [Master].[AllSumm], CC.DiffSumm
from [Master]
inner join 
(select sum(Child.DiffSumm) DiffSumm, Master_ID from Child
group by Master_ID
) CC on [Master].[ID] = CC.Master_ID
go 

drop view MasterChild
drop table [Child]
drop table [Master]


если поправите и сам скрипт, то буду тоже премного благодарен!
13 июн 13, 15:17    [14429009]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить