我想知道BEFORE per语句触发器中将受UPDATE查询影响的行数.那可能吗?
问题是我想只允许最多更新4行的查询.如果受影响的行数为5或更多,我想提出错误.
我不想在代码中执行此操作,因为我需要对数据库级别进行此检查.
这是可能吗?
提前感谢任何有关此线索的线索
我创造了这样的东西:
- 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行.删除行或截断它们怎么样?