Ограничение на поле

← →
}|{yk © (2004-03-31 13:19) [0]

Как сделать ограничение на поле? Оно может принимать или значение из таблицы, или -1 (для дерева). В констрейнт не получается - нельзя подзапросы писать, с вьюхой связать тоже нельзя. А вообще как-то реализовывать можно? Можно конечно триггер написать, но это как-то неправильно...

← →
Курдль © (2004-03-31 13:22) [1]

> Оно может принимать или значение из таблицы, или -1

Т.е. только те значения, что есть в какой-то таблице?
Тогда только триггер! И это самое что ни на есть правильное решение!

← →
Johnmen © (2004-03-31 13:23) [2]

А почему не указан тип БД ?

← →
VLAD-MAL (2004-03-31 13:23) [3]

Я для этих целей юзаю триггеры. Туда что хочешь напихать можно - и контроль, и каскадность, и аудит и проч...
PS С таким вариантом реализации бизнес-логики очень легко работать в ErWin.

← →
}|{yk © (2004-03-31 13:26) [4]

Oracle 8.1.7
Просто each row триггер писать на insert/update в Oracle муторно, просто так вызовет мутацию таблицы. Или я не прав?

← →
Курдль © (2004-03-31 13:30) [5]

> вызовет мутацию таблицы

Вы же не будете писать, а только контролировать! Никакой мутации не будет.
А насчет "муторно" - 3 строки текста :)

← →
Johnmen © (2004-03-31 13:31) [6]

>В констрейнт не получается - нельзя подзапросы писать

А как пробовал, что не получилось ?

>просто так вызовет мутацию таблицы. Или я не прав?

Мутацию можно отключить.

Все верно сказали про триггеры. Более того и констрейнты тоже реализуются через триггеры, системные.

← →
Vlad © (2004-03-31 13:33) [7]

> Курдль © (31.03.04 13:30) [5]

> ! Никакой мутации не будет.

А что, разве мутация возникает только при записи ? :-)

> }|{yk © (31.03.04 13:26) [4]

Вобще, достаточно неплохая практика - делать все изменения в базе через ХП. Это поможет и мутации избежать.

← →
Курдль © (2004-03-31 13:47) [8]

Рабочий пример похожего триггера (оракл 9)
create or replace trigger TSK_GROUP_LOOP before insert or update on PR_TASKS for each row -- when (NEW.TSK_ID_PR is not null) declare vr_IS_EXISTS INTEGER; ex_GROUP_LOOP EXCEPTION; begin select 1 into vr_IS_EXISTS from PR_TASKS T where T.TSK_ID = :NEW.TSK_ID and rownum = 1 start with T.TSK_ID = :NEW.TSK_ID_PR connect by prior T.TSK_ID_PR = T.TSK_ID; if vr_IS_EXISTS is not Null then RAISE ex_GROUP_LOOP; end if; EXCEPTION WHEN ex_GROUP_LOOP THEN Raise_application_error(-20103, "Ошибка: "попытка кольцевой группировки""); WHEN OTHERS then Raise_application_error(-20002, SQLERRM); end TSK_GROUP_LOOP;

← →
}|{yk © (2004-03-31 13:56) [9]

Вобщем вот так решил
CREATE OR REPLACE TRIGGER app_line_report_trg
BEFORE INSERT OR UPDATE
ON app_line_report
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
col NUMBER;
BEGIN
SELECT COUNT (*)
INTO col
FROM line a
WHERE a.mem_id = :NEW.mem_pid;

IF (col = 0)
THEN
IF (:NEW.mem_pid <> -1)
THEN
raise_application_error (-20501, "Íåèçâåñòíûé èäåíòèôèêàòîð!");
END IF;
END IF;
END;

← →
Курдль © (2004-03-31 13:57) [10]

Ну и как, скушало? :)

← →
}|{yk © (2004-03-31 14:04) [11]

А зачем
and rownum = 1 ?
Спасибо за код

← →
}|{yk © (2004-03-31 14:08) [12]

Кстати, а что select 1 from ... будет быстрее для случая определения существования чем select count(*) from ?

← →
}|{yk © (2004-03-31 14:15) [13]

Окончательный триггер таков

CREATE OR REPLACE TRIGGER APP_LINE_REPORT_TRG
BEFORE
INSERT OR UPDATE
ON APP_LINE_REPORT
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
col NUMBER;
vr_is_exists INTEGER;
ex_group_loop EXCEPTION;
ex_unknown_ident EXCEPTION;
BEGIN
SELECT COUNT (*)
INTO col
FROM line a
WHERE a.mem_id = :NEW.mem_pid;

IF (col = 0)
THEN
IF (:NEW.mem_pid <> -1)
THEN
RAISE ex_unknown_ident;
END IF;
END IF;

SELECT 1
INTO vr_is_exists
FROM app_line_report t
WHERE t.mem_id = :NEW.mem_id AND ROWNUM = 1
START WITH t.mem_id = :NEW.mem_pid
CONNECT BY PRIOR t.mem_pid = t.mem_id;

IF vr_is_exists IS NOT NULL
THEN
RAISE ex_group_loop;
END IF;
EXCEPTION
WHEN ex_group_loop
THEN
raise_application_error (-20101,
"Îøèáêà: "ïîïûòêà êîëüöåâîé ãðóïïèðîâêè""
);
WHEN ex_unknown_ident
THEN
raise_application_error (-20102, "Îøèáêà: Íåèçâåñòíûé èäåíòèôèêàòîð!");
END app_line_report_trg;
/

← →
Johnmen © (2004-03-31 14:20) [14]

>}|{yk © (31.03.04 14:08) [12]
>Кстати, а что select 1 from ... будет быстрее для случая >определения существования чем select count(*) from ?

Наиболее быстро
IF EXISTS(SELECT 1 FROM line) THEN

← →
Курдль © (2004-03-31 14:34) [15]

> А зачем
> and rownum = 1 ?

> Кстати, а что select 1 from ... будет быстрее для случая
> определения существования чем select count(*) from ?

Объединяя 2 вопроса.
1. Это чтобы запрос сдох сам по себе после того, как на первой же строке стало ясно, что "тест положительный".
2. count(*) будет упорно пересчитывать все записи
3. Не помню, почему было отказано в применении конструкции if exists .... :)

← →
}|{yk © (2004-03-31 14:44) [16]

2Johnmen © (31.03.04 14:20) [14]
Нет, в триггерах так нельзя
Exists можно использовать только в предложениях SQL

← →
DenK_vrtz © (2004-03-31 14:56) [17]

Чтобы не мучали проблемы мутации можно написать view и триггер к нему

← →
Johnmen © (2004-03-31 15:10) [18]

>}|{yk © (31.03.04 14:44) [16]
>Нет, в триггерах так нельзя
>Exists можно использовать только в предложениях SQL

Точно ? Проверено ? А если ошибка, то какая ?

← →
Курдль © (2004-03-31 15:48) [19]

> Точно ? Проверено ? А если ошибка, то какая ?

В случае замены в коде [8] на if exists ошибка следующая:

"Неверная переменная привязки:
start with T.TSK_ID = :NEW.TSK_ID_PR"

← →
}|{yk © (2004-03-31 15:58) [20]

PLS-00204 function or pseudo-column "exists" may be used inside a SQL statement only

Cause: A pseudocolumn or proscribed function was used in a procedural statement. The SQL pseudocolumns (CURRVAL, LEVEL, NEXTVAL, ROWID, ROWNUM) can be used only in SQL statements. Likewise, certain functions such as DECODE, DUMP, and VSIZE and the SQL group functions (AVG, MIN, MAX, COUNT, SUM, STDDEV, VARIANCE) can be used only in SQL statements.

Action: Remove the pseudocolumn reference or function call from the procedural statement. Or, replace the procedural statement with a SELECT INTO statement; for example, replace:bonus := DECODE(rating, 1, 5000, 2, 2500, ...); with the following statement: SELECT DECODE(rating, 1, 5000, 2, 2500, ...) INTO bonus FROM dual;

← →
Johnmen © (2004-03-31 16:00) [21]

>Курдль © (31.03.04 15:48) [19]

Как выглядит источник ошибки ?

← →
Курдль © (2004-03-31 16:14) [22]

> Johnmen © (31.03.04 16:00) [21]
> >Курдль © (31.03.04 15:48) [19]
>
> Как выглядит источник ошибки ?

ОК! Для установления истины не поленился модифицировать код [8] по Вашим рекомендациям.

create or replace trigger TSK_GROUP_LOOP before insert or update on PR_TASKS for each row -- when (NEW.TSK_ID_PR is not null) declare ex_GROUP_LOOP EXCEPTION; begin if exists (select 1 from PR_TASKS T where T.TSK_ID = :NEW.TSK_ID and rownum = 1 start with T.TSK_ID = :NEW.TSK_ID_PR connect by prior T.TSK_ID_PR = T.TSK_ID; ) then RAISE ex_GROUP_LOOP; end if; EXCEPTION WHEN ex_GROUP_LOOP THEN Raise_application_error(-20103, "Ïîïûòêà "êîëüöåâîé ãðóïïèðîâêè""); WHEN OTHERS then Raise_application_error(-20002, SQLERRM); end TSK_GROUP_LOOP;

PL/SQL Developer отругался на нее, как было указано в [19]

← →
}|{yk © (2004-03-31 16:16) [23]

наверно в 9 можно exists использовать в pl/sql. А в 8 нельзя

← →
Курдль © (2004-03-31 16:29) [24]

> наверно в 9 можно exists использовать в pl/sql. А в 8 нельзя

Не-а! Когда я упростил до нельзя, - отругалося конкретно:
"функция или псевдо-столбец "EXISTS" могут быть использованы только внутри оператора "SELECT"!

← →
Johnmen © (2004-03-31 16:30) [25]

>Курдль © (31.03.04 16:14) [22]
>Для установления истины не поленился ...

:)
Если еще не лень, то стоит сделать проверку корректно. Т.е. в запросе отвязаться от специфики/конкретики. Запрос д.б. прост.
Типа SELECT * FROM Table.

