Prevent update on multiple rows in oracle db

Question

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

What i wanna do now is to prevent this by setting a constraint for a table wich 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 ?

yes this is a table where it makes no sense to update more than on row at a time. — Jan S., Nov 05 '15 at 10:16
You could use trigger to rollback any update that affects more than 1 row — Lukasz Szozda, Nov 05 '15 at 10:17
@lad2025 do we know how many rows are affected in a before statement trigger? — Florin Ghita, Nov 05 '15 at 10:19
@FlorinGhita I am from SQL Server world and there yes. I search equivalent for Oracle, but I see it is executed `FOR EACH ROW` — Lukasz Szozda, Nov 05 '15 at 10:20
Ok so there is no way to prevent it from happening after all ? Is the only option to rollback via trigger ? — Jan S., Nov 05 '15 at 10:24
@ZSchneidi - COMMIT and ROLLBACK statements are not allowed in triggers. — Bob Jarvis - Слава Україні, Nov 05 '15 at 12:08

score 5 · Answer 1 · edited Dec 05 '19 at 23:49

5

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.

edited Dec 05 '19 at 23:49

Jeromy French

11,812
19
76
129

answered Nov 05 '15 at 12:02

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

48,992
9
77
110

Wow, forgot about compound triggers. It is exactly what the op needs. – Florin Ghita Nov 05 '15 at 13:14
It would be interesting to see what happens in case of statement restart as noticed in the answer I mentioned. http://tkyte.blogspot.ro/search?q=statement+restart – Florin Ghita Nov 05 '15 at 14:22

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

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.

edited May 23 '17 at 11:45

Community

1
1

answered Nov 05 '15 at 10:27

Florin Ghita

17,525
6
57
76

Prevent update on multiple rows in oracle db

2 Answers2