Статья по триггерам

Модератор: kdv

eddoc
Сообщения: 25
Зарегистрирован: 20 янв 2008, 00:40

Статья по триггерам

Сообщение eddoc » 07 апр 2008, 13:39

Давно порывался написать, а тут музыкой навеяло :)

Дмитрий, я только недавно столкнулся с понятиями триггер, селективность индекса и т.д. Признаюсь, масштаб и полезность доки на айбейзру впечатляет. Вот только практических примеров "правильной" реализации табличных отношений в триггерах я не нашел (впрочем, как и по эмуляции check'ов, которые по словам той же Х.Борри "на деле оказываются "беззубым зверем", или прав доступа на просмотр/изменение/удаление столбца/записи/таблицы). Ни тут, ни на форуме SQL. Т.е., у Х.Борри в книжке есть примеры поддержки ограничений "декларативной" целостности ПК-ФК на уровне триггера (да еще и в контексте транзакции), а также пример "эмуляции" такого соотношения в lookup-таблице (по-моему, об этом спрашивал автор выше приведенного топика). Правда, ее объяснения "что-куда-вставляется-и-как-оно-должно-работать" не всегда очевидны. А вот на Вашем сайте об этом ни слова.

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

P.S. Извиняюсь, что "наследил" здесь, а не на скулру. Этот обращение личное и тут более уместно, имхо. :)

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Статья по триггерам

Сообщение WildSery » 07 апр 2008, 14:08

eddoc писал(а):Вот только практических примеров "правильной" реализации табличных отношений в триггерах я не нашел (впрочем, как и по эмуляции check'ов, которые по словам той же Х.Борри "на деле оказываются "беззубым зверем", или прав доступа на просмотр/изменение/удаление столбца/записи/таблицы).
А знаешь, почему?
Потому что нет их, таких примеров. И быть не может.
Ни констрэйнты, ни чеки сэмулированы триггерами быть не могут. За исключением случая монопольной работы.

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 07 апр 2008, 16:13

практических примеров "правильной" реализации
табличных отношений в триггерах я не нашел

это невозможно. триггер "видит" только в контексте "наружной" транзакции, поэтому не может обеспечить межтабличную целостность любого вида.
или прав доступа на просмотр/изменение/удаление
триггерами такое делать неудобно. например
www.ibase.ru/devinfo/treedb2.htm
А вот на Вашем сайте об этом ни слова.
потому и ни слова.

Мне кажется, что могут быть интересными примеры multi-acrion триггеров, причем реальные примеры. Но и это тоже скучновато :)

eddoc
Сообщения: 25
Зарегистрирован: 20 янв 2008, 00:40

Re: Статья по триггерам

Сообщение eddoc » 07 апр 2008, 23:17

WildSery писал(а):Потому что нет их, таких примеров. И быть не может.
Ни констрэйнты, ни чеки сэмулированы триггерами быть не могут. За исключением случая монопольной работы.
Значит, Борри лжет? Или я где-то в тексте пропустил оговорку "за исключением случаев монопольной работы"? :)

eddoc
Сообщения: 25
Зарегистрирован: 20 янв 2008, 00:40

Сообщение eddoc » 07 апр 2008, 23:27

kdv писал(а):это невозможно. триггер "видит" только в контексте "наружной" транзакции, поэтому не может обеспечить межтабличную целостность любого вида.
Честно говоря, я в замешательстве. Как же тогда эмулировать констрейнт в справочниках. Может, пример покажете.
kdv писал(а):
или прав доступа на просмотр/изменение/удаление
триггерами такое делать неудобно. например
www.ibase.ru/devinfo/treedb2.htm
Как же так? А приводимый у Борри же пример:

Код: Выделить всё

ACTIVE BEFORE UPDATE OR DELETE POSITION 0
AS
begin
  if (CURRENT_USER <> 'SYSDBA') then
    EXCEPTION NO_AUTHORITY;
end
Громоздко, конечно, но ведь важен принцип.

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Статья по триггерам

Сообщение WildSery » 08 апр 2008, 00:49

