postgresql – 计算触发器更新前要受影响的行数

前端之家收集整理的这篇文章主要介绍了postgresql – 计算触发器更新前要受影响的行数前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我想知道BEFORE per语句触发器中将受UPDATE查询影响的行数.那可能吗?

问题是我想只允许最多更新4行的查询.如果受影响的行数为5或更多,我想提出错误.

我不想在代码中执行此操作,因为我需要对数据库级别进行此检查.
这是可能吗?

提前感谢任何有关此线索的线索

我创造了这样的东西:
  1. begin;
  2.  
  3. create table test (
  4. id integer
  5. );
  6.  
  7. insert into test(id) select generate_series(1,100);
  8.  
  9.  
  10. create or replace function trg_check_max_4_updated_records()
  11. returns trigger as $$
  12. declare
  13. counter_ integer := 0;
  14. tablename_ text := 'temptable';
  15. begin
  16. raise notice 'trigger fired';
  17. select count(42) into counter_
  18. from pg_catalog.pg_tables where tablename = tablename_;
  19. if counter_ = 0 then
  20. raise notice 'Creating table %',tablename_;
  21. execute 'create temporary table ' || tablename_ || ' (counter integer) on commit drop';
  22. execute 'insert into ' || tablename_ || ' (counter) values(1)';
  23.  
  24. execute 'select counter from ' || tablename_ into counter_;
  25. raise notice 'Actual value for counter= [%]',counter_;
  26. else
  27. execute 'select counter from ' || tablename_ into counter_;
  28. execute 'update ' || tablename_ || ' set counter = counter + 1';
  29. raise notice 'updating';
  30. execute 'select counter from ' || tablename_ into counter_;
  31. raise notice 'Actual value for counter= [%]',counter_;
  32.  
  33. if counter_ > 4 then
  34. raise exception 'Cannot change more than 4 rows in one trancation';
  35. end if;
  36.  
  37. end if;
  38. return new;
  39. end; $$language plpgsql;
  40.  
  41.  
  42. create trigger trg_bu_test before
  43. update on test
  44. for each row
  45. execute procedure trg_check_max_4_updated_records();
  46.  
  47. update test set id = 10 where id <= 1;
  48. update test set id = 10 where id <= 2;
  49. update test set id = 10 where id <= 3;
  50. update test set id = 10 where id <= 4;
  51. update test set id = 10 where id <= 5;
  52.  
  53. rollback;

主要思想是在“每行更新之前”触发,创建(如果需要)临时表(在事务结束时删除).在此表中只有一行具有一个值,即当前事务中更新的行数.对于每次更新,值都会递增.如果该值大于4,则停止事务.

但我认为这对你的问题是一个错误解决方案.运行这样的错误查询有什么问题,你写了两次,所以你将改变8行.删除行或截断它们怎么样?

猜你在找的Postgre SQL相关文章