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
1 Answer
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;
And then create the trigger:
create trigger prevent_mass_delete
after delete on the_table
referencing old table as old_table
for each statement
execute procedure prevent_delete();
4 Comments
S-Man
Just curious: Why aren't you building the same function as BEFORE DELETE trigger?
Andre Coetzee
Thanks for the answer. Will this revert the deleted records though?
deletestatement itself?