How to prevent bulk row deletion operations?

user_5279040 0
Viewed 370

I can prevent DELETE completely like this:

CREATE TRIGGER prevent_multiple_row_del
BEFORE DELETE ON ALL
BEGIN
RAISE EXCEPTION 'Cant delete more than 1 row at a time';
END;

But how do I check if the delete operation will lead to deletion of multiple rows? Deletion is not a problem, as long as it's limited to a constant number (1 or 5 or 10, as long as it's not unlimited).

Alternatively, how do I allow deletions but prevent deletions of full tables?

delete-row triggers postgresql
1 Answers

A before statement trigger is too early to know the affected rows.

As to full table deletes, use an after statement trigger. All you'd have to do is select from the table and see whether there is some record left in it.

As to deletes of up to n records, this too would have to be determined after statement. You tagged your question PostgreSQL, but as a_horse_with_no_name pointed out your code is Oracle. In PostgreSQL you'd want to check pg_affected_rows() and in Oracle SQL%ROWCOUNT. I don't know whether PostgreSQL allows to check pg_affected_rows() in an after statement trigger. For Oracle, checking SQL%ROWCOUNT in an after statement trigger doesn't work. It's too early for this variable to check.

So at least for Oracle the trick is to have some custom counter to set to zero before statement, increase after each row and check after statement. I don't know precisely how to do that in PostgreSQL, but there certainly will be a way. In Oracle you'd use a compound trigger, i.e. a super trigger housing the individual triggers.

CREATE OR REPLACE TRIGGER prevent_multiple_row_del
FOR DELETE ON mytable COMPOUND TRIGGER
  v_count INTEGER := 0;

  AFTER EACH ROW IS
  BEGIN
    v_count := v_count + 1;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    IF v_count > 1 THEN
      raise_application_error(-20000, 'Can''t delete more than 1 row at a time');
    END IF;
  END AFTER STATEMENT;

END prevent_multiple_row_del;
user_22707620
Related