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

Откуда:
Сообщений: 20
Предиетная область: таблица - справочник телефонных номеров: id, ... и др. поля.
Некоторые телефонные номера спарены с др. номером. Появляется идея добавить в табл. ссылочное поле id_b, ссылающееся на запись о спаренном номере. Естественно в этой др. записи д.б. ссылка на "ссылающуюся запись" - в общем, понятно. И вот вопрос: возможно ли такую ссылочную целостность реализовать на уровне БД ? Триггер? C трудом представляю логику его работы - тень рекурсии давит. Что другое?
Можно (это уже др. вариант организации данных) в таблицу-справочник номеров вообще ничего не добавлять, а добавить доп. таблицу именно для спаренных номеров типа: id_a, id_b и трактовать это как "номер id_a спарен с номером id_b". Но в таком случае вопрос просто переформулируется: как реализовать целостность для такой табл. (ведь одновременное наличие записей типа A-B и C-A не допускается) не говоря уж о (как мне кажется) проблеммах с выборками...
М.б. кто имеет опыт в схожих случаях?
19 ноя 04, 14:16    [1120774]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
автор
Что другое?


Foreign Key Constraint саму на себя.
19 ноя 04, 14:34    [1120902]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
Smirnov Anton
Member

Откуда: Н.Новгород
Сообщений: 3220
по-моему, логичней сделать
TABLE_SPRAVOCHNIK
(
id,
....
)

TABLE_SPAREN
(
id1,
id2
)
(id1,id2) = PK
19 ноя 04, 14:39    [1120952]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
analyser
Member

Откуда:
Сообщений: 20
pkarklin
Foreign Key Constraint саму на себя.

И что?!
Навесить FK не проблемма. Вопрос в том, как обеспечить непротиворечивость взаимных ссылок. Т.е. если в записи А появилась ссылка на запись В, то в записи В обязана появиться ссылка на А. Ну и т.д.
Я думал, понятно...
19 ноя 04, 14:40    [1120961]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
analyser
Member

Откуда:
Сообщений: 20
Smirnov Anton:
Это тоже не решение - я ведь писал, что недопустимо в доп. табл. наличие записей типа:
A-B
B-C
и т.д.
Спаренными одновременно могут быть только 2 (два) телефона...
19 ноя 04, 14:46    [1120998]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
Smirnov Anton
Member

Откуда: Н.Новгород
Сообщений: 3220
на счёт только 2-х телефонов - не знал
с другой стороны, не всё логику системы можно реализовать структурой БД
перед "спариванием" :-) телефонов надо будет просто проверить, не спарен ли один из них с каким-то другим
19 ноя 04, 14:52    [1121030]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
тень рекурсии давит
и
Спаренными одновременно могут быть только 2 (два) телефона
Попробуйте хранить в id_b первой записи положительное значение id_второй, а во второй отрицательное -(минус) id_первой.
И волки сыты и..., т.е. и однозначность ссылки остается и исчезает тень рекурсии.
???
19 ноя 04, 16:05    [1121505]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
analyser
pkarklin
Foreign Key Constraint саму на себя.

И что?!
Навесить FK не проблемма. Вопрос в том, как обеспечить непротиворечивость взаимных ссылок. Т.е. если в записи А появилась ссылка на запись В, то в записи В обязана появиться ссылка на А. Ну и т.д.
Я думал, понятно...


