Триггер в FireBird

← →
Knight © (2012-04-30 17:28) [0]

Подскажите как триггером выставить статус родительской записи при изменении статуса одной из дочерних?

Статусов всего 4

Статус родительского :
1 - если статус 1 у всех дочерних
2 - если статус хотя бы у одного дочернего равен 2
3 - если статус хотя бы у одного дочернего равен 3 или 4
4 - если статус у всех дочерних равен 4

← →
Knight © (2012-04-30 17:36) [1]

Необходимые поля
fID - идентификатор
fParentID - родительский идентификатор (для дерева)
fStatus - статус (который надо менять вверх по дереву по вышеуказанным правилам)

← →
Knight © (2012-04-30 17:43) [2]

CREATE TRIGGER AU_NOTE FOR tNote ACTIVE AFTER INSERT OR UPDATE OR DELETE AS BEGIN ??? IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=... AND fStatus<>1)) ??? UPDATE tNote SET fStatus=1 WHERE fID=fParentID ??? ELSE ??? END

← →
Медвежонок Пятачок © (2012-04-30 19:52) [3]

я бы вообще отказался от статуса для родительской. нафиг надо париться с хранением, если оно влет вычисляется, причем недорого

← →
Knight © (2012-04-30 20:21) [4]

> я бы вообще отказался от статуса для родительской

В смысле не дорого? Это чтобы вывести список, например, корневых записей со статусами, надо прогнать все зависимые узлы до конечных "листьев". По мне проще при изменении статуса у "листа" триггером скорректировать статус у родительских и использовать его при просмотре списка.

← →
asail © (2012-04-30 20:28) [5]

> Knight © (30.04.12 17:28)

Вот какой должен быть статус у родительской записи если в текущей дочерней записи меняем статус, скажем с 2 на 4? 3 или 4?
Не пробежавшись по всем остальным дочерним записям. А если пробегаться, то лучше все ж
> Медвежонок Пятачок © (30.04.12 19:52) [3]

← →
Knight © (2012-04-30 21:02) [6]

> А если пробегаться, то лучше все ж

Так лучше пробежаться один раз при смене статуса, чем каждый раз при выводе списка.

← →
Knight © (2012-04-30 21:13) [7]

... или я чего-то не подопонимаю.

Чтобы сменить статус у родительской надо подняться по дереву вверх

1 - запись 1.1 - подзапись 2.1 - подзапись 2.2 - подзапись 3.1 - подзапись 3.2 - подзапись (*) 2.3 - подзапись 1.2 - подзапись 1.3 - подзапись

Если изменился 3.2, то надо:
1) проверить дочерние 2.2
2) проверить дочерние 1.1
3) проверить дочерние 1

А если проверять статусы налету, то надо делать прогон по всему низлежащему дереву.

← →
Knight © (2012-04-30 21:21) [8]

А триггерами... при создании, изменении, удалении записи срабатывает триггер, который проверяет статус родителя, если статус изменился, то статус изменяется, при этом срабатывает триггер на изменение родителя... и так по цепочке, все на автомате и красиво.

← →
Knight © (2012-04-30 22:06) [9]

Во что получилось... вроде работает

Query.SQL.Text:="CREATE TRIGGER AU_NOTE FOR tNote " +" ACTIVE AFTER INSERT OR UPDATE OR DELETE " +"AS " +"BEGIN " +" IF (New.fParentID > 0) THEN " +" BEGIN " +" IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=New.fParentID AND fStatus<>1))) THEN " +" UPDATE tNote SET fStatus=1 WHERE fID=New.fParentID; " +" ELSE " +" IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=New.fParentID AND fStatus<>4))) THEN " +" UPDATE tNote SET fStatus=4 WHERE fID=New.fParentID; " +" ELSE " +" IF (EXISTS(SELECT 1 FROM tNote WHERE fParentID=New.fParentID AND (fStatus=3 OR fStatus=4))) THEN " +" UPDATE tNote SET fStatus=3 WHERE fID=New.fParentID; " +" ELSE " +" UPDATE tNote SET fStatus=2 WHERE fID=New.fParentID; " +" END " +"END";

← →
Knight © (2012-04-30 22:11) [10]

Почти... работает :)
Удаление подзаписи не отрабатывает.

← →
sniknik © (2012-04-30 22:22) [11]

> Если изменился 3.2, то надо:
> 1) проверить дочерние 2.2
> 2) проверить дочерние 1.1
> 3) проверить дочерние 1
>
> А если проверять статусы налету, то надо делать прогон по всему низлежащему дереву.

