CODEWITHSUNDEEP
oracle-databaseplsqltriggers
user1069968
user1069968

Reputation: 267

ORACLE trigger check if each value of a row is different to each other

ist it possible to compare on insert and update if all values are different to each other (except NULL)? i have 10 columns with numbers and doesnt want to write for each possibility an if statement.

example:

column_1 | column_2 | column_3
--------------------------------
   5           2          4        <- allowed to insert
   1           2          1        <- forbidden to insert/update, because there are two '1' in a row

Upvotes: 1

Views: 125

Answers (1)

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

Reputation: 50017

I think you're going to need to have the explicit comparisons. You can use a CHECK constraint to make the comparisons, as in

CREATE TABLE SOME_TABLE
  (COL1   NUMBER,
   COL2   NUMBER,
   COL3   NUMBER,
   COL4   NUMBER,
   COL5   NUMBER,
   COL6   NUMBER,
   COL7   NUMBER,
   COL8   NUMBER,
   COL9   NUMBER,
   COL10  NUMBER,
   CONSTRAINT SOME_TABLE_CK1
     CHECK(COL1  NOT IN (COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10) AND
           COL2  NOT IN (COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10) AND
           COL3  NOT IN (COL4, COL5, COL6, COL7, COL8, COL9, COL10) AND
           COL4  NOT IN (COL5, COL6, COL7, COL8, COL9, COL10) AND
           COL5  NOT IN (COL6, COL7, COL8, COL9, COL10) AND
           COL6  NOT IN (COL7, COL8, COL9, COL10) AND
           COL7  NOT IN (COL8, COL9, COL10) AND
           COL8  NOT IN (COL9, COL10) AND
           COL9  NOT IN (COL10)));

Share and enjoy.

Upvotes: 4

Related Questions