Как реализовать работу с CURSOR в триггере

[frostyland]

Приветствую!

В триггере на INSERT/UPDATE/DELETE необходимо изменить значения в нескольких записях.
Подскажите синтаксис пробежки по курсору, а лучше - документацию на CURSOR (сам поразбираюсь)

[kdv]

www.ibase.ru/devinfo/updsame.htm
плюс release notes, если используешь FB 2.x

[frostyland]

http://www.ibase.ru/devinfo/updsame.htm
плюс release notes, если используешь FB 2.x

Вот тут я в-общем-то нарыл
http://www.lnssoftware.ca/esql.htm#4.3. ... or|outline [4.3.14.3. Fetching Rows from a Cursor]

Сделал вот такую конструкцию

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

  OPEN CUR1;
  FETCH CUR1 INTO :ID, :X, :Y;
  WHILE (SQLCODE = 0)
  DO
  BEGIN
    FETCH CUR1 INTO :ID, :X, :Y;
    
    UPDATE DEAL
    SET PDN_PART = :X
    WHERE
      DEAL_NO = :ID;

    
  END
  CLOSE CUR1;

Однако. при попытке изменения записи (в BlazeTop), происходит ошибка

Too many concurrent executions of the same request.
Too many concurrent executions of the same request.
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At trigger 'TR_DEALS_SUM_FOR_PDN'
At .

(TR_DEALS_SUM_FOR_PDN - как раз мой триггер).
Ругань на вызов вот этого запроса

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

    UPDATE DEAL
    SET PDN_PART = :X
    WHERE
      DEAL_NO = :ID;

Собственно. я пытаюсь обнови... Аааа! Я обновляю, снова срабатывает триггер.. короче - рекурсия.
А как быть?

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

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

  BEGIN
    FETCH CUR1 INTO :ID, :REF_X, :Y;
    REF_X =  Y    
  END

[frostyland]

Вот е-мое...

Голову сломал. Не работает такой синтаксис

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

  OPEN CUR1;
  FETCH CUR1 INTO :CURR_DEAL_ID, :CURR_DEAL_SUM;

  WHILE (SQLCODE = 0)
  DO
  BEGIN
    PDN_FOR_DEAL = :CURR_DEAL_SUM * :PDN_SUM / :ALL_DEAL_SUM;
    UPDATE REPORT_INFO
    SET
      PDN_SUM = :PDN_SUM,
      DEAL_SUM = :CURR_DEAL_SUM,
      DEAL_PDN = :PDN_FOR_DEAL
    WHERE
      FK_DEAL = :DEAL_NO;

    FETCH CUR1 INTO :CURR_DEAL_ID, :CURR_DEAL_SUM;
  END
  CLOSE CUR1;

Проблема именно в цикле. Видимо, зацикливается. Как правильно проверять курсор на завершение записей?

[kdv]

ээээ... Курсоры, это вообще часть Embedded SQL. Embedded SQL это "встраиваемый" в С/C++ приложения текст SQL, который обрабатывается препроцессором. Т.е. по факту курсоров как таковых нет, есть обычные "выборки". Например, IBQuery.Open. Это тоже "курсор".

Тем не менее, некоторые производители РСУБД встроили в SQL, поддерживаемый сервером, часть Embedded SQL. Причины разные, в них вдаваться не будем.
Так вот. В FB 2.x введена поддержка курсоров на уровне языка SQL. Но понятно, что синтаксис урезанный, и описан в ReleaseNotes, а не в каких-то сторонних документов хрен знает от какого сервера.

Как правильно проверять курсор на завершение записей?

что-что?

Проблема именно в цикле. Видимо, зацикливается.

запрос-то какой? и надеюсь, курсор не в триггере? Если в триггере, то за такое сажать надо. Годика на 3. с отлучением от компьютера

[WildSery]

Автор, триггер срабатывает на каждую изменяемую запись, а не "на каждый update".
Потому никаких циклов в триггере быть не может. Забудьте про MSSQL, тут нет групповой обработки изменённых записей.

[frostyland]

В общем так.
В триггере сделал вызов процедуры, а в ней уже курсор

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

  OPEN CURR_DEAL_CURSOR;

  WHILE (1=1) DO
    BEGIN
      FETCH CURR_DEAL_CURSOR INTO :CURR_DEAL_ID, :CURR_DEAL_SUM;

      IF (ROW_COUNT = 0) THEN
        LEAVE;

  CLOSE CURR_DEAL_CURSOR;

Проверка на последнюю запись через SQLCODE так и не получается.
Делаю через IF (ROW_COUNT = 0)
Работает...

[kdv]

Работает...

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

[WildSery]

В общем так.
В триггере сделал вызов процедуры, а в ней уже курсор

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

[frostyland]

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

Там вот так:
Есть таблица DEAL, хранящая суммы по каждой сделке в поле DEALSUM. Сделки имеют внешний ключ учетного периода FK_MONTH_NO и отдела FK_DEPT_NO

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

CREATE TABLE DEAL(
  DEAL_NO     INTEGER NOT NULL,
  FK_DEPT_NO  INTEGER,
  FK_MONTH_NO INTEGER,
  DEALSUM     DECIMAL(9,2) DEFAULT 0
);

Есть таблица REPORT_INFO, в полях которой надо производить различные расчеты при любом изменении в таблице DEAL.
К примеру, пробегаться в DEAL по всем сделкам данного периода и отдела, пересчитывать удельный порог доходности для каждой сделки, после чего вносить информацию в REPORT_INFO.DEAL_PDN.

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

CREATE TABLE REPORT_INFO(
  REPORT_INFO_NO  INTEGER NOT NULL, 
  FK_DEAL_NO     INTEGER NOT NULL,          
  FK_MONTH_NO    INTEGER NOT NULL,        
  FK_DEPT_NO     INTEGER NOT NULL,
  PDN_SUM     DECIMAL(9,2) DEFAULT 0,      /* сумма ПДН для отдела, которому принадлежит сделка (копируется из таблицы PDN) */  
  DEAL_SUM    DECIMAL(9,2) DEFAULT 0,     /* сумма сделки (копируется из  DEAL) */
  DEAL_PDN    DECIMAL(9,2) DEFAULT 0,     /* удельный ПДН для сделки. Равен: (сумма данной сделки / суммы всех сделок отдела по периоду) * PDN_SUM, то есть ПДН отдела за период */
);

Что я делаю:

Использую триггер для таблички DEAL, в котором вызываю процедуру

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

CREATE TRIGGER TR_DEALS_SUM_FOR_PDN FOR DEAL
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0
AS

DECLARE VARIABLE MONZ_NO INTEGER = 0;
DECLARE VARIABLE DEPT_NO INTEGER = 0;
DECLARE VARIABLE DEAL_NO INTEGER = 0;
DECLARE VARIABLE IS_MAKE SMALLINT = 0;
DECLARE VARIABLE IS_EXISTS SMALLINT = 0;

/*
  При любом изменении в суммах и активности сделок

  1. Если сделка добавляется, добавим соответствие в REPORT_INFO
     Если удаляется - удалим из REPORT_INFO
  2. Вызываем процедуру обновления таблицы отчетов

*/

BEGIN

-- 1.
  IF (INSERTING OR UPDATING) THEN
  BEGIN
    MONZ_NO = NEW.FK_MONTH_NO;
    DEPT_NO = NEW.FK_DEPT_NO;
    DEAL_NO = NEW.DEAL_NO;
    IS_MAKE = NEW.MAKE;
  END
  ELSE IF (DELETING) THEN
  BEGIN
    MONZ_NO = OLD.FK_MONTH_NO;
    DEPT_NO = OLD.FK_DEPT_NO;
    DEAL_NO = OLD.DEAL_NO;
    IS_MAKE = OLD.MAKE;
  END
  
-- приводим записи REPORT_INFO в соответствие с DEAL

  SELECT COUNT(*) FROM REPORT_INFO
  WHERE FK_DEAL = :DEAL_NO
  INTO :IS_EXISTS;

  IF (INSERTING AND IS_MAKE=1) THEN
    INSERT INTO REPORT_INFO(FK_DEAL, FK_MONTH, FK_DEPT)
      VALUES(:DEAL_NO, :MONZ_NO, :DEPT_NO);

  ELSE IF (UPDATING) THEN
  BEGIN
    IF ((IS_MAKE=1) AND (IS_EXISTS=0)) THEN
      INSERT INTO REPORT_INFO(FK_DEAL, FK_MONTH, FK_DEPT)
        VALUES(:DEAL_NO, :MONZ_NO, :DEPT_NO);
    ELSE IF (IS_MAKE=0) THEN
      DELETE FROM REPORT_INFO
        WHERE FK_DEAL = :DEAL_NO;
  END

  ELSE IF (DELETING) THEN
    DELETE FROM REPORT_INFO
      WHERE FK_DEAL = :DEAL_NO;

-- 2
  EXECUTE PROCEDURE PR_UPD_REPORT_INFO(:DEPT_NO, :MONZ_NO);

END
;

Процедура вот такая

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

CREATE PROCEDURE PR_UPD_REPORT_INFO (
  DEPT_NO   INTEGER,
  MONZ_NO   INTEGER
)
AS
DECLARE VARIABLE PDN_SUM DECIMAL(15,3) = 0;
DECLARE VARIABLE ALL_DEAL_SUM DECIMAL(15,3) = 0;
DECLARE VARIABLE ALL_EXP_SUM DECIMAL(15,3) = 0;

DECLARE VARIABLE CURR_DEAL_CURSOR CURSOR FOR
    (SELECT D.DEAL_NO, D.DEALSUM
      FROM DEAL D
      WHERE D.FK_MONTH_NO = :MONZ_NO
      AND D.FK_DEPT_NO = :DEPT_NO
      AND D.MAKE = 1);

DECLARE VARIABLE CURR_DEAL_ID INTEGER = 0;
DECLARE VARIABLE CURR_DEAL_SUM DECIMAL(15,3) = 0;
DECLARE VARIABLE PDN_FOR_DEAL DECIMAL(15,5) = 0;

DECLARE VARIABLE CURR_EXP_CURSOR CURSOR FOR
    (SELECT SUM(E.VAL)
      FROM EXPENDS_IN_DEAL E
      WHERE E.FK_DEAL = :CURR_DEAL_ID);

BEGIN

-- 1
  SELECT
    P.PDN FROM PDN P
  WHERE
    P.FK_DEPT_NO = :DEPT_NO AND
    P.FK_MONTH_NO = :MONZ_NO
  INTO :PDN_SUM;

-- 2
  SELECT
    SUM(D.DEALSUM) FROM DEAL D
  WHERE
    D.FK_DEPT_NO = :DEPT_NO AND
    D.FK_MONTH_NO = :MONZ_NO AND
    D.MAKE = 1
  INTO :ALL_DEAL_SUM;
  

--  3

  OPEN CURR_DEAL_CURSOR;

  WHILE (1=1) DO
    BEGIN
      FETCH CURR_DEAL_CURSOR INTO :CURR_DEAL_ID, :CURR_DEAL_SUM;

      IF (ROW_COUNT = 0) THEN
        LEAVE;

      PDN_FOR_DEAL = :CURR_DEAL_SUM * :PDN_SUM / :ALL_DEAL_SUM;
      
      OPEN CURR_EXP_CURSOR;
      FETCH CURR_EXP_CURSOR INTO :ALL_EXP_SUM;
      CLOSE CURR_EXP_CURSOR;

      UPDATE REPORT_INFO
      SET
        PDN_SUM = :PDN_SUM,
        DEAL_SUM = :CURR_DEAL_SUM,
        DEAL_PDN = :PDN_FOR_DEAL,
        DEAL_EXP = :ALL_EXP_SUM,
        DEAL_PROFIT = :CURR_DEAL_SUM - :PDN_FOR_DEAL - :ALL_EXP_SUM
      WHERE
        FK_DEAL_NO = :CURR_DEAL_ID;

    END

  CLOSE CURR_DEAL_CURSOR;

END
;

Там много чего дополнительного в этой процедуре, но главное - в цикле WHILE (1=1) DO я пробегаю все сделки и для каждой из них привожу в соответствие записи REPORT_INFO.
Вот собственно...

и надеюсь, курсор не в триггере? Если в триггере, то за такое сажать надо.

Признаться я не понял такой категоричной позиции
ВОт тут именно так и поступают

Кроме того, подскажите мне как по-другому реализовать вот такую реакцию:
При добавлении в таблицу X добавить в таблицу Y по одной записи для каждого соответствия в таблице Z...
Я понимаю, что так или иначе надо бежать по таблице Z, а для этого курсор и нужен

[Slavik]

Смотрю, смотрю... И никак в толк не возьму, зачем тебе извраты с курсорами? FOR SELECT не подходит? Синтаксис описан в документации. Не надо лишних экспериментов.

Я понимаю, что так или иначе надо бежать по таблице Z, а для этого курсор и нужен

Бегать не всегда обязательно. UPDATE'ом можно править и несколько записей сразу.

P.S. Версию сервера тоже не вижу, хотя уже намекали. Но судя по всему не ниже FB2.x

[kdv]

Признаться я не понял такой категоричной позиции
ВОт тут именно так и поступают

мда. в MS SQL триггеры срабатывают "потом". В IB/FB они срабатывают сразу.
Никаких inserted/updated в IB/FB нет, потому что триггеры срабатывают при вставке-изменении-удалении КАЖДОЙ записи.
Т.е. если update обновляет 55 записей, то триггер before update вызовется 55 раз.
Поэтому способы работы с триггерами, изложенные в том курсе для MS SQL, в большинстве не годятся для IB/Fb. как и для Оракла с триггерами for each row.

Поэтому в триггерах IB/FB обычно никаких циклов не пишут. Потому что для предыдущего случая цикл вызовется 55 раз.
В общем, думать надо, а не лепить.

[frostyland]

В общем, думать надо, а не лепить.

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

[Slavik]

Не представляю, как помочь человеку, который никого не слышыт. Товарищ просто ведёт здесь дневник эволюции своих экспериментов. Почитать документацию, которую советуют -- ни за что, это же противоречит принципам эволюции кодера. На конструкцию for select, которую советовали уже два раза даже не обратил внимания. Зато нарыть в инете примеров от хз каких серверов и увлечённо с ними экспериментировать -- это пожалуйста. Версию используемого сервера так и не сообщил. Может это и не IB/FB вовсе?

Кроме того, подскажите мне как по-другому реализовать вот такую реакцию:
При добавлении в таблицу X добавить в таблицу Y по одной записи для каждого соответствия в таблице Z...
Я понимаю, что так или иначе надо бежать по таблице Z, а для этого курсор и нужен

В простых случаях это решается классическим insert (...) select ... from ... Без всяких курсоров и циклов. Синтаксис опять же описан в доке.

[frostyland]

Не представляю, как помочь человеку, который никого не слышыт. Товарищ просто ведёт здесь дневник эволюции своих экспериментов. Почитать документацию, которую советуют -- ни за что, это же противоречит принципам эволюции кодера. На конструкцию for select, которую советовали уже два раза даже не обратил внимания. Зато нарыть в инете примеров от хз каких серверов и увлечённо с ними экспериментировать -- это пожалуйста. Версию используемого сервера так и не сообщил. Может это и не IB/FB вовсе?

Кроме того, подскажите мне как по-другому реализовать вот такую реакцию:
При добавлении в таблицу X добавить в таблицу Y по одной записи для каждого соответствия в таблице Z...
Я понимаю, что так или иначе надо бежать по таблице Z, а для этого курсор и нужен

В простых случаях это решается классическим insert (...) select ... from ... Без всяких курсоров и циклов. Синтаксис опять же описан в доке.

Слышу, слышу, уважаемый kdv.
Почитал и про FOR SELECT, признаюсь не знал. С другой стороны, это тот же курсор, только неименованный. Про версию сервера не упоминал, ибо никак не могу привыкнуть. Учту на будущее, версия 2.
В Firebird_v2.0.4.ReleaseNotes.pdf кстати нет рекомендации НЕ использовать EXPLICIT CURSORS.
А insert (...) select ... from ... видимо не для моего случая, нельзя мне без цикла по условиям задачи. Во всяком случае, на данном витке своей SQL-эволюции я так думаю.

[kdv]

Слышу, слышу, уважаемый kdv.

это slavik писал, а не я.

В Firebird_v2.0.4.ReleaseNotes.pdf кстати нет рекомендации НЕ использовать EXPLICIT CURSORS.

там также нет рекомендации не использовать execute statement. и так далее. Использовать можно все, это не запрещено, функциональность для того и делается, чтобы ее использовали.
Но - есть грамотное использование, а есть ... И при этом самом альтернативном использовании (неграмотном) очень легко получить нулевую производительность, если не при разработке, то при первом же запуске БД в промышленную эксплуатацию. А потом, соответственно, будем говорить что "ФБ - отстой".

[frostyland]

Но - есть грамотное использование, а есть ...

Вот это - ключевая фраза, которая, как мне кажется и может служить фундаментом полезных форумов. Отсылать к документации не есть сложно, можно запустить для этих целей бота и попивать чаек. Только редко где в документации можно найти жизненные рекомендации, а если и есть такая то придется перевернуть горы бумаг, а на это не всегда есть время. Форум есть место обмена жизненным опытом программистов разного ранга в режиме реального времени, как мне кажется. В частности, в процессе развития топика у меня сложилось мнение, что ряд собеседников обладает сакральным знанием, что использовать EXPLICIT CURSORS - моветон, а вот FOR SELECT - то что надо. Ну так поделитесь им, или дайте ссылку на обсуждение такого вопроса, будет очень полезно для начинающих.

[Tonal]

Да в другом тут вопрос.
Народ никак врубится не может зачем тебе при изменении 1 записи менять не связанные с ней записи.
Да ещё и в этой же таблице (если судить по твоей рекурсии).
По хорошему, поле REPORT_INFO.PDN_SUM должко быть в таблице отделов, а REPORT_INFO.DEAL_PDN - вообще вычисляться во время запроса данных.
Тогда тебе в триггере DEAL не нужен будет ни for ... select, ни именованный курсор, а только примитивные действия на вставку/обновление/удаление.

[WildSery]

1. Невежливо не указывать наличие полей MAKE в таблице DEAL, а также DEAL_EXP и DEAL_PROFIT в REPORT_INFO, и в то же время использовать их в коде.
2. Связка триггер+процедура построены так, что если в DEAL у существующей сделки я изменю, например, месяц, то "отчётные" записи будут пересчитаны для нового месяца, но не тронут старый месяц. Т.е. сделка будет учтена в обоих месяцах. То же самое для изменения DEPT_NO.
3. Если бы даже предполагалось хранить океан данных, чтобы оправдать существование заранее рассчитанной REPORT_INFO, то динамический её расчёт сводит на нет все преимущества, и даже наоборот, жутко усугубляет. Почему бы вообще не отказаться от этой таблицы, и использовать запрос?
(Пишу из головы, возможны ошибки)

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

select d.deal_no,
       d.fk_month_no,
       d.fk_dept_no,
       p.pdn as pdn_sum,
       d.dealsum as deal_sum,
       d.dealsum * p.pdn / deal_group.deals_sum as deal_pdn,
       exp_group.val as deal_exp,
       d.dealsum - d.dealsum * p.pdn / deal_group.deals_sum - coalesce(exp_group.val, 0) as deal_profit
  from DEAL d
       join PDN p on p.fk_dept_no = d.fk_dept_no and p.fk_month_no = d.fk_month_no
       (select fk_dept_no, fk_month_no, sum(dealsum) deals_sum
          from deal
          where make = 1
          group by 1, 2
       ) deal_group on deal_group.fk_dept_no = d.fk_dept_no and deal_group.fk_month_no = d.fk_month_no
       left join
       (select fk_deal, sum(val) as val
          from EXPENDS_IN_DEAL
          group by 1
       ) exp_group on exp_group.fk_deal = d.deal_no
  where d.make = 1

К запросу можно добавлять фильтрующие условия точно так же, как для запроса из таблицы REPORT_INFO, для чего бы она ни использовалась.
И данные всегда будут текущие и корректны, в отличие от.

[frostyland]

К запросу можно добавлять фильтрующие условия точно так же, как для запроса из таблицы REPORT_INFO, для чего бы она ни использовалась.
И данные всегда будут текущие и корректны, в отличие от.

thanxxx!