>}|{yk © (31.03.04 16:16) [23]

Странно... Но возможно...

← →
Sergey13 © (2004-03-31 16:47) [26]

2}|{yk © (31.03.04 13:19)
А если все по другому, попроще. 8-)
Вместо -1 NULL или в ту таблицу, на которую ссылается -1 как служебную строку добавить? И включай простой констрейнт.

← →
roottim (2004-03-31 17:02) [27]

2Sergey13 © (31.03.04 16:47) [26]
да речь здесь видимо не о внешнем констрейнте а о проверке дерево..
1. процедурой проще и надежней..
2. exists это предикат sql а не функция и неотделим от него
3. приведу пример , но проверять не буду для триггера перед_вставкой begin select id into :NEW.parent_id from my_table t where t.id = :NEW.parent_id; -- если ид такого нет.. получите екскептион.. NO_DATA_FOUND -- если есть то есть :) end;

← →
Johnmen © (2004-03-31 17:08) [28]

>2. exists это предикат sql а не функция и неотделим от него

Это в Оракле он так называется ?

← →
Sergey13 © (2004-03-31 17:09) [29]

2roottim (31.03.04 17:02) [27]
Я понял. Мое "или в ту таблицу, на которую ссылается -1 как служебную строку добавить" следует читать как "или в таблицу -1 как служебную строку добавить". 8-)
А вот про проще/надежнее - почему это? Стандартный констр всяко надежнее и шустрее, ИМХО. К тому же иногда процедуры имеют свойство выключаться незаметно. 8-(

Чем меньше серверу мы пишем
Тем меньше сервер тормозит.
(с) Моя вольная трактовка классики 8-)

← →
Курдль © (2004-03-31 17:15) [30]

> Вместо -1 NULL или в ту таблицу, на которую ссылается -1
> как служебную строку добавить? И включай простой констрейнт.

Это позволит лишь отказаться от проверки "на -1", но не от проверки "есть ли такое значение в таблице". А в последнем случае придется таки писать триггер.
Процедуры, подменяющие insert, update, delete - это опасная стратегия. Ведь некто может и не знать/забыть/не обратить внимания на то, что в таблицу разрешено вносить записи только через процедуру и долбануть insert. Зато триггер всегда начеку, как пограничная собака :)

2Курдль © (31.03.04 17:15) [30]
>Это позволит лишь отказаться от проверки "на -1", но не от проверки "есть ли такое значение в таблице".
Почему это? Ставишь форинкей с ПарентИД на ИД и фсе.

У "заявителя" не прописано, что это форинкей! (Тогда б и ежу было понятно, что оракл не даст напортачить и без всяких "-1").

2Курдль © (31.03.04 17:28) [32]
А с чего бы он всю бодягу поднял (это я как вышедший из отпуска телепат говорю?
Да и обычно так деревья в оракле растут.
8-)

> Да и обычно так деревья в оракле растут.

В оракле деревья не так растут, а уходят вглубь корнями аж до ER-модели! А такие приколы, как форинкей еще там отлавливаются! :)

2Курдль © (31.03.04 17:37) [34]
>а уходят вглубь корнями аж до ER-модели
Эк ты как глубоко копнул. Лопату не сломал? 8-)

>А такие приколы, как форинкей еще там отлавливаются! :)
Это проца по сравнению с форином прикол. 8-)

> >а уходят вглубь корнями аж до ER-модели
> Эк ты как глубоко копнул. Лопату не сломал? 8-)

8=()
Я уж давно меньше, чем экскаваторами не практикую.
Рисуешь неделю какую-то хреноплетень из прямоугольничков и стрелочек, а потом нажатием 2-х кнопок (создать физ.модель/ генерировать БД) получаешь ораклевую базу со всеми ее деревьями! :)

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

2}|{yk © (31.03.04 18:23) [38]
А по делу то, на чем остановился, на процедуре/тригере или на моем правильном предложении? 8-)

просто я фиктивный элемент в справочник статей добавлять не могу
потому нужна проверка кроме того для дерева нужна проверка на циклы

2}|{yk © (31.03.04 18:41) [40]
Опиши структуру таблиц и задачу, а то запутал ты всех, ИМХО.
Завтра продолжим, надеюсь.
Я домой.

← →
roottim (2004-03-31 20:12) [42]

2jonmen
хорошо переделаю exists - sql оператор а не ф-я

2жук
а что мешает сделать корень = null?
тогда в этом случае можно просто сделать констрейнт в виде вненего ключа самого на себя
ALTER TABLE MY_TABLE
ADD CONSTRAINT MY_TABLE_TREE FOREIGN KEY(PARENT_ID)
REFERENCES MY_TABLE(ID) ON DELETE CASCADE

Ограничение на поле Найти похожие ветки