CODEWITHSUNDEEP
sqloracle11gtriggersoracle-sqldevelopersql-insert
lcvalves
lcvalves

Reputation: 77

Oracle SQL Developer - Sequential trigger still increments when inserts output error

I'm creating a database, and in this table Utilizador I want the field codUtilizador, which is PK, to be sequential, insert after insert.

I created the trigger via editing the table, don't know much about coding triggers but i'll put the code down below.

What happens is whenever an inserts outputs an error, it still counts to the trigger, and the next one that inserts successfully, has a value different from it was supposed to be. (That's what i'm guessing)

Here's the code if it helps.
Tables:

CREATE TABLE TipoUtilizador (
CodTipoUtilizador Number(1) Primary key 
check (CodTipoUtilizador in ('0','1')),
desc_TipoUtilizador VARCHAR2(20) not null
); 


CREATE TABLE CPostal (
CodPostal VARCHAR2(8) Primary key
check (CodPostal like '____-___'),
desc_CodPostal VARCHAR2(30) not null
);

CREATE TABLE Utilizador ( 
CodUtilizador Number(10) Primary key,
username VARCHAR2(15) Unique not null, 
password VARCHAR2(15) not null,
nCC Number(8) Unique not null,
nif Number(9) Unique not null,
nTelefone Number(9) Unique not null,
rua VARCHAR2(50) not null,
nPorta Number(3) not null,
CodPostal VARCHAR2(8) not null references CPostal(CodPostal), 
CodTipoUtilizador Number(1) not null references TipoUtilizador(CodTipoUtilizador)
);

Insert:

INSERT INTO Utilizador(username, password, nCC, nif, nTelefone, rua, nPorta, CodPostal, CodTipoUtilizador)
    VALUES ('lcva2', 'lcva123', '87654321', '222222222', '999999999', 'Rua Alberto Campos', '13', '4600-102', '0');

Script output (I inserted the 2nd one wrong on purpose so i could show you the problem):

1 row inserted.


Error starting at line : 39 in command -
INSERT INTO Utilizador(username, password, nCC, nif, nTelefone, rua, nPorta, CodPostal, CodTipoUtilizador) VALUES ('lcva', 'lcva123', '12345678', '111111111', '913748456', 'Rua Alberto Campos', '13', '4600-102', '1')
Error report -
ORA-00001: unique constraint (LUIS.SYS_C007911) violated


1 row inserted.

Trigger:

create or replace TRIGGER UTILIZADOR_TRG 
BEFORE INSERT ON UTILIZADOR 
FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF INSERTING AND :NEW.CODUTILIZADOR IS NULL THEN
      SELECT UTILIZADOR_SEQ.NEXTVAL INTO :NEW.CODUTILIZADOR FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;

This is how i added the trigger

Result:

On row 2, the codUtilizador field should be 2, not 3

Upvotes: 0

Views: 114

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Constraints are checked after the before insert trigger is fired. So the sequence value is updated when the insert fails. The rollback of the transaction doesn't affect the sequence.

The reason for doing the check after the trigger is simple: the trigger might change values in the row and these need to meet the constraints.

This is explained in the documentation. The relevant part is:

Loop for each row affected by the SQL statement.

a. Run all BEFORE row triggers that apply to the statement.

b. Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)

c. Run all AFTER row triggers that apply to the statement.

Sequences are not locked by the transaction. Locking them could have a serious impact on performance, when there are multiple insert/updates occurring at the same time.

Upvotes: 1

Related Questions