допустим статусы везде = 1 т.е. родителю ставим 1, после меняется (или до этого) 3.2 и у него статус 2... т.е. по условию родителю нужно поставить 2.
как быть? противоречие в твоей же логике условий и проверки статусов.
проверять все одно нужно все, чтобы соответствовало [0].

← →
sniknik © (2012-04-30 22:23) [12]

> после меняется (или до этого) 3.2
вернее 3.1, не исправил копипаст выше.

← →
Медвежонок Пятачок © (2012-04-30 22:27) [13]

3 - если статус хотя бы у одного дочернего равен 3 или 4
4 - если статус у всех дочерних равен 4

статус 4 - полный прогон. есть только дети со статусом 4 и других никаких нет.

статус 3 - полный прогон снова. допустим есть один чайлд с 4 или 3. Но нам еще надо убедиться, что у нас не случай, когда все дети - четверки (тогда не 3 а 4)

← →
Knight © (2012-04-30 22:54) [14]

> родителю ставим 1,

fStatus - статус (который надо менять вверх по дереву по вышеуказанным правилам)

Забыл явно прописать, что статус менять можно только у записей у которых нет подчинённых. Т.е. у "листьев". У узлов "родителей" статус выставляется на основании низлежащих.

← →
Knight © (2012-04-30 23:01) [15]

По [9]

Первый отбор хоть одной дочерней записи со статусом не равным 1 => если таких нет, то статус родителя 1 Иначе Второй отбор хоть одной дочерней записи со статусом не равным 4 => если таких нет, то статус родителя 4 Иначе Третий отбор что есть 3 или 4 => найдены, то статус родителя 3 Иначе статус родителя 2

← →
Knight © (2012-04-30 23:14) [16]

Что тут не так? Вылетает с ошибкой "Invalid request BLR at offset 108 undefinied message number"

Query.SQL.Text:="CREATE TRIGGER AU_NOTE FOR tNote " +" ACTIVE AFTER INSERT OR UPDATE OR DELETE " +"AS " +"DECLARE VARIABLE NOTE_ID INTEGER; " +"BEGIN " +" IF (DELETING) THEN " +" NOTE_ID=Old.fParentID; " +" ELSE " +" NOTE_ID=New.fParentID; " +" IF (NOTE_ID>0) THEN " +" BEGIN " +" IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=:NOTE_ID AND fStatus<>1))) THEN " +" UPDATE tNote SET fStatus=1 WHERE fID=:NOTE_ID; " +" ELSE " +" IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=:NOTE_ID AND fStatus<>4))) THEN " +" UPDATE tNote SET fStatus=4 WHERE fID=:NOTE_ID; " +" ELSE " +" IF (EXISTS(SELECT 1 FROM tNote WHERE fParentID=:NOTE_ID AND (fStatus=3 OR fStatus=4))) THEN " +" UPDATE tNote SET fStatus=3 WHERE fID=:NOTE_ID; " +" ELSE " +" UPDATE tNote SET fStatus=2 WHERE fID=:NOTE_ID; " +" END " +"END";

← →
Knight © (2012-04-30 23:19) [17]

Только переменную добавил... сервер embedded мож в этом косюк?

← →
Knight © (2012-04-30 23:26) [18]

Переделал без переменной, ошибка пропала. Удаление стало отрабатываться.

← →
Knight © (2012-04-30 23:41) [19]

Вроде все работает более-менее корректно... вот что получилось

CREATE TRIGGER AU_NOTE FOR tNote ACTIVE AFTER INSERT OR UPDATE OR DELETE AS BEGIN IF (DELETING) THEN BEGIN IF (Old.fParentID>0) THEN BEGIN IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=Old.fParentID AND fStatus<>1))) THEN UPDATE tNote SET fStatus=1 WHERE fID=Old.fParentID; ELSE IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=Old.fParentID AND fStatus<>4))) THEN UPDATE tNote SET fStatus=4 WHERE fID=Old.fParentID; ELSE IF (EXISTS(SELECT 1 FROM tNote WHERE fParentID=Old.fParentID AND (fStatus=3 OR fStatus=4))) THEN UPDATE tNote SET fStatus=3 WHERE fID=Old.fParentID; ELSE UPDATE tNote SET fStatus=2 WHERE fID=Old.fParentID; END END ELSE BEGIN IF (New.fParentID>0) THEN BEGIN IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=New.fParentID AND fStatus<>1))) THEN UPDATE tNote SET fStatus=1 WHERE fID=New.fParentID; ELSE IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=New.fParentID AND fStatus<>4))) THEN UPDATE tNote SET fStatus=4 WHERE fID=New.fParentID; ELSE IF (EXISTS(SELECT 1 FROM tNote WHERE fParentID=New.fParentID AND (fStatus=3 OR fStatus=4))) THEN UPDATE tNote SET fStatus=3 WHERE fID=New.fParentID; ELSE UPDATE tNote SET fStatus=2 WHERE fID=New.fParentID; END END END

← →
sniknik © (2012-05-01 00:08) [20]

> Забыл явно прописать, что статус менять можно только у записей у которых нет подчинённых. Т.е. у "листьев". У узлов "родителей" статус выставляется на основании низлежащих.
и что это меняет? вся твоя "цепочка" только отвлечение внимания, если важны только листья... ну допустим, есть -
1 - запись 1.1 - подзапись 2.1 - подзапись 2.2 - подзапись 3.1 - подзапись = 2 3.2 - подзапись = 2 2.3 - подзапись 1.2 - подзапись 1.3 - подзапись

делаем изменение 3.2 на 1, по твоей логике родители получат везде 1, хотя
> 2 - если статус хотя бы у одного дочернего равен 2
и оно таки одно равно.

не?

← →
sniknik © (2012-05-01 00:10) [21]

вообще, вот решение
Медвежонок Пятачок © (30.04.12 19:52) [3]
> я бы вообще отказался от статуса для родительской. нафиг надо париться с хранением, если оно влет вычисляется, причем недорого
но свой "велосипед" понятно милее...

← →
Knight © (2012-05-01 00:31) [22]

> делаем изменение 3.2 на 1, по твоей логике родители получат
> везде 1

род. доч.1 доч.2 доч.3 доч.4 род. | доч. 1 1+1+1+1 2 1+2+1+2 3 1+3+2+1 4 4+4+4+4

> я бы вообще отказался от статуса для родительской

Кажется понял почему так проще... это если дерево выводить в TreeView, тогда можно рассчитать и пользовать.

Но у меня выводится в ListView .

← →
Knight © (2012-05-01 00:38) [23]

> вся твоя "цепочка" только отвлечение внимания

Органайзер пишу для диплома. Любая запись - это задача или подзадача, статусы - степень выполнения без процентного выражения (4 статуса - новая, приступил, что-то сделал, выполнил). Задача в статусе 1 и имеет 5 подзадач - значит ни за одну подзадачу ещё не принимался. Если в статусе 3 - значит что-то в подзадачах уже поделано и т.д.

← →
sniknik © (2012-05-01 01:19) [24]

> Но у меня выводится в ListView .
и что?

ну вот меняем/тригер срабатывает на второй "дочке", меняется на 1 ???
род. доч.1 = 1 доч.2 = 2 доч.3 = 1 доч.3.1 = 2 доч.4 = 1
у тебя поставит 1 родителю, а по [0] должно быть 2. и т.д. все одно нужен "пробег" по всей ветке.

← →
sniknik © (2012-05-01 01:30) [25]

> ну вот меняем/тригер
а, понятно, в доч.3 будет 2 когда ее дочке присвоится 2-ка.

← →
Труп Васи Доброго © (2012-05-02 12:05) [26]

> все одно нужен "пробег" по всей ветке.

Зачем куда-то бегать?
Получаем следующую логику: статусы бывают только 1..4
1) запись без статуса т.е. (NULL) невозможна, она при создании дефолтом ставится = 1, отсюда следует, что при создании новой записи проверять и изменять статус родителя вообще не требуется.
2) При изменении статуса записи надо создать триггер BEFORE UPDATE вычисляющий новый статус записи на основе статусов её дочек.

CREATE TRIGGER BU_NOTE FOR tNote
ACTIVE BEFORE UPDATE
AS
DECLARE VARIABLE Vstatus INTEGER;
DECLARE VARIABLE Vcount INTEGER;
DECLARE VARIABLE Vmax INTEGER;
BEGIN

SELECT COALESCE(MAX (fStatus),1), COALESCE(SUM(fStatus),1), COALESCE(COUNT(fStatus),1) FROM tNote WHERE (fParentID=New.fID) GROUP BY fParentID INTO :VStatus, :Vsum, :Vcount;
IF (:VStatus<>:Vsum/:Vcount) THEN
BEGIN
IF (:Vstatus>=3) Then Vstatus = 3
ELSE Vstatus = 2;
END
New.fStatus = :Vstatus;
END

