Рекурсивный вызов триггера

Lonely Mazaretsky
Дата: 12.11.2004 18:51:14
Привет, народ!

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

DECLARE
  max_numorder bigint;
BEGIN
  SELECT MAX(numorder) INTO max_numorder FROM flashmovie;
  IF NOT FOUND THEN
    NEW.numorder := 1;
  ELSIF NEW.numorder > max_numorder THEN
    NEW.numorder := max_numorder + 1;
  ELSE
    UPDATE flashmovie SET numorder = numorder - 1 WHERE numorder > OLD.numorder;
    UPDATE flashmovie SET numorder = numorder + 1 WHERE numorder >= NEW.numorder;
  END IF;
  return NEW;
END;

Очевидно, что если будет вызван UPDATE внутри триггера на эту же таблицу, то триггер пойдет по рекурсии. Как этого избежать? Могу ли я к примеру каким-то образом узнать уровень вложенности текущего вызова триггера, чтобы сразу же из него выйти, когда этот уровень больше чем "1"?
фффф
Дата: 15.11.2004 04:30:35
Наверное нет такой функции.
В pl/perl или pl/python сохраняется состояние глобальных переменных между вызовами функций в рамках сессии, можно воспользоваться этим (не для написания логики триггера - это IMHO часто проще делать в pl/pgsql, а только для хранения переменных). Допустим вот так:
create or replace function inc_var(text) returns int as $_$
 return ++$v{$_[0]};
$_$ language plperl;

create or replace  function dec_var(text) returns int as $_$
 return --$v{$_[0]};
$_$ language plperl;

create or replace  function get_var(text) returns int as $_$
 return $v{$_[0]};
$_$ language plperl;

create or replace  function set_var(text, int) returns int as $_$
 return $v{$_[0]} = $_[1];
$_$ language plperl;

--перед insert/update/delete
select set_var('trigger_a_nestlevel', 0);

--в начале триггера
if get_var('trigger_a_nestlevel') > 0 begin
  return NEW;
end if;
perform inc_var('trigger_a_nestlevel');
...
--в конце триггера
perform dec_var('trigger_a_nestlevel');
return NEW;
И возможно проще будет хранить порядковые номера для сортировки в отдельной таблице (с foreign key на основную).
4321
Дата: 15.11.2004 14:47:59
Избежать каскада можно, если:
1. Триггер в записи будет проверять некое условие. (например дополнительное буленово поле todo bool DEFAULT TRUE, или иной признак состояния).
2. Перед UPDATE для всех остальных записей перевести их в требуемое состояние "отключения обработки"

Идея такова: для BEFORE UPDATE
(возможно и не сработает - надо проверить)

.... -- то же, что у вас
....
BEGIN
  IF NEW.todo THEN     
      SELECT MAX(numorder) INTO max_numorder FROM flashmovie;
      IF NOT ...
          .... -- то же, что у вас
      ELSE
          -- сначала установим флаги
          UPDATE flashmovie SET todo = FALSE WHERE  numorder !=  OLD.numorder;
          
          .... -- то же, что у вас
  ELSE
       --DO NOTHING , вернее:
       IF NOT OLD.todo THEN
            -- апдейт значения триггером => установить флаг в ожидание
            NEW.todo = True;  --восстановить после UPDATE - если BEFORE UPDATE
       ELSE 
            -- установка флага триггером
            --DO NOTHING
       END IF; 
   END IF;
   return NEW;
END;


Вот потестировал:
К сожалению Бефоре не прошло (у меня) а вот с Афтер:
CREATE TABLE public.test
(
  id int4 NOT NULL,
  name varchar(64) NOT NULL,
  number int4,
  todo bool DEFAULT true,
  CONSTRAINT pkey_test PRIMARY KEY (id)
) WITHOUT OIDS;

--!
CREATE OR REPLACE FUNCTION public.testbefore()
  RETURNS trigger AS
'DECLARE
	max_number bigint;
BEGIN
	IF NEW.todo THEN
		IF OLD.number <> NEW.number THEN -- чтобы не вызывать пустопоржне
			SELECT MAX(number) INTO max_number FROM test;
			IF NOT FOUND THEN
				NEW.number := 1;
			ELSIF NEW.number > max_number THEN
				NEW.number := max_number + 1;
			ELSE
				-- сначала установим флаги
				UPDATE test SET todo = FALSE WHERE  id <>  NEW.id;
	
				UPDATE test SET number = number - 1 WHERE number > OLD.number  AND id <>  NEW.id;
				UPDATE test SET number = number + 1 WHERE number >= NEW.number  AND  id <>  NEW.id;
			END IF;
		END IF;
	ELSE
		--DO NOTHING , вернее:
		IF NOT OLD.todo THEN
			-- апдейт значения триггером => установить флаг в ожидание
			--NEW.todo = True;  --восстановить после UPDATE - если BEFORE UPDATE
			UPDATE test SET todo = True WHERE id = NEW.id;
		ELSE 
			-- установка флага триггером
			--DO NOTHING			
		END IF; 
	END IF;   
	return NEW;
END;'
  LANGUAGE 'plpgsql' VOLATILE;

--!
CREATE TRIGGER test_before_update
--  BEFORE UPDATE - не получилось
  AFTER UPDATE
  ON public.test
  FOR EACH ROW
  EXECUTE PROCEDURE public.testbefore();
чтобы не хранить поле todo в постоянной таблице, то же видимо можно организовать с временной таблицей (+ количество обновляемых каскадно записей надо усечь до реально необходимого числа). Как настропалить BEFORE UPDATE на то же самое - надо помозговать.