how to get deleting rows count in before delete trigger in oracle-CodePudding

I'm going to create a before delete trigger in oracle in which I need to know how many records are going to be deleted, letting me to message to user if the number of deleting rows exceeds a threshold (and maybe canceling the deletion)

The question is how to get number of the deleting rows to proceed the check?

Thanks for any help

CodePudding user response：

I guess you can query select count with same conditions that you are going to use for delete and then check the count to decide whether to delete or not. But that will increase one more query.

If your delete command is like delete from table where conditions then you can get count by using select count(*) from table where conditions. Once you get count result decide whether to perform delete or not.

CodePudding user response：

A delete statement can affect many rows. There are four events on which you can place a trigger:

before statement where all you know is that 0 to n deletes are going to take place
before each row where you only see one row that is about to get deleted
after each row where you only see that one row again
after statement where all you know is that that 0 to n deletes have taken place

So none of these trigger types alone helps you with your task. What you want is a compound trigger, where you can count the affected rows in the before or after each row section and look at the total in the after statement section.

CREATE OR REPLACE TRIGGER trg_check_max_deletes
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 > 3 THEN
      RAISE_AQPPLICATION_ERROR(-20000, 'You are not allowed to delete more than 3 rows at once.');
    END IF;
  END AFTER STATEMENT;
END trg_check_max_deletes;