eddoc писал(а):Значит, Борри лжет? Или я где-то в тексте пропустил оговорку "за исключением случаев монопольной работы"? :)
Я Хелен читал по диагонали, да и то не всё. Потому не буду обсуждать её тезисы.
Однако, я могу обсудить реальную работу версионной БД. Ссылочную целостность триггерами контролировать нельзя. Как ни крути.

eddoc
Сообщения: 25
Зарегистрирован: 20 янв 2008, 00:40

Re: Статья по триггерам

Сообщение eddoc » 08 апр 2008, 09:01

WildSery писал(а):Я Хелен читал по диагонали, да и то не всё. Потому не буду обсуждать её тезисы.
Однако, я могу обсудить реальную работу версионной БД. Ссылочную целостность триггерами контролировать нельзя. Как ни крути.
Хорошо. Если позволите

Код: Выделить всё

CREATE TABLE LOOKUP (
    UQ_ID       SMALLINT NOT NULL,
    VALUE1      VARCHAR(30) CHARACTER SET WIN1251 NOT NULL,
    VALUE2      CHAR(2) CHARACTER SET WIN1251 NOT NULL,
    START_DATE  TIMESTAMP NOT NULL,
    END_DATE    TIMESTAMP NOT NULL
);

CREATE TABLE REQUESTOR (
    ID            INTEGER NOT NULL,
    LOOKUP_ID     SMALLINT,
    DATA          VARCHAR(20) CHARACTER SET WIN1251,
    TRANSAC_DATE  TIMESTAMP NOT NULL
);
ALTER TABLE REQUESTOR ADD CONSTRAINT PK_REQUESTOR PRIMARY KEY (ID);

/* сам триггер*/
CREATE OR ALTER TRIGGER REQUESTOR_BIU0 FOR REQUESTOR
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
declare variable LOOKUP_NUM SMALLINT;
declare variable NEED_CHEK SMALLINT = 0;
begin
  if (INSERTING AND NEW.LOOKUP_ID IS NOT NULL) then
   NEED_CHEK = 1;
   if (UPDATING) then
      if ((OLD.LOOKUP_ID IS NULL AND NEW.LOOKUP_ID IS NOT NULL) OR
         (OLD.LOOKUP_ID IS NOT NULL AND NEW.LOOKUP_ID <> OLD.LOOKUP_ID )) then
         NEED_CHEK = 1;
         if (NEED_CHEK = 1) then
            BEGIN
               SELECT L1.UQ_ID FROM LOOKUP L1
                  WHERE L1.START_DATE <= CAST(NEW.TRANSAC_DATE AS DATE)
                  AND L1.END_DATE >= CAST(NEW.TRANSAC_DATE AS DATE)
                  AND L1.VALUE2 = (SELECT L2.VALUE2 FROM LOOKUP L2
                     WHERE L2.UQ_ID = NEW.LOOKUP_ID)
               INTO :LOOKUP_NUM;
               NEW.LOOKUP_ID = LOOKUP_NUM;
            END
end;
Единственное, что я не понял. Если вставлять (скажем, в другом триггере) значения CURRENT_TIMESTAMP в поля START_DATE и END_DATE в начале и конце транзакции соответственно, то:
1) их значение должно различаться хотя бы тысячными долями секунды. Переменная CURRENT_TIMESTAMP их на самом деле возвращает? В IBE, увы, таблички их не отображают :( ;
2) почему TRANSAC_DATE приводится к типу DATE - ведь коню понятно, что транзакция не будет длится больше суток(!) :)

Собственно, вопрос: можно этот код использовать в нативном виде или сие есть (как тут часто звучит) проктологическая направленность изысканий?

P.S. Поймите меня правильно, я не пытаюсь поймать Вас на противоречиях. Я хочу разобраться досконально. Издержки профессии :)

Merlin
Динозавр IB/FB
Сообщения: 1502
Зарегистрирован: 27 окт 2004, 11:44

Сообщение Merlin » 08 апр 2008, 12:09

Уууу, как всё запущено... (С) Тут не с кода надо начинать. С психотерапии. Насчёт целей, средства дело вторичное.

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Сообщение WildSery » 08 апр 2008, 12:17

Похоже на выдранный из контекста пример. Условия применения не ясны.
Не надо только цитировать целиком книжку.

