Create Trigger for Quence in Primary key

Asked

Viewed 1,924 times

2

I have the following table in oracle:

table guest 

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’t create Trigger. The sequence is already created. How I create Trigger related to the id_hospede to increase at the time of insert?

oracle

2 answers

2

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’re not 12c, I don’t see what’s wrong with Trigger. It makes a mistake?

By the way, try this code:

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

  • i just don’t know how I refer the id_hospede to make the auto increement at the time of the Insert.

  • In the first example I gave, no references to column: Insert into identity_test_tab(Description) values('test');

  • Sorry, I forgot to mention the excerpt. I’m talking about the setundo excerpt of your answer, which speaks of nextval. Puts a full example of the insertion in the id_hospede, name, for example.

0

Good afternoon André,

Sequence already acts as a Trigger. At the time of Insert you can call passing the nextval in values:

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

    did not understand. I use sec_hospdede_1 nextval at the time of Insert? Give an example, please.

  • INSERT INTO nameTable (seq_hospede_1) VALUES (seq_hospede_1.nextval);

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.