Insert record automatically when doing a PL/SQL insert

Asked

Viewed 919 times

0

I’m starting to mess with databases PL/SQL and I’m having a hard time creating a Trigger when inserting a row in the table, the Trigger place a value in my column whose name is token.

I made a code with what I know of DB, but when I turn it presents the following error:

(ORA-04084: não pode alterar valores NEW para este tipo de gatilho)

Follows the code:

create TABLE BancoJulianaCadastro(
codUser integer,
nome varchar(200),
senha varchar(50),
login varchar(8),
email varchar(30),
cpf number(11)
token number(20));

alter table BancoJulianaCadastro add CONSTRAINT codUser_PK PRIMARY key (codUser);

create sequence token_seq
minvalue 1
maxvalue 99999
start with 1
increment by 1;



create or replace trigger token_tgr 
AFTER INSERT ON BancoJulianaCadastro

declare
  sequencial number;
begin
   select token_seq.NEXTVAL
    into sequencial
   from dual;  
  :new.token := sequencial;
end;
database pl-sql trigger

1 answer

1


In this case, you can use a Trigger BEFORE instead of AFTER and you can assign the Sequence directly to the :new.token. Something like this: 

CREATE OR REPLACE TRIGGER TOKEN_TGR
BEFORE INSERT ON BancoJulianaCadastro
FOR EACH ROW
DECLARE
BEGIN
   :NEW.TOKEN := TOKEN_SEQ.NEXTVAL;
END;

But if it is possible, it would be interesting to pass the Quence (TOKEN_SEQ.NEXTVAL) already during the Insert, so it would not need this Rigger and the Insert code would be more or less like this: 

INSERT INTO TESTE_TRIGGER (CODUSER, NOME, SENHA, LOGIN, EMAIL, CPF, TOKEN)
VALUES (1234, 'JO SOARES', 'PASSWORD', 'JOSOARES', '[email protected]', '99999999999', TOKEN_SEQ.NEXTVAL );

Browser other questions tagged database pl-sql trigger

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