Hi, I have some little problem with my exercises: i have these two tables
CREATE TABLE PRENOTAZIONI (
CodPrenotazioni NUMBER(5) PRIMARY KEY,
DataPrenotazioni DATE DEFAULT SYSDATE NOT NULL,
CodProg NUMBER (3) NOT NULL,
CodUtente VARCHAR2 (20) NOT NULL
)
STORAGE(INITIAL 128M NEXT 64M MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE POSTI(
CodPosto NUMBER (3) PRIMARY KEY,
Fila VARCHAR2 (1) CHECK (Fila='A' OR Fila='B' OR Fila='C' OR Fila='D' OR Fila='E' OR Fila='F' OR Fila='G' OR Fila='H' OR Fila='I' OR Fila='L' OR Fila='M' OR Fila='M' OR Fila='N' OR Fila='O' OR Fila='P' OR Fila='Q' OR Fila='R' OR Fila='S' OR Fila='T' OR Fila='U' OR Fila='V' OR Fila='Z') NOT NULL,
Numero NUMBER (2) NOT NULL,
CodSala NUMBER (2) NOT NULL,
CodPren NUMBER (4) NOT NULL
)
STORAGE(INITIAL 128K NEXT 64K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

In the table POSTI i have the PK codPosto and other values that can be the same and this is the problem: i want to have only different row. For example:
INSERT INTO POSTI VALUES('1','A','1','01','000');
INSERT INTO POSTI VALUES('2','A','1','01','000');
For me is an error, because the insert for one place must be unique; to resolve this problem i have thinked a trigger, this trigger:

create or replace TRIGGER POSTI
BEFORE INSERT ON POSTI
FOR EACH ROW
DECLARE
CURSOR RAG_CURSOR IS
SELECT CODPOSTO,FILA,NUMERO,CODSALA,CODPREN FROM POSTI;
VAL_RAG RAG_CURSOR %ROWTYPE;
BEGIN
FOR VAL_RAG IN RAG_CURSOR
LOOP
IF((:NEW.FILA=VAL_RAG.FILA)AND(:NEW.NUMERO=VAL_RAG.NUMERO)AND(:NEW.CODSALA=VAL_RAG.CODSALA))
THEN DBMS_OUTPUT.PUT_LINE('Tupla già inserita');
END IF;
END LOOP;
END;

The trigger compile, but if i try to enter two rows egual with two different key, the trigger doesn't do its work.
Can you help me?

SQL> CREATE TABLE POSTI(
  2  CodPosto NUMBER (3) PRIMARY KEY,
  3  Fila VARCHAR2 (1) CHECK (Fila='A' OR Fila='B' OR Fila='C' OR Fila='D' OR Fila='E' OR Fila='F' OR Fila='G' OR Fila='H' OR Fila='I' OR Fila='L' OR Fila='M' OR Fila='M' OR Fila='N' OR Fila='O' OR Fila='P' OR Fila='Q' OR Fila='R' OR Fila='S' OR Fila='T' OR Fila='U' OR Fila='V' OR Fila='Z') NOT NULL,
  4  Numero NUMBER (2) NOT NULL,
  5  CodSala NUMBER (2) NOT NULL,
  6  CodPren NUMBER (4) NOT NULL
  7  )
  8  /

Table created.

SQL>
SQL> INSERT INTO POSTI VALUES('1','A','1','01','000');

1 row created.

SQL> INSERT INTO POSTI VALUES('2','A','1','01','000');

1 row created.

SQL>
SQL> delete from posti;

2 rows deleted.

SQL>
SQL> alter table posti add constraint posti_uk unique ( Fila, Numero, CodSala, CodPren );

Table altered.

SQL>
SQL> INSERT INTO POSTI VALUES('1','A','1','01','000');

1 row created.

SQL> INSERT INTO POSTI VALUES('2','A','1','01','000');
INSERT INTO POSTI VALUES('2','A','1','01','000')
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.POSTI_UK) violated


SQL>