А вот я сразу не понял. :( Предлагаю создать доп. таблицу из одного столбца, где хранить idшники спаренных номеров (т.е. при спаривании номеров помещать в эту таблицу 2 idшника). А чтоб проверить возможность спаривания достаточно будеть проверить наличие idшника телефона, который предполагается спарить, в доп. таблице.
19 ноя 04, 16:22    [1121587]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
analyser
Member

Откуда:
Сообщений: 20
Продолжаем разговор
1. LR:
Т.е. Вы за первый подход и, стало быть, триггер. И как он должен выглядеть? (Я, наверное, просто не догоняю...). Идет у нас UPDATE на первую запись (id_a) - меняется/удаляется/добавляется значение id_b для спаренного телефона. Мы должны записать во вторую запись (id_b) ссылку на первую....
Я не улавливаю, чем здесь поможет запись ее в отрицательном виде.

2.pkarklin:
Предложение о доп. таблице из одного (?!) поля еще менее понятно. И как из нее определить, кто с кем спарен?
19 ноя 04, 16:39    [1121647]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
автор
2.pkarklin:
Предложение о доп. таблице из одного (?!) поля еще менее понятно. И как из нее определить, кто с кем спарен?


Я предлагал свою доп. таблицу в развитие идеи от Smirnov Anton. Хотя немного подумав и одной таблицы от Smirnov Anton хватит, тока проверку придеться проводить по 2 полям таблицы.
19 ноя 04, 16:57    [1121718]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
Smirnov Anton
Member

Откуда: Н.Новгород
Сообщений: 3220
если хочется быть совсем уверенным, навесь на таблицу
TABLE_SPAREN
(
id1,
id2
)
(id1,id2) = PK
триггеры на update и insert, в которых проверяется условие спаренности только 2-х телефонов
подобную схему я уже делал, только не в MSSQL, а в MSAccess для какой-то программульки, даже с его скудными возможностями всё было нормально, гемороя с проверками типа
(id=id1 or id=id2)
дольшого не было, да и там, я так думаю, эта схема более часто исползовалась
(так уж часто вы будете пользоваться таблицей TABLE_SPAREN)
тем более у вас все карты в виде triggrs, sp, view,...
19 ноя 04, 17:09    [1121766]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
analyser
Member

Откуда:
Сообщений: 20
pkarklin:
Значит, теперь я не понял :)
Кстати, об "идее от Smirnov Anton" (впрочем, в первом посте этот вариант как альтернативный был сразу приведен):Вы считаете, что он будет лучше? Чем?
Ведь непротиворечивость на уровне БД здесь тоже не обеспечить. И, по-всему, пока выходит, что, как сказал тот же Smirnov Anton, "не всё логику системы можно реализовать структурой БД". Т.е из клиента проводить "правильный" анализ и изменение таблиц. Жаль, конечно. Так вот, чем вариант с доп. табл. в таком разе лучше варианта с доп. полем?
19 ноя 04, 17:11    [1121772]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
автор
Ведь непротиворечивость на уровне БД здесь тоже не обеспечить.


почему же не обеспечить?! И именно на уровне БД. Ведь триггер - это объект бд, а не клиента. И его поведение не зависит от того, как и чем вы лезете в Базу. А триггером как раз можно проверять, что номер телефона тока 1 раз содержиться в доп. таблице, правда 2 поля проверять, ну, доп. нагрузка есть доп нагрузка.
19 ноя 04, 17:15    [1121785]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
Я не улавливаю, чем здесь поможет запись ее в отрицательном виде.
Теперь я не улавливаю, где и чем что-то должно помогать?
Вопрос в том, как обеспечить непротиворечивость взаимных ссылок. Т.е. если в записи А появилась ссылка на запись В, то в записи В обязана появиться ссылка на А. Ну и т.д.
Это? В триггере AFTER UPDATE элементарно в любом случае, т.е.для этого не нужно вводить никаких отрицательных значений.
update table set id_b=null from table t inner join deleted d on t.id_a=d.id_b
inner join inserted i on d.id_a=d.id_a where i.id_b<>d.id_b
update table set id_b=i.ida from table t inner join inserted i on t.id_a=i.id_b
inner join deleted d on i.id_a=d.id_a where i.id_b<>d.id_b
А вот как потом работать с этой неопределенной "двойственностью", вот здесь отрицательное значение дало бы нужную (пусть искусственную) ассиметрию...ИМХО.
19 ноя 04, 17:15    [1121789]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
Smirnov Anton
Member

Откуда: Н.Новгород
Сообщений: 3220
под
не всё логику системы можно реализовать структурой БД

я понимал только структуру таблиц и связи между ними
а
автор
из клиента проводить "правильный" анализ и изменение таблиц

триггеры не дадут вам этого сделать
а для пущей уверенности внесите скрипт добавления "спаренного" телефона в хп(где будете всё проверять), и склиента вызывайте его
19 ноя 04, 17:17    [1121795]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
analyser
Member

Откуда:
Сообщений: 20
Smirnov Anton & pkarklin:
Ага! Допер. Значит, и триггер на осн. табл., и доп. таблица. И достоверность обеспечим. Да...
А вот не появится ли потом, при работе с такой структурой, геморрой, к-рый перечеркнет достоинства автоматической достоверности ?(Очень мне не нравится, что искать "свой" телефон я должен в 2-х полях доп. табл.)

