Trigger to avoid duplication of data in ORACLE

Question

Trigger to avoid duplication of data in ORACLE

Navigation

#1 by (1 votes)

0

I'm learning Oracle and I had a problem. I have my Chat table:

create table Charla (
   id_charla number,
   codigo_usuario number,
   fecha_charla date,
   fecha_fincharla date
)

Now, create a trigger so that it does not allow me to enter a "Chat" if a previous one was already registered with the same user_code. This is my trigger:

create or replace trigger DISCHARLA
before insert on CHARLA
for each row
begin
if :new.codigo_usuario = :old.codigo_usuario then
            Raise_Application_Error(-20099,'NO PUEDE REGISTRAR OTRA CHARLA SI AUN MANTIENE CHARLA VIGENTE.');
end if;
end DISCHARLA;

But still it still allows me to enter Talks with the same user code. Any help or recommendation to improve my trigger that does not work.

Thank you.

oracle plsql

asked by Stalinn 02.08.2018 в 00:03

source

1 answer

Replicas in kubernetes between nodes Routing in ANGULAR 5

score 1 · Accepted Answer

In an Insert trigger, the variables: old are null, only the variables: new have value of the new record according to each column or field of the table. On the line:

if :new.codigo_usuario = :old.codigo_usuario then

Always evaluate false (even if the two values have NULL, Null )

What you need is to create a unique index that has the field or column user_code , something like the following:

CREATE UNIQUE INDEX IDX_CHARLA_CODIGO_USUARIO
ON CHARLA (CODIGO_USUARIO);

And in your application or client program, catch the error DUP_VAL_ON_INDEX exception: ORA-00001. to show the appropriate message to the user. Or use a stored procedure to do the insertion and inside it trap the error mentioned above and use RAISE_APPLICATION_ERROR with the message for the end user.

Greetings