CODEWITHSUNDEEP
oracle-databaseconstraints
Jan S.
Jan S.

Reputation: 331

Prevent update on multiple rows in Oracle db

I had a major screw up in my latest patch. An update condition was incomplete and I updated multiple rows by accident.

What I want to do now is to prevent this by setting a constraint for a table which cause an exception as soon as I try to update multiple rows. Optionally with specific parameters.

Is there a way to do this in Oracle 11.2?

Upvotes: 3

Views: 1194

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You can accomplish this by using a compound trigger:

CREATE OR REPLACE TRIGGER TABLE1_FAIL_MULT_UPDATES_TRG
  FOR UPDATE ON TABLE1
COMPOUND TRIGGER
  nUpdate_count  NUMBER;

  BEFORE STATEMENT IS
  BEGIN
    nUpdate_count := 0;
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    IF UPDATING THEN
      nUpdate_count := nUpdate_count + 1;

      IF nUpdate_count > 1 THEN
        RAISE_APPLICATION_ERROR(-20100, 'Attempted to update more than 1 row');
      END IF;
    END IF;
  END BEFORE EACH ROW;

END TABLE1_FAIL_MULT_UPDATES_TRG;

You can read further on compound triggers here.

Best of luck.

Upvotes: 5

Florin Ghita
Florin Ghita

Reputation: 17643

You can use the Answer on this question which offer a solution with three triggers and package variable to count the number of rows affected. In the third trigger, if the number of rows is greater than one then raise an exception. The entire statement will be rolled back.

This is also safe for concurrency because package variables are "stored" session level.

Upvotes: 0

Related Questions