Postgres 删除前触发检查金额 [英] Postgres trigger check amount before delete
本文介绍了Postgres 删除前触发检查金额的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试在触发器之前创建一个 postgres,以检查在实际删除之前将要删除的记录数量.例如不删除超过 5 条记录
I am trying to create a postgres before trigger to check the amount of records that are going to be deleted before it actually does. For example to not delete more than 5 records
推荐答案
您可以使用 AFTER DELETE 语句级触发器来实现这一点.在触发器函数中,您可以计算受影响的行数,如果计数过高则抛出异常.该异常将强制回滚发起删除的事务.
You could achieve that with an AFTER DELETE statement-level trigger. Inside the trigger function you can count the number of affected rows and throw an exception if the count is too high. The exception will force a rollback of the transaction that initiated the delete.
create function prevent_delete()
returns trigger
as
$BODY$
declare
l_count integer;
begin
select count(*)
into l_count
from old_table;
if l_count > 5 then
raise exception 'Too many rows would be deleted';
end if;
return null;
end;
$BODY$
LANGUAGE plpgsql;
然后创建触发器:
create trigger prevent_mass_delete
after delete on the_table
referencing old table as old_table
for each statement
execute procedure prevent_delete();
这篇关于Postgres 删除前触发检查金额的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文