3) Нужен тригер AFTER UPDATE/DELETE изменяющий статус родительской записи.
CREATE TRIGGER AUD_NOTE FOR tNote
ACTIVE AFTER UPDATE OR DELETE
AS
DECLARE VARIABLE Vstatus INTEGER;

BEGIN
IF (DELETING) THEN Vstatus=1 ELSE Vstatus = NEW. fStatus;
UPDATE tNote SET fStatus=:VStatus WHERE fID=Old.fParentID;
END

Работать будет безотказно (должно работать). За синтаксис не ручаюсь ибо не кодил года 3.

← →
Труп Васи Доброго © (2012-05-02 12:08) [27]

Да, во втором триггере можно сначала вставить проверку на наличие "родителя", но и без неё будет работать, просто ему обновлять будет некого.

← →
oldman © (2012-05-02 12:32) [28]

> Статус родительского :
> 1 - если статус 1 у всех дочерних
> 2 - если статус хотя бы у одного дочернего равен 2
> 3 - если статус хотя бы у одного дочернего равен 3 или 4
> 4 - если статус у всех дочерних равен 4

Надо хранить у родителя не статус, а количество дочерних веток с их статусами, например
3-2-5-2 (3 единицы, 2 двойки, 5 троек, 2 четверки, всего 12 дочек)
При изменении дочернего статуса меняем запись родителя (запомнив статус дочери до и после изменения/удаления/добавления)
Статус родителя высчитывается просто и легко

← →
Труп Васи Доброго © (2012-05-02 12:49) [29]

> Надо хранить у родителя не статус, а количество дочерних
> веток

Категорически не согласен, ибо это подразумевает наличие разницы между детьми и родителями, по условию все записи равнозначные, а у тебя придётся вводить какой-то особый признак родительскости + ещё доп. поля для количества дочек и их статусов.

← →
Knight © (2012-05-02 12:50) [30]

Надо всё обдумать ещё раз... подскажите пока, почему не работают переменные в Firebird 1.5.5... Точнее, так, объявление переменной к сбою создания процедуры не приводит, присвоение, тоже, если делаешь

update ... set поле=переменная
тоже прожёвывает, а вот если использовать в

WHERE поле=:переменная
вылетает ошибка "Invalid request BLR at offset 108 undefinied message number"

Это так и должно быть?

← →
Труп Васи Доброго © (2012-05-02 13:16) [31]

> WHERE поле=:переменная

Сделай WHERE поле=переменная, возможно счастье близко :)

← →
Knight © (2012-05-02 13:20) [32]

Тогда говорит, что поле с именем переменная не найдено

← →
Труп Васи Доброго © (2012-05-02 13:30) [33]

Ничего не понятно. Ты в КГБ работаешь? Или твоя процедура деньги из банка на твой счёт переводит? Нет? Тогда не партизань, а дай сюда процедуру. Ты её в IBExperte прогонял пошагово? За значениями переменных следил? Что там творится?

← →
Knight © (2012-05-02 13:33) [34]

Query.SQL.Text:="CREATE TRIGGER AU_NOTE FOR tNote " +" ACTIVE AFTER INSERT OR UPDATE OR DELETE " +"AS " +"DECLARE variable vID INTEGER; " +"BEGIN " +" IF (DELETING) THEN " +" vID=Old.fParentID; " +" ELSE " +" vID=New.fParentID; " +" IF (vID>0) THEN " +" BEGIN " +" IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=:vID AND fStatus<>1))) THEN " +" UPDATE tNote SET fStatus=1 WHERE fID=:vID; " +" ELSE " +" IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=:vID AND fStatus<>4))) THEN " +" UPDATE tNote SET fStatus=4 WHERE fID=:vID; " +" ELSE " +" IF (EXISTS(SELECT 1 FROM tNote WHERE fParentID=:vID AND (fStatus=3 OR fStatus=4))) THEN " +" UPDATE tNote SET fStatus=3 WHERE fID=:vID; " +" ELSE " +" UPDATE tNote SET fStatus=2 WHERE fID=:vID; " +" END " +"END";

← →
Knight © (2012-05-02 13:42) [35]

> дефолтом ставится = 1, отсюда следует, что при создании
> новой записи проверять и изменять статус родителя вообще
> не требуется

Почему не требуется? Было 3 дочерних записи со статусом 4, у родителя соответственно тоже 4, добавили новую задачу, статус родителя должен смениться на 3.

> Труп Васи Доброго © (02.05.12 12:49) [29]
> > Надо хранить у родителя не статус, а количество дочерних
> > веток
>
> Категорически не согласен, ибо это подразумевает наличие
> разницы между детьми и родителями, по условию все записи
> равнозначные, а у тебя придётся вводить какой-то особый
> признак родительскости + ещё доп. поля для количества дочек
> и их статусов.

Knight © (30.04.12 17:36) [1]
Необходимые поля
fID - идентификатор
fParentID - родительский идентификатор (для дерева)
fStatus - статус (который надо менять вверх по дереву по вышеуказанным правилам)

Все нормально. Просто в fStatus хранить данные о дочках. Собственный статус рассчитывается в зависимости от дочек.

> добавили новую задачу, статус родителя должен смениться
> на 3.

Согласен, я не подумал, что к выполненной задаче можно ещё задания добавлять :) Ну тогда в мой второй триггер надо создавать как ACTIVE AFTER UPDATE OR DELETE OR INSERT
Всё будет работать.

А что за глюк с переменными? Может компоненте надо сообщить как-то, что это не параметр, а переменная?

> Knight © (02.05.12 14:45) [38]

надо поставить Query.ParamCheck = False
Хотя, странно это - зачем триггер создавать из своей программы?

> Хотя, странно это - зачем триггер создавать из своей программы?

Прога с базой embedded, если базы нет генерю чистую при первом запуске.

> надо поставить Query.ParamCheck = False

И ещё Query.Params.Clear; Спасибо! Ошибка пропала :)

Совсем запутал! Это тебе кто сообщение об ошибке выдаёт? И Когда выдаёт?
з.Ы. Вынеси UPDATE "за скобки"

Query.SQL.Text:="CREATE TRIGGER AU_NOTE FOR tNote "
+" ACTIVE AFTER INSERT OR UPDATE OR DELETE "
+"AS "
+"DECLARE variable vID INTEGER; "
+"DECLARE variable vStatus INTEGER; "
+"BEGIN "
+" IF (DELETING) THEN "
+" vID=Old.fParentID; "
+" ELSE "
+" vID=New.fParentID; "
+" IF (vID>0) THEN "
+" BEGIN "
+" IF (NOT (EXISTS(SELECT 1 FROM tNote WHERE fParentID=:vID AND fStatus<>1))) THEN "
+" vStatus=1; "
+" ELSE "
+" IF (NOT (EXISTS(SELECT 1 FROM tNote WHEREfParentID=:vID AND fStatus<>4))) THEN "
+" vStatus=4; "
+" ELSE "
+" IF (EXISTS(SELECT 1 FROM tNote WHERE fParentID=:vID AND (fStatus=3 OR fStatus=4))) THEN "
+" vStatus=3; "
+" ELSE "
+" vStatus=2; "
+" END "
+" UPDATE tNote SET fStatus=:vStatus WHERE fID=:vID; "
+"END";

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

> изменить непосредственно статус "родителя"

Изменение статуса родителя из программы недоступно.

> Труп Васи Доброго

Я твой вариант сохранил на всякий :)

> Изменение статуса родителя из программы недоступно.

Я тоже раньше был такой наивный. Запомни, всегда найдётся какая-нибудь .адла "умная", которая залезет в базу в обход программы и поковыряется.
Да и если вдруг база будет использоваться другими программами (написанными не тобой), то там такой защиты может не быть.
ИМХО всё что можно перенести в базу должно быть перенесено. Программа должна только запрашивать готовый результат или передавать базе данные для внесения/изменения, но сама ничего делать в базе не должна, это работа СУБД.

> Труп Васи Доброго © (02.05.12 15:56) [46]

Это диплом. Практической ценности (кроме меня - т.к. пишу для себя) не несёт. Весь смысл, защитить его как-нибудь и пользоваться самому, т.к. органайзер вроде надо, но из готового ничего не подобрал либо не то, либо слишком наворочено :)

> Это диплом. Практической ценности (кроме меня - т.к. пишу
> для себя) не несёт.

Пишешь диплом для себя?
Ого!!!

> oldman (03.05.2012 12:23:48) [48]

А новых студентов принимают?

> Пишешь диплом для себя?

Я 1с занимаюсь, тем подходящих небыло. Выбрал что самому пригодится.

Триггер в FireBird Найти похожие ветки