CODEWITHSUNDEEP
sqlpostgresqltriggers
Andre Coetzee
Andre Coetzee

Reputation: 1310

Postgres trigger check amount before delete

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

Upvotes: 1

Views: 931

Answers (1)

user330315
user330315

Reputation:

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();

Upvotes: 3

Related Questions