1

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

2
  • Why not do this in the delete statement itself? Commented Jul 11, 2019 at 11:33
  • I want to use it as a safety feature. So that you don't have to build these checks in the apps Commented Jul 11, 2019 at 11:35

1 Answer 1

3

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();
Sign up to request clarification or add additional context in comments.

4 Comments

Just curious: Why aren't you building the same function as BEFORE DELETE trigger?
@S-Man: because statement level triggers can only be defined as AFTER triggers (which makes sense, because only after everything was done can the database know which rows were affected by the statement)
Thanks for the answer. Will this revert the deleted records though?
@AndreCoetzee: yes, as I wrote: it will rollback the transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.