Я тебе приведу два простых примера.
1. Подключусь я к этой твоей БД экспертом, и сделаю DELETE FROM LOOKUP. Что, ссылочная целостность сохранилась?
Скорее всего, в книжке есть определённые доп. триггеры на этот случай. Не важно, см. следующий пример.
2. Подключусь экспертом, сделаю DELETE FROM LOOKUP и не буду коммитить транзакцию. Все триггера на ON DELETE уже отработали.
В это время либо кто-то, работающий с БД, или я сам из другого коннекта, навставляю новых записей в REQUESTOR.
И наконец, коммит удаления. Что будет, как ты думаешь?

ЗЫ: Что-то добрый я сегодня...

eddoc
Сообщения: 25
Зарегистрирован: 20 янв 2008, 00:40

Сообщение eddoc » 08 апр 2008, 15:10

Merlin писал(а):Уууу, как всё запущено... (С) Тут не с кода надо начинать. С психотерапии. Насчёт целей, средства дело вторичное.
"Хамите, парниша" (с)

eddoc
Сообщения: 25
Зарегистрирован: 20 янв 2008, 00:40

Сообщение eddoc » 08 апр 2008, 15:33

WildSery писал(а):Похоже на выдранный из контекста пример. Условия применения не ясны.
Не надо только цитировать целиком книжку.
Естессно, выдранный. Иначе, только книжку целиком :)
WildSery писал(а):Что, ссылочная целостность сохранилась?
Скорее всего, в книжке есть определённые доп. триггеры на этот случай.
Абсолютно верно. Один на наличие ключа соответствия, другой - на наличие "подчиненных" записей.
WildSery писал(а):2. Что будет, как ты думаешь?
Могу порассуждать, с того момента, когда "кто-то другой?" А ты не сочти за труд, поправь меня (желательно с подробными объяснениями, я ведь сюда за знаниями пришел, а не пиписьками меряться :)

1. Если "кто-то другой" закоммитил инсерты в REQUESTOR перед твоим DELETE FROM LOOKUP, то ты получишь эксцепшн, что в REQUESTOR есть "подчиненные" записи.

2. Если ты оказался с коммитом первым, то уже "кто-то другой" при своем коммите получает другой эксепшн, что нет ключа соответствия в LOOKUP (уговор не цитировать книгу в силе, надеюсь? Заглянешь туда сам? Или все-таки скопировать сюда?)

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Сообщение WildSery » 08 апр 2008, 16:13

eddoc писал(а):А ты не сочти за труд, поправь меня (желательно с подробными объяснениями, я ведь сюда за знаниями пришел, а не пиписьками меряться :)
Никто с тобой меряться не собирается. И здесь не курсы по изучению азов IB/FB.
eddoc писал(а):1. Если "кто-то другой" закоммитил инсерты в REQUESTOR перед твоим DELETE FROM LOOKUP, то ты получишь эксцепшн, что в REQUESTOR есть "подчиненные" записи.
В каком месте получу? Я уже удалил. Только ещё не подтвердил этого.
Мосье точно понимает, что такое версионность?

Мне даже интересно стало, о чём там говорит Хелен, номер страницы дай.

eddoc
Сообщения: 25
Зарегистрирован: 20 янв 2008, 00:40

Сообщение eddoc » 09 апр 2008, 00:33

WildSery писал(а):Никто с тобой меряться не собирается. И здесь не курсы по изучению азов IB/FB.
А я этого и не требую. Я всего прошу участников форума помочь с неясными вопросами, ответы на которые я не нашел (или не уяснил) из прочитаной литературы. А вместо этого получаю хамские замечания, в лучшем случае менторскую отповедь, из коей следует, что программирование в FB - удел избранных. Самому не грустно?
WildSery писал(а):В каком месте получу? Я уже удалил. Только ещё не подтвердил этого.
Мосье точно понимает, что такое версионность?
Мне даже интересно стало, о чём там говорит Хелен, номер страницы дай.
"Слушайте, Шура, если уж вы окончательно перешли на французский язык, то называйте меня не мосье, а ситуайен, что значит - гражданин."(ц)
Извиняюсь за спешку с ответом. Согласен, фигню сморозил. :) "Сироты" появяться. На этот счет Хелен рекомендует разрешать удаление из LOOKUP только главбуху ))))

