Crear un campo autoincrementable en ORACLE

Para los que nos iniciamos en los Gestores de Bases de Datos con SQL Server, la transición a ORACLE resulta siempre más dura que cuando se realiza el camino inverso. Realizando una analogía simple, en mi caso podría decir que T-SQL es a PL/SQL lo que C# es a C++. Más complicación, más potencia… y un sistema más “tiquismiquis” que los desarrollados por Microsoft.
SQL Server proporciona una forma muy sencilla de crear campos autonuméricos, generalmente utilizados en los IDs de las claves primarias de las tablas. Para crear un campo autoincrementable, lo único que teníamos que hacer era editar las propiedades de la columna que queríamos convertir en autoincrementable, acceder a sus propiedades y seleccionar la opción [Especificación de Identidad] marcando la casilla (Identidad) e informando del número de inicio del índice (Inicialización de identidad) y el número de unidades que se incrementará cada vez que se intente insertar un registro (Incremento de identidad).

Oracle nos pone las cosas un poco más difíciles, obligándonos a realizar dos pasos para realizar el mismo procedimiento: crear una secuencia y crear un trigger.

Creando una secuencia

Una secuencia, como su propio nombre indica, es un artefacto que devolverá un número incrementado en las unidades que le digamos cada vez que se consulte. Crear una secuencia es sencillo. La sintaxis para ello es la siguiente:


CREATE SEQUENCE nombre_secuencia
[INCREMENT BY unidades_incremento]
[START WITH valor_inicial]
[MINVALUE valor_minimo] || [NOMINVALUE]
[MAXVALUE valor_maximo] || [NOMAXVALUE];

Existen otras opciones, como CYCLE, CACHE u ORDER, pero para el ejemplo básico que queremos mostrar, será más que suficiente. Crearemos una secuencia llamada “Secuencia_Usuarios”, que comience en 1, se incremente de una unidad en una unidad y que no posea valores mínimos ni máximos. La sentencia PL/SQL para tal tarea sería la siguiente:


CREATE SEQUENCE Secuencia_Usuario
INCREMENT BY 1
START WITH 1
NOMINVALUE
NOMAXVALUE;

Debemos fijarnos en un detalle: es aconsejable crear una secuencia distinta para cada autoincremento que queramos programar. Esto se debe a que, si utilizamos la misma secuencia para varias tablas, cada vez que se inserte en una de ellas, el valor para el incremento será común a todas las tablas que hagan uso de ella.

Creando un Trigger

Un Trigger o Disparador es una accion programada que se ejecuta cuando se da una determinada condición (generalmente, una inserción, una modificación o una eliminación). Crearemos un Trigger que inserte el siguiente valor de la secuencia en el lugar del ID autoincrementable, para lo cual, de forma coloquial, le diremos lo siguiente a nuestra base de datos:

Antes de insertar una fila en la tabla Usuario


almacena temporalmente la fila que vas a insertar,


guarda el siguiente valor de la secuencia en una variable


y rellena el campo IdUsuario con ese valor.

A continuación, procede con la inserción.

Lo cual, en PL/SQL, se traduciría en el siguiente código:


CREATE OR REPLACE TRIGGER Trigger_Usuario_Id
BEFORE INSERT ON Usuario
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE valorSecuencia NUMBER := 0;
BEGIN
SELECT Secuencia_Usuario.NEXTVAL INTO valorSecuencia FROM DUAL;
:NEW.IdUsuario := valorSecuencia;
END;

Lo que le estamos diciendo a Oracle es lo siguiente:

Llegados a este punto, para los que estamos acostumbrados a T-SQL vemos algo extraño: además de asignar el valor a la variable, se añade una cláusula FROM que referencia a la tabla DUAL. ¿Qué es DUAL? DUAL es una tabla especial de ORACLE. A diferencia de T-SQL, PL/SQL no puede realizar sentencias SELECT sin su correspondiente FROM. La tabla DUAL es una tabla con una sola columna (llamada DUMMY) y un solo valor (generalmente, ‘X’). Así nos aseguramos de que recuperemos un solo valor, que es precisamente lo que queremos hacer. Por lo tanto, las siguientes sentencias T-SQL y PL/SQL serían equivalentes:


-- T-SQL:
SELECT @variable = 1;
SELECT @variable;

-- PL/SQL:
SELECT 1 INTO variable FROM DUAL;
SELECT variable FROM DUAL;

Finalizada la secuencia y el trigger, cada vez que se inserte una fila en nuestra tabla, un nuevo valor se añadirá automáticamente a nuestro campo Id.

2 Responses to “Crear un campo autoincrementable en ORACLE”

  1. ERNESTO - Abril 24, 2010

    Hola, tengo un error al crear un trigger que me dice que hace falta la palabra Clave ON, pero los errores de oracle no son muy descriptivos y pienso que es de sintaxis

    CREATE OR REPLACE TRIGGER MOVE_ONE

    AFTER INSERT MOVE
    FOR EACH NOW
    BEGIN

    IF(:new.MOVETYPEID=1)then

    UPDATE COUNT SET COUNTSALE=0 WHERE COUNT.COUNTID=:new.COUNTID;

    ELSE IF(:new.MOVETYPEID=2)then

    UPDATE COUNT SET COUNTSALE=COUNTSALEt:new.MOVEVALUE WHERE COUNT.COUNTID=:new.COUNTID;

    IF(:new.MOVEVALUE>50000000)then INSERT INTO AUDITORIA

    END IF

    END;

  2. Daniel García - Abril 26, 2010

    Me imagino que “MOVE” es la tabla en la que estás enlazando el Trigger. En ese caso, la palabra “ON” debería ir precisamente antes del nombre de la tabla:

    AFTER INSERT ON MOVE
    FOR EACH ROW
    BEGIN

    …

    END;

    Espero que te sirva. Un saludo.

Leave a Reply