En la mayoría de nuestras aplicaciones tenemos la necesidad de poder generar el numero de nuestros comprobantes (Ordenes de compra, pedidos de ventas, etc.)
Existen diferentes técnicas para poder realizar esta operación. En los ejemplos de este post mostrare las dos formas mas tradicionales de hacerlo y cuales son sus pros y contras de cada una.
Para estos ejemplos crearemos una base de datos nueva con la siguiente sentencia
CREATE
DATABASE EJEMPLO_NUMERACION Usando auto numéricos
Dentro de SQLserver podemos definir en un tipo de dato entero la posibilidad de que sea autoincrementar, esto hará que de forma automática el motor de base de datos calcule el valor que debe ir en ese campo. Usando esta posibilidad podríamos definir que en nuestros comprobantes el campo de numero sea auto numérico.
El siguiente ejemplo ilustra como hacer esta operación
USE
EJEMPLO_NUMERACION GO
CREATE
TABLE OC (ID INT IDENTITY, FECHA DATETIME NOT NULL, CLIENTE VARCHAR(50) NOT NULL)
GO
Como se podrá observar el campo ID es de tipo int y a su vez tiene la propiedad de ser autoincremental, el resto de los campos corresponden a datos necesarios de la Orden de compra de este ejemplo.
Si queremos insertar un nuevo registro (o sea una nueva orden de compra) simplemente hacemos lo siguiente
INSERT
INTO OC (FECHA,CLIENTE) VALUES
(GETDATE(),'IBM') go
Si luego de esto hacemos un select a la tabla OC y vemos los registros podremos observar que se ha generado de forma automática el ID 1, si hacemos otro insert valido veremos que se generara el 2 y así sucesivamente.
Ahora bien, que sucede si por ejemplo hacemos un insert y no se pudo realizar la transacción, por ejemplo queremos insertar una OC donde el campo Cliente sea nulo lo cual no esta permitido por la integridad de la base de datos.
INSERT
INTO OC (FECHA,CLIENTE) VALUES
(GETDATE(),NULL) go
Al hacer esta instrucción recibiremos el siguiente error:
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'CLIENTE', table 'EJEMPLO_NUMERACION.dbo.OC'; column does not allow nulls. INSERT fails.
Bien, el registro si hacemos un select a la tabla no se ha generado y eso es correcto ya que ha existido un problema.
Ahora bien, hasta el momento solo tenemos generada la OC con ID = 1 y la 2 ha fallado. Pues arreglamos el tema del null en el insert para que se genere la OC = 2 ya que es la que continua luego de la 1.
INSERT
INTO OC (FECHA,CLIENTE) VALUES
(GETDATE(),'MICROSOFT') go
El registro se genero ahora "correctamente", pero si hacemos un select a la tabla nos encontraremos que ahora tenemos el ID = 1 y el ID = 3, con lo cual estamos teniendo huecos en nuestra numeración.
Como se podrá observar es un grave problema tener estos huecos en los números de los comprobantes (imagine que fueran facturas por ejemplo), pues esto se debe a como funciona el auto númerico de SQLServer, es simple de usar e implementar pero hay que saber lo de las transacciones, ante una falla el numero lo reservo igual y considero que se genero con lo cual lo salta [:(]
Usando tabla numeradora
Otra alternativa a la que vimos es la de generar una tabla donde tendremos los números de los comprobantes y usarla para numerar uno nuevo, si la transacción fallo simplemente hay que tener el recaudo de no cambiar el valor en la tabla numeradora.
Entonces, lo primero que generamos es esta tabla numeradora
CREATE
TABLE NUMERADOR (TIPO_COMPROBANTE VARCHAR(100), ULTIMO_NUMERO INT)
GO
INSERT
INTO NUMERADOR VALUES ('OC',1) INSERT
INTO NUMERADOR VALUES ('FACTURAS',10) GO
La tabla numeradora podrá ser utilizada por distintos comprobantes, de ahí es que tiene un campo denominado tipo_comprobante y otro ultimo_numero.
Como también se puede observar se le han insertado 2 registros, uno para los comprobantes de Ordenes de Compra y el otro para facturas.
Ahora bien, lo que debemos hacer como siguiente paso es utilizar esta tabla numeradora para obtener el ultimo_numero sumarle 1 y asignarlo a la OC que estamos generando. Aquí entran a jugar una serie mas de detalles a conocer, si hacemos un Select (sin cambiar el nivel de bloqueo) y otro usuario en otro sitio hace lo mismo podrán obtener el mismo ultimo_numero lo cual al sumarle 1 los dos intentaran hacer la misma OC.
Para evitar este tipo de situaciones, ya que no solo necesito numerar sino también asegurar que no exista la posibilidad de obtener números duplicados (la misma OC por ejemplo) es que vamos a hacer uso de algunos trucos de bloqueos y variables.
DROP
TABLE OC GO
CREATE
TABLE OC (NUMERO INT, FECHA DATETIME,CLIENTE VARCHAR(50) NOT NULL) GOdeclare @proximo_numero int begin try begin
tran update numerador set @proximo_numero = ultimo_numero = ultimo_numero + 1
where tipo_comprobante = 'OC'
INSERT INTO OC VALUES (@proximo_numero,getdate(),'SQLTOTAL') commit tran
end try begin
catch rollback tran -- primero hacemos un rollback
print @@error -- mostramos el error generado
end
catch GO
La sentencia UPDATE no solo modifica la tabla sino que además actualiza la variable que luego utilizamos para generar el comprobante. De esta manera logramos que si hay 2 procesos haciendo esto por el uso de los bloqueos internos de SQL al hacer uso de la instrucción UPDATE, el segundo proceso no podrá hacer update hasta que el primero no haya terminado la transacción (ya sea por commit o rollback tran)
Si hacemos un select sobre OC vamos a observar que se ha generado el id numero 2, ahora bien si intentamos hacer un nuevo insert pero que falla y luego volvemos a insertar uno que no falla no tendremos los huecos en la numeración como nos ha sucedido con los identity.
Para probar que no se van a generar 2 números iguales en tiempos iguales hay que hacer la siguiente prueba con el Script.
1) Habrá dos ventanas de management studio o Query Analizer si usa 2000
2) En ambas copie por ejemplo este código
declare @proximo_numero int begin try begin
tran update numerador set @proximo_numero = ultimo_numero = ultimo_numero + 1
where tipo_comprobante = 'OC'
INSERT INTO OC VALUES (@proximo_numero,getdate(),'SQLTOTAL')
end try begin
catch rollback tran -- primero hacemos un rollback
print @@error -- mostramos el error generado
end
catch 3) Ejecute desde la primer ventana el script, luego hágalo desde la segunda. Como podrá observar en la segunda ventana se queda esperando y no procesa , esto se debe que hay bloqueos sobre la tabla numeradora y que el segundo update no ha podido ser realizado.
4) En la primer ventana escriba commit tran y ejecute solo eso
Al hacer esto podrá observar que la segunda ventana ha terminado.
5) Haga un select sobre OC y vea los resultados [:)]
Bueno con esto termino, espero que les haya sido de utilidad y espero sus comentarios
Filtrar por APML
Subscribir