Avoid entering duplicate data in an Oracle table

Question

Avoid entering duplicate data in an Oracle table

Navigation

#1 by (0 votes)

2

I would like to know how to create a Trigger in Oracle that prevents data from duplicating data in a table. To start I have my temporary table:

CREATE TABLE temporal(
Nombre varchar(50),
Tipo varchar(50),
Pais varchar(50)
)

That when you insert data is as follows

----------------------------------
Nombre     |  Tipo      | Pais
----------------------------------
User 1        2           USA
User 2        2           FRANCIA
User 3        1           ALEMANIA
User 4        2           FRANCIA
User 5        1           USA

Then I have a table called Pais

CREATE TABLE PAIS(idpais int,nombre varchar(50))

What I want is that when inserting in the temporary table, insert in the country table the data of the country field of that table but without entering repeated names since the idpais already works for me autonumerico

CREATE TRIGGER insertpais
AFTER INSERT ON temporal
BEGIN
INSERT INTO Pais(nombre) VALUES(:new.Pais)
END

This trigger works for me but I would insert twice the value of FRANCE and USA to the Country table so that is the part I would like to resolve

-------------------------
idpais      |   nombre
------------------------
   1              USA
   2             FRANCIA
   3             ALEMANIA
   4             FRANCIA
   5              USA

And I would like

-------------------------
idpais      |   nombre
------------------------
   1              USA
   2             FRANCIA
   3             ALEMANIA

sql oracle insert trigger

asked by Oscar Corleto Soto 02.01.2019 в 03:39

source

1 answer

Relationship tables laravel Translate the permalinks using the "qTranslate-X" plugin

score 0 · Accepted Answer

A simple way is to use a clause INSERT ... SELECT .. and in SELECT bring only the rows of temporal whose countries are not in 'COUNTRY:

CREATE TRIGGER insertpais
AFTER INSERT ON temporal
BEGIN
INSERT INTO PAIS(nombre)
SELECT  DISTINCT Pais
    FROM temporal
    WHERE Pais NOT IN (SELECT nombre FROM PAIS);
END;