问题描述
如果客户端用户试图从一个表中删除5条以上的记录，我想使用触发器来限制它.我有一个基本的想法可以做到这一点，但是我不知道如何实现这个想法.感谢您的帮助.
If the client user is trying to delete more than 5 records from a Table i want to restrict that using a trigger. I have a basic idea to do that but i don't know how to implement the Idea. I appreciate any HELP.
基本思想:在触发器IF TG_OP =删除且要删除的记录数大于5的情况下，然后选择限制".
Basic Idea : In Trigger IF TG_OP = Delete and the count of records to be deleted are more than 5 then Restrict.
CREATE TRIGGER adjust_count_trigger BEFORE DELETE ON schemaname.tablename
FOR EACH ROW EXECUTE PROCEDURE public.adjust_count();
CREATE OR REPLACE FUNCTION adjust_count()
RETURNS TRIGGER AS
$$
DECLARE
num_rows int;
num_rows1 int;
BEGIN
IF TG_OP = 'DELETE' THEN
EXECUTE 'select count(*) from '||TG_TABLE_SCHEMA ||'.'||TG_RELNAME ||' where oid = old.oid ' into num_rows ;
IF num_rows > 5 Then
RAISE NOTICE 'Cannot Delete More than 5 Records , % ', num_rows ;
END IF ;
END IF ;
RETURN OLD;
END;
$$
LANGUAGE 'plpgsql';
推荐答案
在早期版本的Postgres中，您可以模拟Postgres 10中引入的转换表.您需要两个触发器.
In earlier versions of Postgres you can simulate a transition table introduced in Postgres 10. You need two triggers.
create trigger before_delete
before delete on my_table
for each row execute procedure before_delete();
create trigger after_delete
after delete on my_table
for each statement execute procedure after_delete();
在第一个触发器中，创建一个临时表并在其中插入一行:
In the first trigger create a temp table and insert a row into it:
create or replace function before_delete()
returns trigger language plpgsql as $$
begin
create temp table if not exists deleted_rows_of_my_table (dummy int);
insert into deleted_rows_of_my_table values (1);
return old;
end $$;
在临时表的其他触发器计数行中并将其删除:
In the other trigger count rows of the temp table and drop it:
create or replace function after_delete()
returns trigger language plpgsql as $$
declare
num_rows bigint;
begin
select count(*) from deleted_rows_of_my_table into num_rows;
drop table deleted_rows_of_my_table;
if num_rows > 5 then
raise exception 'Cannot Delete More than 5 Records , % ', num_rows;
end if;
return null;
end $$;
上述解决方案似乎有点怪异，但如果删除之前仅临时表不存在，则是安全的(不要对多个表使用相同的临时表名称).
The above solution may seem a bit hacky but it is safe if only the temp table does not exist before delete (do not use the same name of the temp table for multiple tables).
这篇关于如何使用PL/pgSQL触发器限制删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！