Не сам же я это придумал. Пожалуйста, стр 681-685.

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Сообщение WildSery » 09 апр 2008, 11:20

eddoc писал(а):удаление из LOOKUP только главбуху
Вооот. Сериализация работы. Сиречь монопольная работа. "Псевдомногопользовательская", точнее.
Именно поэтому и звучит изначальное утверждение - триггерами целостность контролировать нельзя. "Поддерживать можно, контролировать нельзя", я бы так выразился :)

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 09 апр 2008, 12:40

поскольку гражданин eddoc предпочитает трясти, а не читать, то я пуляю сюда цитату из хелпа IBAnalyst:

8. Что делать, если "плохой" индекс построен по FK?


Действительно, из предыдущего пункта ясно, что от "плохих" индексов стоит избавиться. Однако, если такой индекс построен по Foreign Key, то удалить его можно только вместе с Foreign Key. А это приведет к отсутствию контроля целостности между двумя таблицами.

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

Поэтому, при замене FK триггерным контролем целостности должны быть соблюдены условия:

1. записи из справочника никогда не удаляются, или удаляются в монопольном режиме (см. транзакции reserving в документе)
2. столбец первичного ключа справочника никогда не модифицируется (можно создать такой запрет триггером before update).

При соблюдении этих условий FK можно удалить, и разумеется, строить индекс по этому же столбцу не нужно.

eddoc
Сообщения: 25
Зарегистрирован: 20 янв 2008, 00:40

Сообщение eddoc » 09 апр 2008, 13:07

Все, ребят, сдаюсь! Посыпаю голову пеплом, предварительно разбив об нее кирпич. :)

пошел достраивать ФК-констрейнты, в т.ч. и на лукапы из 2-х записей :))

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Сообщение WildSery » 09 апр 2008, 14:31

eddoc писал(а):пошел достраивать ФК-констрейнты, в т.ч. и на лукапы из 2-х записей
Ну что за дитя... Всё бы ёрничать.
У тебя что, лукапы из 2-х записей то и дело удаляются и меняются ID у них?

CyberMax
Заслуженный разработчик
Сообщения: 638
Зарегистрирован: 31 янв 2006, 09:05

Сообщение CyberMax » 09 апр 2008, 14:51

Рассмотрим ситуацию с более высокого уровня.
В 1С, наряду с типом "Справочник", есть тип "Перечисление", который есть небольшой набор постоянных или почти постоянных строковых значений (например, пол человека) и является частным случаем справочника. По большому счету, таблица с двумя полями ID и NAME фактически и является таким перечислением.
Одно из свойств перечисления - мы в любой момент знаем, сколько в нем значений, а значит, и селективность индекса любого FK на него (допустим, что используются все значения этого перечисления). Как следствие того, что набор значений небольшой, все FK на таблицы перечислений изначально плохоселективны - такова их природа.
Вывод: для индексов FK на таблицу с перечислением надо хранить не селективность, а гистограмму распределения значений (ограничив ее, например, 255 значениями). Если я не ошибаюсь, запрос на реализацию этой фичи есть в трекере, только когда это будет реализовано и будет ли вообще реализовано, скажут только разработчики.

eddoc
Сообщения: 25
Зарегистрирован: 20 янв 2008, 00:40

Сообщение eddoc » 09 апр 2008, 17:44

WildSery писал(а):Ну что за дитя... Всё бы ёрничать.
У тебя что, лукапы из 2-х записей то и дело удаляются и меняются ID у них?
Ну уж, даже я знаю, что менять ID - моветон! :)
Был один лукап из 2-3х записей (клиники, в которой у меня идет прием того или иного пациента). Я после тутошних баталий прикинул, что на практике от связки клиника-пациент (lookup-requestor) нет никакой пользы. Потому сделал лукап обычной табличкой.
WildSery писал(а):Ну что за дитя... Всё бы ёрничать.
Не обращай внимания. Это я пытаюсь лицо сохранить :)

Спасибо всем за участие и науку.

Attid
Спец
Сообщения: 377
Зарегистрирован: 14 ноя 2006, 09:58

Re: Статья по триггерам

Сообщение Attid » 10 апр 2008, 00:35

eddoc писал(а):В IBE, увы, таблички их не отображают :(
если включить в настройках то будет =)

Ответить