在触发器中更新之前计算受影响的行数 [英] count number of rows to be affected before update in trigger

问题描述

我想知道在每个语句触发器之前中受 UPDATE 查询影响的行数。

I want to know number of rows that will be affected by UPDATE query in BEFORE per statement trigger . Is that possible?

问题是我只允许查询最多更新4行的查询。如果受影响的行数是5或更多，我想引发错误。

The problem is that i want to allow only queries that will update up to 4 rows. If affected rows count is 5 or more i want to raise error.

我不想在代码中这样做，因为我需要在数据库级别进行此检查。
这有可能吗？

I don't want to do this in code because i need this check on db level. Is this at all possible?

在此先感谢您提供任何线索

Thanks in advance for any clues on that

推荐答案

我创建了这样的内容：

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，则事务将停止。

The main idea is to have a trigger on 'before update for each row' that creates (if necessary) a temporary table (that is dropped at the end of transaction). In this table there is just one row with one value, that is the number of updated rows in current transaction. For each update the value is incremented. If the value is bigger than 4, the transaction is stopped.

但是我认为这对您的问题是错误的解决方案。重复运行两次您写的错误查询是什么问题，因此您需要更改8行。删除行或将其截断怎么办？

But I think that this is a wrong solution for your problem. What's a problem to run such wrong query that you've written about, twice, so you'll have 8 rows changed. What about deletion rows or truncating them?

这篇关于在触发器中更新之前计算受影响的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！