Create trigger for sequence in primary key

Question

Create trigger for sequence in primary key

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I have the following table in oracle:

host table

id_hospede  
nome  
login  
senha  
rg  
cpf  
telefone

sequence:

create sequence seq_hospede_1
start with 1
increment by 1
maxvalue 1000
minvalue 1
nocache
cycle;

trigger:

CREATEOR REPLACE TRIGGER dept_bir  
BEFORE INSERTON hospede  
FOR EACH ROWBEGINSELECT dept_seq.NEXTVAL  
INTO:new.id  
FROM dual;  
END;

I just can not create the trigger. The sequence is already created. How do I create the trigger related to id_hospede to increment at time of insert ?

oracle

asked by anonymous 11.11.2016 / 17:33

2 answers

Optimizing default middleware behavior Is it possible to change the color of Marker marker in Google Maps?

score 1 · Answer 1

What is your Oracle version? If it is 12c, there is now identiy column, which does the increment for you, without triggers, nor sequences:

CREATE TABLE identity_test_tab (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(30)
);

If you do not have 12c, I do not see what the trigger is wrong. Does it make a mistake?

Now try this code:

CREATE OR REPLACE TRIGGER dept_bir  
BEFORE INSERT ON hospede  
FOR EACH ROW 
BEGIN
   :new.id  :=  dept_seq.NEXTVAL;
END;

score 0 · Answer 2

Good afternoon André,

The sequence already acts as a trigger. At the time of insert you can call by passing the nextval in values:

INSERT INTO nomeTabela (seq_hospede_1) VALUES (seq_hospede_1.nextval);