Insert record automatically when doing Insert PL / SQL

Question

Insert record automatically when doing Insert PL / SQL

Navigation

#1 by (1 votes)

0

I'm starting to mess with the PL / SQL database and I'm having a hard time creating a trigger that when inserting a row in the table, put a value in my column whose name is token .

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

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

Follow 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;

pl-sql trigger

asked by anonymous 02.04.2018 / 23:00

1 answer

How to display the loading indicator on Swift 4 with WKViewWeb SQL query help

score 1 · Accepted Answer

In this case, you can use a BEFORE trigger instead of AFTER and assign the sequence directly to the field: 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 were possible, it would be interesting to pass the sequence (TOKEN_SEQ.NEXTVAL) already during the insert, so it would not need this trigger and the insert code would look something 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 );