LR:
Т.е. триггер на UPDATE, вызывающий UPDATE той же таблицы, это не страшно?
Оно не уйдет в себя? И я напрасно боюсь рекурсии?
19 ноя 04, 18:10    [1121994]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
автор
Значит, и триггер на осн. табл., и доп. таблица.


Триггер лучше на доп. таблицу повесить. Ведь там же искать будем.

автор
Очень мне не нравится, что искать "свой" телефон я должен в 2-х полях доп. табл.


И чем не нравиться? Поиск записи по индексу выполниться моментально.
19 ноя 04, 18:14    [1122006]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
Т.е. триггер на UPDATE, вызывающий UPDATE той же таблицы, это не страшно?
Оно не уйдет в себя? И я напрасно боюсь рекурсии?

BOL - RECURSIVE_TRIGGERS

P.S. В любом случае всегда можно проверить - надо апдейтить или уже не надо...
19 ноя 04, 18:26    [1122034]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
analyser
Member

Откуда:
Сообщений: 20
Исправляюсь (совсем к вечеру до ручки дошел): предыдущ. мой пост - ересь (я как-то умудрился пропустить кусок про триггер на доп. табл. ест-но, имелся в виду именно он.) Sorry.

Да, возможно это решение. Вот только насчет поиска по индексу...
Выбрать тел. с инф-ей о спаренном с ним (если есть):

SELECT table1.*,..., table2.id_2
FROM table1 , table2 ---- я уж попроще, по MS SQL'евски объединю таблицы, ладно?
WHERE table2.id_1 = table1.id1

или:

SELECT table1.*,..., table2.id_1
FROM table1, table2
WHERE table2.id_2 = table1.id1


Писать обобщенный вариант ? И так во всех запросах?

ЗЫ: Пока сочинял, пришел ответ от LR. Отцы, что скажете - м.б все гораздо проще?
19 ноя 04, 18:35    [1122068]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
IMHO, не вижу смысла в дополнительной таблице, в большинстве предполагаемых операций с описанной инф.структурой она будет лишним звеном.
Но Вы же располагаете полной информацией обо всех возможных операциях - вот Вам и решать...
19 ноя 04, 19:27    [1122180]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
analyser
Member

Откуда:
Сообщений: 20
LR, дружище, а ведь Вы, похоже, правы. Заглянул, наконец, в BOL (это все от лени - нет чтобы сначала почитать!). И с удивлением для себя прочел (интересно, сколько людей тратят свое и чужое время, виесто того, чтобы просто прочитать...?):
...direct recursion is prevented when the RECURSIVE_TRIGGERS database option is set to OFF.
А я-то тут нагородил огорода....
Shame on me.
А Вам огромное спасибо! (И что я сразу не посмотрел...блин...!)
19 ноя 04, 19:44    [1122217]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
vma_mnt
Member

Откуда: Новокузнецк
Сообщений: 602
А не проще создать таблицу телефонов, таблицу абонентов, и таблицу Телефоны+абонент.

Сразу снимаются ограничения на количество параллельных телефонов.
Легко делать выборку как по телефонам (у кого стоят) так и по абонентам.
19 ноя 04, 19:51    [1122236]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
analyser
Member

Откуда:
Сообщений: 20
vma_mnt:
Ни фига не понял... При чем здесь абоненты? Они, конечно, есть, но от наличия/отсутствия абонента телефонный номер не перестает быть спаренным.
Или я опять чего-то не понял?

PS: Все, ухожу домой, теперь только если завтра... Всем, пока!
19 ноя 04, 19:55    [1122250]     Ответить | Цитировать Сообщить модератору
 Re: Синхронизация взаимных ссылок в одной таблице - триггер?  [new]
vma_mnt
Member

Откуда: Новокузнецк
Сообщений: 602
телефонный номер - это ячейка на станции. Эта ячейка может быть подключена к от 1 до N количеству телефонов.

Кому поставили телефон - абонент. Абонент может иметь сколько угодно телефонов, в том числе и параллельные (классическое отношение многие ко многим). Решается созданием таблицы ТЕЛЕФОН+АБОНЕНТ со структурой

КодТелефона
КодАбонента
МестоУстановки.

Все, проще не бывает, телефонный справочник готов.
Легко управляется, развивается (к примеру, если надо хранить историю телефонов, добавляются поля дата установки и дата снятия) и т.д.
19 ноя 04, 20:24    [1122298]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить