Вопросы Теги

количество количества строк, которые будут затронуты перед обновлением в триггере

Я хочу знать количество строк, которые будут затронуты UPDATE запрос в BEFORE на триггер оператора. Это возможно?

Проблема состоит в том, что я хочу позволить только запросы, которые обновят до 4 строк. Если затронутое количество строк равняется 5 или больше, я хочу повысить ошибку.

Я не хочу делать это в коде, потому что мне нужно, это проверяет уровень дб. Это вообще возможно?

Заранее спасибо за любые подсказки об этом

8
задан sbczk 1 April 2010 в 13:41
поделиться

6 ответов

Я создал что-то вроде этого: 

begin;

create table test (
    id integer
);

insert into test(id) select generate_series(1,100);


create or replace function trg_check_max_4_updated_records() 
returns trigger as $$
declare
    counter_ integer := 0;
    tablename_ text := 'temptable';
begin
    raise notice 'trigger fired';
    select count(42) into counter_ 
        from pg_catalog.pg_tables where tablename = tablename_;
    if counter_ = 0 then
        raise notice 'Creating table %', tablename_;
        execute 'create temporary table ' || tablename_ || ' (counter integer) on commit drop';
        execute 'insert into ' || tablename_ || ' (counter) values(1)';

        execute 'select counter from ' || tablename_ into counter_;
        raise notice 'Actual value for counter= [%]', counter_;
    else
        execute 'select counter from ' || tablename_ into counter_;
        execute 'update ' || tablename_ || ' set counter = counter + 1';
        raise notice 'updating';
        execute 'select counter from ' || tablename_ into counter_;
        raise notice 'Actual value for counter= [%]', counter_;

        if counter_ > 4 then
            raise exception 'Cannot change more than 4 rows in one trancation';
        end if;

    end if;
    return new;
end; $$ language plpgsql;


create trigger trg_bu_test before 
  update on test 
  for each row
  execute procedure trg_check_max_4_updated_records();

update test set id = 10 where id <= 1;
update test set id = 10 where id <= 2;
update test set id = 10 where id <= 3;
update test set id = 10 where id <= 4;
update test set id = 10 where id <= 5;

rollback;

Основная идея состоит в том, чтобы иметь триггер «перед обновлением для каждой строки», который создает (при необходимости) временную таблицу (которая удаляется в конце транзакции). В этой таблице есть только одна строка с одним значением, то есть количеством обновленных строк в текущей транзакции. Для каждого обновления значение увеличивается. Если значение больше 4, транзакция останавливается.

Но я думаю, что это неправильное решение вашей проблемы. В чем проблема, выполнить такой неправильный запрос, о котором вы писали, дважды, чтобы у вас было изменено 8 строк. А как насчет удаления строк или их усечения?

1
ответ дан 5 December 2019 в 21:17
поделиться

Попробуйте использовать Serializable Isolation Level. Я считаю, что это даст вам согласованное представление данных базы данных внутри транзакции. Тогда вы сможете использовать вариант №1, который упомянул MusiGenesis, без уязвимости по времени. Конечно, протестируйте его для проверки.

1
ответ дан 5 December 2019 в 21:17
поделиться

Саймон имел хорошую идею, но его реализация излишне сложна. Это мое предложение: 

create or replace function trg_check_max_4()                
returns trigger as $$
begin
        perform true from pg_class 
                where relname='check_max_4' and relnamespace=pg_my_temp_schema(); 
        if not FOUND then
                create temporary table check_max_4 
                        (value int check (value<=4)) 
                        on commit drop;
                insert into check_max_4 values (0); 
        end if;

        update check_max_4 set value=value+1; 
        return new;
end; $$ language plpgsql;
2
ответ дан 5 December 2019 в 21:17
поделиться

Напишите функцию, которая обновляет строки за вас или выполняет откат. Извините за плохое форматирование стиля. 

create function update_max(varchar, int)
RETURNS void AS
$BODY$

DECLARE

   sql ALIAS FOR $1;
   max ALIAS FOR $2;
   rcount INT;

BEGIN

   EXECUTE sql;
   GET DIAGNOSTICS rcount = ROW_COUNT;

   IF rcount > max THEN

       --ROLLBACK;
       RAISE EXCEPTION 'Too much rows affected (%).', rcount;

   END IF;

   --COMMIT;

END;

$BODY$ LANGUAGE plpgsql

Затем назовите это как 

select update_max('update t1 set id=id+10 where id < 4', 3);

, где первый параметр - это ваш sql-оператор, а второй - максимальное количество строк.

2
ответ дан 5 December 2019 в 21:17
поделиться

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

  1. Выполнить SELECT COUNT (*), чтобы определить количество записей, на которые повлияет ОБНОВЛЕНИЕ, и затем выполнить ОБНОВЛЕНИЕ только в том случае, если счетчик равен 4 или меньше
  2. Выполните ОБНОВЛЕНИЕ внутри транзакции и зафиксируйте транзакцию только в том случае, если возвращенное количество затронутых строк равно 4 или меньше

Нет. 1 является уязвимым по времени (количество записей, затронутых UPDATE, может измениться между проверкой COUNT (*) и фактическим UPDATE. № 2 довольно неэффективен, если есть много случаев, когда количество обновленных строк больше 4.

0
ответ дан 5 December 2019 в 21:17
поделиться

PostgreSQL имеет два типа триггеров : триггеры строк и операторов. Триггеры строк работают только в контексте строки, поэтому вы не можете их использовать. К сожалению, триггеры оператора «до» не видят , какие изменения должны произойти, поэтому я не верю, что вы можете их использовать.

Исходя из этого, я бы сказал, что вряд ли вы сможете встроить такую ​​защиту в базу данных с помощью триггеров, если только вы не против использования триггера «после» и отката транзакции, если условие не устраивает. Не возражал бы, если бы ошиблись. :)

1
ответ дан 5 December 2019 в 21:17
поделиться
Другие вопросы по тегам:

Похожие вопросы: