Programación Avanzada TRANSACT SQL

Programación Avanzada TRANSACT SQL

• Las funciones encapsulan sentencias TRANSACT-SQL de uso frecuente.
• Aceptan parámetros, realizan acciones (como cálculos complejos) y devuelven un resultado (escalar o una tabla).

Uso de Funciones Definidas por el Usuario

• En TRANSACT-SQL como SELECT.
• En aplicaciones que llaman a la función.
• En la definición de otra función definida por el usuario.
• Para parametrizar una vista o mejorar la funcionalidad de una vista indizada.
• Para definir una columna en una tabla.
• Para definir una restricción CHECK en una columna.
• Para reemplazar un procedimiento almacenado.

Tipos de Funciones de Usuario

• Funciones Escalares
• Funciones con valores de tabla de varias instrucciones
• Funciones con valores de tabla en línea

Funciones Escalares

• Retornan un único valor (int, money, varchar, real, etc.).
• Se pueden usar en cualquier lugar, incluso dentro de sentencias SQL.
• Sintaxis:

CREATE FUNCTION [PROPIETARIO.] NOMBRE_FUNCION
([{@PARAMETER TIPO DE DATO [=DEFAULT]} [,..N]])
RETURNS VALOR_ESCALAR
[AS]
BEGIN
CUERPO DE LA FUNCION
RETURN EXPRESION_ESCALAR
END

• Ejemplo: Función que retorna el precio promedio de todos los productos:

CREATE FUNCTION DBO.PRECIOPROMEDIO() RETURNS DECIMAL
AS
BEGIN
DECLARE @PROM DECIMAL
SELECT @PROM=AVG(PRECIOUNIDAD)
FROM COMPRAS.PRODUCTOS
RETURN @PROM
END
GO
- - MOSTRAR EL RESULTADO
PRINT DBO.PRECIOPROMEDIO()

• Ejemplo: Función que retorna la cantidad de pedidos registrados en el presente año para un empleado dado su ID:

CREATE FUNCTION DBO.PEDIDOSEMPLEADO(@ID INT) RETURNS DECIMAL
AS
BEGIN
DECLARE @Q DECIMAL=0
SELECT @Q=COUNT(*)
FROM VENTAS.PEDIDOSCABE
WHERE YEAR(FECHAPEDIDO)=YEAR(GETDATE()) AND IDEMPLEADO=@ID
IF @Q IS NULL
 SET @Q=0
RETURN @Q
END
GO
- - MOSTRAR EL RESULTADO DEL EMPLEADO DE CODIGO 4
PRINT DBO.PEDIDOSEMPLEADO(4)

Funciones de Tabla en Línea

• Devuelven la salida de una simple declaración SELECT.
• La salida se puede utilizar dentro de JOINS o queries como si fuera una tabla estándar.
• Sintaxis:

CREATE FUNCTION [propietario.] nombre_funcion
([{ @parameter tipo de dato [ = default]} [,..n]])
RETURNS TABLE
[AS]
RETURN [(] Sentencia SQL [)]

• Ejemplo: Función que lista los clientes e incluye el nombre del país:

CREATE FUNCTION DBO.CLIENTES()
RETURNS TABLE
AS
RETURN (SELECT
IDCLIENTE AS 'CODIGO',
NOMBRECIA AS 'CLIENTE',
DIRECCION,
NOMBREPAIS AS 'PAIS'
 FROM VENTAS.CLIENTES C JOIN VENTAS.PAISES P
 ON C.IDPAIS = P.IDPAIS)
GO -- EJECUTANDO LA FUNCION
SELECT * FROM DBO.CLIENTES() WHERE PAIS='CHILE'
GO

• Ejemplo: Función que lista los registros de los pedidos por un determinado año, incluye el nombre del producto, el precio que fue vendido y la cantidad vendida:

CREATE FUNCTION DBO.PEDIDOSAÑO(@Y INT)
RETURNS TABLE
AS
RETURN (SELECT PC.IDPEDIDO AS 'PEDIDO',
FECHAPEDIDO,
NOMBREPRODUCTO,
PD.PRECIOUNIDAD AS '¨PRECIO',
CANTIDAD
FROM VENTAS.PEDIDOSCABE PC
JOIN VENTAS.PEDIDOSDETA PD ON PC.IDPEDIDO=PD.IDPEDIDO
JOIN COMPRAS.PRODUCTOS P ON PD.IDPRODUCTO=P.IDPRODUCTO
WHERE YEAR(FECHAPEDIDO) = @Y)
GO -- EJECUTANDO LA FUNCION
SELECT * FROM DBO.PEDIDOSAÑO(2010)
GO

Funciones de Tabla de Multisentencias

• Similares a los procedimientos almacenados, pero devuelven una tabla.
• Se usan donde se requiere más lógica y procesamiento.
• Sintaxis:

CREATE FUNCTION [propietario.] nombre_funcion
([{@parameter  tipo de dato [ = default]} [,..n]])
RETURNS TABLE
[AS]
BEGIN
Cuerpo de la función
RETURN
END

• Ejemplo: Función que retorna el inventario de los productos registrados en la base de datos:

CREATE FUNCTION DBO.INVENTARIO()
RETURNS @TABLA TABLE(IDPRODUCTO INT,
   NOMBRE VARCHAR(50),
   PRECIO DECIMAL,
   STOCK INT)
AS
BEGIN
INSERT INTO @TABLA
SELECT IDPRODUCTO,
NOMBREPRODUCTO,
PRECIOUNIDAD,
UNIDADESENEXISTENCIA
FROM COMPRAS.PRODUCTOS
RETURN
END
GO -- EJECUTANDO LA FUNCION
SELECT * FROM DBO.INVENTARIO()
GO

• Ejemplo: Función que genera un reporte de ventas por empleado en un año dado, retornando datos del empleado, cantidad de pedidos y monto total:

CREATE FUNCTION DBO.REPORTEVENTAS(@Y INT)
RETURNS @TABLA TABLE(ID INT,
NOMBRE VARCHAR(50),
CANTIDAD INT,
MONTO DECIMAL)
AS
BEGIN
INSERT INTO @TABLA
SELECT E.IDEMPLEADO,
APELLIDOS,
COUNT(*),
SUM(PRECIOUNIDAD*CANTIDAD)
FROM VENTAS.PEDIDOSCABE PC JOIN VENTAS.PEDIDOSDETA PD
ON PC.IDPEDIDO = PD.IDPEDIDO JOIN VENTAS.EMPLEADOS E
ON E.IDEMPLEADO = PC.IDEMPLEADO
WHERE YEAR(FECHAPEDIDO) = @Y
GROUP BY E.IDEMPLEADO, APELLIDOS
RETURN
END
GO -- IMPRIMIR EL REPORTE DEL AÑO 2010
SELECT * FROM DBO.REPORTEVENTAS(2010)
GO

Limitaciones de Funciones Definidas por el Usuario

• No todas las sentencias SQL son válidas dentro de una función.
• Válido:
  • Sentencias de asignación.
  • Sentencias de Control de Flujo.
  • Sentencias SELECT y modificación de variables locales.
  • Operaciones de cursores sobre variables locales.
  • Sentencias INSERT, UPDATE, DELETE con variables locales.
• Inválido:
  • Armar funciones no determinadas como GetDate().
  • Sentencias de modificación o actualización de tablas o vistas.
  • Operaciones CURSOR FETCH que devuelven datos del cliente.

Procedimientos Almacenados

• Son grupos formados por instrucciones SQL y el lenguaje de control de flujo.
• Se prepara un plan de ejecución para que la subsiguiente ejecución sea muy rápida.
• Pueden:
  • Incluir parámetros.
  • Llamar a otros procedimientos.
  • Devolver un valor de estado a un procedimiento de llamada o lote para indicar el éxito o el fracaso del mismo y la razón de dicho fallo.
  • Devolver valores de parámetros a un procedimiento de llamada o lote.
  • Ejecutarse en SQL Server remotos.
• Mejoran la potencia, eficacia y flexibilidad de SQL.
• Los procedimientos compilados mejoran la ejecución de las instrucciones y lotes de SQL de forma dramática.
• Los procedimientos almacenados se crean con CREATE PROCEDURE.
• Para ejecutar un procedimiento almacenado, ya sea un procedimiento del sistema o uno definido por el usuario, use el comando EXECUTE.
• Sintaxis para crear un procedimiento almacenado:

CREATE PROCEDURE
   -- Añadir parámetros al procedimiento almacenado
     = ,
     =
AS
BEGIN
   -- Insertar la sentencia para el procedimiento
   Sentencia SQL
END

• Sintaxis para modificar un procedimiento almacenado:

ALTER PROCEDURE NOMBRE_PROCEDIMIENTO
    = ,
    =
AS
CONSULTA_SQL

• Sintaxis para eliminar un procedimiento almacenado:

DROP PROCEDURE NOMBRE_PROCEDIMIENTO

• Ejemplo: Procedimiento almacenado que lista todos los clientes:

- - PROCEDIMIENTO ALMACENADO
CREATE PROCEDURE USP_CLIENTES
AS
SELECT IDCLIENTE AS CODIGO,
 NOMBRECIA AS CLIENTE,
 DIRECCION,
 TELEFONO
FROM VENTAS.CLIENTES
GO
- - EJECUTANDO EL PROCEDIMIENTO ALMACENADO
EXEC USP_CLIENTES
GO
- - O simplemente
USP_CLIENTES
GO

• Ejemplo: Procedimiento almacenado que busca los datos de los pedidos registrados en una determinada fecha:

CREATE PROCEDURE USP_PEDIDOSFECHAS
@F1 DATETIME
AS
SELECT *
FROM VENTAS.PEDIDOSCABE
WHERE FECHAPEDIDO = @F1
GO
- - EJECUTANDO EL PROCEDIMIENTO ALMACENADO
EXEC USP_PEDIDOSBYFECHAS @F1='10-01-1996'
GO
- - O simplemente
EXEC USP_PEDIDOSBYFECHAS '10-01-1996'
GO

• La sentencia ALTER PROCEDURE permite modificar el contenido del procedimiento almacenado.
• Ejemplo: Modificación del procedimiento para consultar pedidos entre un rango de dos fechas:

ALTER PROCEDURE USP_PEDIDOSBYFECHAS
@F1 DATETIME,
@F2 DATETIME
AS
SELECT *
FROM VENTAS.PEDIDOSCABE
WHERE FECHAPEDIDO BETWEEN @F1 AND @F2
GO
- - Para ejecutar el procedimiento almacenado
EXEC USP_PEDIDOSBYFECHAS @F1='10-01-1996', @F2='10-10-1996'
GO

• Para eliminar un procedimiento almacenado, ejecute la instrucción DROP PROCEDURE:

DROP PROCEDURE USP_PEDIDOSBYFECHAS
GO

Especificar Parámetros en Procedimientos Almacenados

• Un procedimiento se comunica con el programa que lo llama mediante sus parámetros.
• Se pueden pasar valores al procedimiento mediante los parámetros.
• El procedimiento también puede devolver valores al programa mediante parámetros OUTPUT.
• Un procedimiento puede tener hasta 2100 parámetros.
• Cada parámetro debe tener un nombre, tipo de datos, dirección y valor predeterminado.

Especificar el Nombre del Parámetro

• Cada parámetro debe tener un nombre único.
• Los nombres deben empezar por @ y seguir las reglas de identificadores de objetos.

Especificar la Dirección del Parámetro

• La dirección puede ser de entrada o de salida.
• El valor predeterminado es un parámetro de entrada.
• Para especificar un parámetro de salida, se usa la palabra clave OUTPUT en la definición del parámetro.
• El programa que realiza la llamada también debe utilizar la palabra clave OUTPUT al ejecutar el procedimiento.

Especificar un Valor de Parámetro Predeterminado

• Se pueden crear procedimientos con parámetros opcionales especificando un valor predeterminado.
• Si no se especifica otro valor al ejecutar el procedimiento, se utilizará el valor predeterminado.
• Ejemplo: Procedimiento almacenado que muestra los datos de los pedidos, los productos que fueron registrados por cada pedido, el precio del producto y la cantidad registrada por un determinado cliente y año:

CREATE PROCEDURE USP_PEDIDOSCLIENTEAÑO
@ID VARCHAR(5),
@AÑO INT = 2011
AS
SELECT PC.IDPEDIDO AS 'PEDIDO',
FECHAPEDIDO, NOMBREPRODUCTO,
PD.PRECIOUNIDAD AS '¨PRECIO',
CANTIDAD
FROM VENTAS.PEDIDOSCABE PC JOIN VENTAS.PEDIDOSDETA PD
ON PC.IDPEDIDO = PD.IDPEDIDO JOIN COMPRAS.PRODUCTOS P
ON PD.IDPRODUCTO = P.IDPRODUCTO
WHERE YEAR(FECHAPEDIDO) = @AÑO
AND IDCLIENTE = @ID
GO
- - Podemos ejecutar el procedimiento enviando solo el valor para @id
EXEC USP_PEDIDOSCLIENTEAÑO @ID='ALFKI'
GO
- - O enviando los valores a los dos parámetros
EXEC USP_PEDIDOSCLIENTEAÑO @ID='ALFKI', @AÑO=1997
GO

• Ejemplo: Procedimiento almacenado que retorna la cantidad de pedidos y el monto total de pedidos, registrados por un determinado empleado y en determinado año:

CREATE PROCEDURE USP_REPORTEPEDIDOSEMPLEADO
@ID INT,
@Y INT,
@Q INT OUTPUT,
@MONTO DECIMAL OUTPUT
AS
SELECT @Q= COUNT(*),
@MONTO = SUM(PRECIOUNIDAD*CANTIDAD)
FROM VENTAS.PEDIDOSCABE PC JOIN VENTAS.PEDIDOSDETA PD
ON PC.IDPEDIDO = PD.IDPEDIDO
WHERE IDEMPLEADO =@ID AND YEAR(FECHAPEDIDO) = @Y
GO
- - Al ejecutar, primero declaramos las variables de retorno y luego, las variables de retorno se le indicara con la expresión OUTPUT.
DECLARE @Q INT, @M DECIMAL
EXEC USP_REPORTEPEDIDOSEMPLEADO @ID=2,
@Y=1997,
@Q=@Q OUTPUT,
@MONTO=@M OUTPUT
GO
PRINT 'CANTIDAD DE PEDIDOS COLOCADOS:' + STR(@Q)
PRINT 'MONTO PERCIBIDO:'+STR(@M)
GO

Uso de Cursores en Procedimientos Almacenados

• Los cursores permiten llevar a cabo la misma tarea utilizando sólo una consulta que, de otro modo, requeriría varias.
• Todas las operaciones del cursor deben ejecutarse dentro de un solo procedimiento
• Un procedimiento almacenado no puede abrir, recobrar o cerrar un cursor que no esté declarado en el procedimiento
• Ejemplo: Procedimiento almacenado que imprime cada uno de los registros de los productos, donde al finalizar, visualice el total del inventario:

CREATE PROCEDURE USP_INVENTARIO
AS
- - DECLARACION DE VARIABLES PARA EL CURSOR
DECLARE @ID INT, @NOMBRE VARCHAR(255), @PRECIO DECIMAL, @ST INT,
@INV INT
SET @INV=0
- - DECLARACIÓN DEL CURSOR
DECLARE CPRODUCTO CURSOR FOR
SELECT  IDPRODUCTO,
NOMBREPRODUCTO,
PRECIOUNIDAD,
UNIDADESENEXISTENCIA
FROM COMPRAS.PRODUCTOS
- - APERTURA DEL CURSOR
OPEN CPRODUCTO
- - LIBERAR LOS RECURSOS
DEALLOCATE CPRODUCTO
- - LECTURA DE LA PRIMERA FILA DEL CURSOR
FETCH CPRODUCTO INTO @ID, @NOMBRE, @PRECIO, @ST
WHILE (@@FETCH_STATUS = 0 )
BEGIN
- - IMPRIMIR
PRINT STR(@ID) + SPACE(5) + @NOMBRE + SPACE(5) +
STR(@PRECIO) + SPACE(5) + STR(@ST)
- - ACUMULAR
SET @INV += @ST
- - LECTURA DE LA SIGUIENTE FILA DEL CURSOR
FETCH CPRODUCTO INTO @ID, @NOMBRE, @PRECIO, @ST
END
- - CIERRE DEL CURSOR
CLOSE CPRODUCTO
PRINT 'INVENTARIO DE PRODUCTOS:' + STR(@INV)
GO

• Ejemplo: Procedimiento que genera un reporte de los pedidos realizados por un empleado en cada año, totalizando el monto de sus operaciones por cada año.

CREATE PROCEDURE USP_REPORTEPEDIDOSXAÑOXEMPLEADO
@EMP INT=1
AS
- - DECLARACIÓN DE VARIABLES DE TRABAJO
DECLARE  @Y INT, @Y1 INT, @PEDIDO INT, @MONTO DECIMAL, @TOTAL DECIMAL
SET @TOTAL=0
- - DECLARACIÓN DEL CURSOR
DECLARE MI_CURSOR CURSOR FOR
SELECT YEAR(FECHAPEDIDO) AS 'AÑO',
  PC.IDPEDIDO,
  SUM(PRECIOUNIDAD*CANTIDAD) AS MONTO
FROM VENTAS.PEDIDOSCABE PC
JOIN VENTAS.PEDIDOSDETA PD
ON PC.IDPEDIDO=PD.IDPEDIDO
WHERE IDEMPLEADO = @EMP
GROUP BY YEAR(FECHAPEDIDO), PC.IDPEDIDO
ORDER BY 1
- - APERTURA DEL CURSOR
OPEN MI_CURSOR
- - LECTURA DEL PRIMER REGISTRO
FETCH MI_CURSOR INTO @Y, @PEDIDO, @MONTO
- - ASIGNACIÓN DEL VALOR INICIAL DE @Y EN LA VARIABLE @Y1
SET @Y1 = @Y
- - IMPRIMIR EL PRIMER AÑO
PRINT 'AÑO:' + CAST(@Y1 AS VARCHAR)
- - RECORRER EL CURSOS MIENTRAS HAYAN REGISTROS
WHILE @@FETCH_STATUS=0
BEGIN
IF(@Y = @Y1)
BEGIN
- - ACUMULAR
SET @TOTAL += @MONTO
END
ELSE
BEGIN
PRINT 'AÑO:' + CAST(@Y1 AS VARCHAR) + SPACE(2)+
'IMPORTE: ' + CAST(@TOTAL AS VARCHAR)
PRINT 'AÑO:' + CAST(@Y AS VARCHAR)
SET @Y1=@Y
SET @TOTAL=@MONTO
END
- - IMPRIMIR EL REGISTRO
PRINT CAST(@PEDIDO AS VARCHAR) + SPACE(5)+
CAST(@MONTO AS VARCHAR)
- - LECTURA DEL SIGUIENTE REGISTRO
FETCH MI_CURSOR INTO @Y, @PEDIDO, @MONTO
END
- - CERRAR EL CURSOR
CLOSE MI_CURSOR
- - LIBERAR EL RECURSO
DEALLOCATE MI_CURSOR;
PRINT ' AÑO:' + CAST(@Y1 AS VARCHAR) + SPACE(2)+ 'IMPORTE: ' +
STR(@TOTAL)
GO
- -Al ejecutar el procedimiento almacenado, se le envía el parámetro que representa el id del empleado, imprimiendo su record de ventas de pedidos por año
USP_REPORTEPEDIDOSXAÑOXEMPLEADO 2
GO

Modificar datos con procedimientos almacenados

• Los procedimientos almacenados pueden aceptar datos como parámetros de entrada y pueden devolver datos como parámetros de salida, conjuntos de resultados o valores de retorno.
• Adicionalmente, los procedimientos almacenados pueden ejecutar sentencias de actualización de datos: INSERT, UPDATE, DELETE
• Ejemplo, defina un procedimiento almacenado para insertar un registro de la tabla Clientes, en este procedimiento, definiremos parámetros de entrada que representan los campos de la tabla.

CREATE PROCEDURE USP_INSERTACLIENTE
@ID VARCHAR(5),
@NOMBRE VARCHAR(50),
@DIRECCION VARCHAR(100),
@IDPAIS CHAR(3),
@FONO VARCHAR(15)
AS
INSERT INTO VENTAS.CLIENTES(IDCLIENTE, NOMCLIENTE, DIRECCION,IDPAIS,
TELEFONO)
VALUES(@ID, @NOMBRE, @DIRECCION, @IDPAIS, @FONO)
GO
- -El ejecutar el procedimiento almacenado, se enviará la lista de los parámetros definidos en el procedimiento.
EXEC USP_INSERTACLIENTE 'ABCDE', 'JUAN CARLOS MEDINA',
    'CALLE 25 NO 123','006','5450555'
GO

• Ejemplo: Procedimiento almacenado que permite evaluar la existencia de un registro de empleado para insertar o actualizar sus datos:

CREATE PROCEDURE USP_ACTUALIZAEMPLEADO
@ID INT,
@NOMBRE VARCHAR(50),
@APELLIDO VARCHAR(50),
@FN DATETIME,
@DIRECCION VARCHAR(100),
@IDDIS INT,
@FONO VARCHAR(15),
@IDCARGO INT,
@FC DATETIME
AS
MERGE RRHH.EMPLEADOS AS TARGET
USING
(SELECT @ID, @NOMBRE, @APELLIDO, @FN, @DIRECCION, @IDDIS, @FONO,
@IDCARGO, @FC) AS SOURCE
(IDEMPLEADO, NOMEMPLEADO, APEEMPLEADO, FECNAC, DIRECCION, IDDISTRITO,
FONOEMPLEADO, IDCARGO, FECCONTRATA)
ON (TARGET.IDEMPLEADO = SOURCE.IDEMPLEADO)
WHEN MATCHED THEN
UPDATE RRHH.EMPLEADOS
SET NOMEMPLEADO=@NOMBRE, APEEMPLEADO=@APELLIDO, FECNAC=@FN,
 DIRECCION=@DIRECCION, IDDISTRITO=@IDDIS,
 FONOEMPLEADO=@FONO, IDCARGO=IDCARGO, FECCONTRATA=@FC
WHEN NOT MATCHED THEN
INSERT  INTO RRHH.EMPLEADOS VALUES(@ID, @NOMBRE, @APELLIDO,
@FN, @DIRECCION, @IDDIS, @FONO, @IDCARGO, @FC) ;
GO

TRANSACCIONES EN TRANSACT-SQL

• Una transacción es un conjunto de operaciones TRANSACT SQL que se ejecutan como un único bloque, es decir, si falla una operación TRANSACT SQL fallan todas.
• Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos.
• Si una transacción encuentra errores y debe cancelarse o revertirse, se borran todas las modificaciones de los datos.
• Ejemplo:

CREATE PROCEDURE USP_AGREGAPEDIDO -- PARÁMETROS DE PEDIDOSCABE
@IDPED INT,
@IDCLI VARCHAR(5),
@IDEMP INT,
@FECPED DATETIME,
@IDPROD INT,
@PRE DECIMAL,
@CANT INT
AS
- - AGREGANDO UN REGISTRO A PEDIDOSCABE
INSERT INTO VENTAS.PEDIDOSCABE(IDPEDIDO,IDCLIENTE,
IDEMPLEADO,FECHAPEDIDO)
VALUES(@IDPED, @IDCLI, @IDEMP, @FECPED)
- - AGREGANDO UN REGISTRO A PEDIDOSDETA
INSERT INTO VENTAS.PEDIDOSDETA(IDPEDIDO, IDPRODUCTO,
PRECIOUNIDAD,CANTIDAD, DESCUENTO)
VALUES(@IDPED, @IDPROD, @PRE, @CANT, 0)
- - DESCONTANDO EL STOCK DE PRODUCTOS
UPDATE COMPRAS.PRODUCTOS SET UNIDADESENEXISTENCIA -=@CANT
WHERE IDPRODUCTO = @IDPROD
GO

Transacciones implícitas y explicitas

• Para agrupar varias sentencias TRANSACT SQL en una única transacción, disponemos de los siguientes métodos:
• Transacciones explícitas: Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se termina explícitamente con una instrucción COMMIT o ROLLBACK.
• Transacciones implícitas: Se inicia automáticamente una nueva transacción cuando se ejecuta una instrucción que realiza modificaciones en los datos, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK.
• Sintaxis para el control de errores:

BEGIN TRY

END TRY
BEGIN CATCH

END CATCH

• Sintaxis para el control de las transacciones

- - Inicio de transacción con nombre
BEGIN TRAN NombreTransaccion

COMMIT TRAN NombreTransaccion--Confirmación de la transacción.
ROLLBACK TRAN NombreTransaccion--Reversión de la transacción.

• Ejemplo: Procedimiento Almacenado que Controla Inserción a la tabla cliente por medio de @@ERROR

CREATE PROCEDURE USP_INSERTACLIENTE
@ID VARCHAR(5),
@NOMBRE VARCHAR(50),
@DIRECCION VARCHAR(100),
@IDPAIS CHAR(3),
@FONO VARCHAR(15)
AS
BEGIN TRAN TCLIENTE
INSERT INTO VENTAS.CLIENTES(IDCLIENTE, NOMCLIENTE, DIRECCION,
        IDPAIS, TELEFONO)
VALUES(@ID, @NOMBRE, @DIRECCION, @IDPAIS, @FONO)
GO
IF @@ERROR = 0
BEGIN
COMMIT TRAN TCLIENTE
PRINT 'CLIENTE REGISTRADO'
END
ELSE
BEGIN
PRINT @@ERROR
ROLLBACK TRAN TCLIENTE
END
GO

• Ejemplo: Control de errores con Try-Catch

CREATE PROCEDURE USP_AGREGAPEDIDO

@IDPED INT,
@IDCLI VARCHAR(5),
@IDEMP INT,
@FECPED DATETIME,
@IDPROD INT,
@PRE DECIMAL,
@CANT INT
AS
- - INICIO DE LA TRANSACCION
BEGIN TRAN TPEDIDO
- - INICIO DEL CONTROL DE ERRORES
BEGIN TRY

- - AGREGANDO UN REGISTRO A PEDIDOSCABE
INSERT INTO
VENTAS.PEDIDOSCABE(IDPEDIDO,IDCLIENTE,IDEMPLEADO,FECHAPEDIDO)
VALUES(@IDPED, @IDCLI, @IDEMP, @FECPED)

- - AGREGANDO UN REGISTRO A PEDIDOSDETA
INSERT INTO VENTAS.PEDIDOSDETA(IDPEDIDO, IDPRODUCTO,
PRECIOUNIDAD,CANTIDAD, DESCUENTO)
VALUES(@IDPED, @IDPROD, @PRE, @CANT, 0)

- - DESCONTANDO EL STOCK DE PRODUCTOS
UPDATE COMPRAS.PRODUCTOS SET UNIDADESENEXISTENCIA -=@CANT
WHERE IDPRODUCTO = @IDPROD

- - CONFIRMANDO LA ACTUALIZACION
COMMIT TRAN TPEDIDO
PRINT 'PEDIDO REGISTRADO'
END TRY
BEGIN CATCH
PRINT @@ERROR
ROLLBACK TRAN TPEDIDO
END CATCH
GO

• Ejemplo: Procedimiento para realizar un backup a la tabla ClienteBAK, evaluando si existe el registro y si no, creandolo, caso contrario actualizandolo y/o eliminandolo.

- - CREAR LA TABLA CLIENTESBAK
CREATE TABLE VENTAS.CLIENTESBAK (
 IDCLIENTE VARCHAR(5) PRIMARY KEY,
 NOMBRECLIENTE VARCHAR(40) NOT NULL,
 DIRECCION VARCHAR(60) NOT NULL,
 IDPAIS CHAR(3),
 TELEFONO VARCHAR(24) NOT NULL
)
GO
- - PROCEDIMIENTO QUE REALIZA BACK UP A LA TABLA CLIENTES
CREATE PROCEDURE USP_CLIENTEBAK
AS
BEGIN TRAN BK
BEGIN TRY
MERGE VENTAS.CLIENTESBAK AS TARGET
USING VENTAS.CLIENTES AS SOURCE
ON (TARGET.IDCLIENTE = SOURCE.IDCLIENTE)
WHEN MATCHED AND TARGET.NOMBRECLIENTE <> SOURCE.NOMCLIENTE THEN
 UPDATE SET TARGET.NOMBRECLIENTE = SOURCE.NOMCLIENTE,
 TARGET.DIRECCION = SOURCE.DIRCLIENTE, TARGET.IDPAIS =
SOURCE.IDPAIS, TARGET.TELEFONO=SOURCE.FONOCLIENTE
WHEN NOT MATCHED THEN
INSERT  VALUES(SOURCE.IDCLIENTE,SOURCE.NOMCLIENTE,
SOURCE.DIRCLIENTE, SOURCE.IDPAIS, SOURCE.FONOCLIENTE)
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
PRINT 'TRANSACCION COMPLETADA'
COMMIT TRAN BK
END TRY
BEGIN CATCH
PRINT @@ERROR
ROLLBACK TRAN
END CATCH
GO

TRIGGERS O DISPARADORES

• Los disparadores permiten realizar cambios “en cascada” en tablas relacionadas, imponer restricciones de columna más complejas que las permitidas por las reglas, compara los resultados de las modificaciones de datos y llevar a cabo una acción resultante.

Definición del disparador

• Un disparador es un tipo especial de procedimiento almacenado que se ejecuta cuando se insertan, eliminan o actualizan datos de una tabla especificada.
• Los disparadores pueden ayuda a mantener la integridad de referencia de los datos conservando la consistencia entre los datos relacionados lógicamente de distintas tablas.
• La principal ventaja de los disparadores es que son automáticos: funcionan cualquiera sea el origen de la modificación de los datos.
• Cada disparador es específico de una o más operaciones de modificación de datos, UPDATE, INSERT o DELETE.
• El disparador se ejecuta una vez por cada instrucción.

Creación de disparadores

• Un disparador es un objeto de la base de datos.
• Sintaxis para crear un disparador:

CREATE TRIGGER [ esquema. ]nombre_trigger
ON { Tabla | Vista }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
sentencia sql  [ ; ]

• Sintaxis para modificar el TRIGGER:

ALTER TRIGGER TX_PRODUCTOS
ON COMPRAS.PRODUCTOS
FOR INSERT, UPDATE, DELETE
AS
PRINT 'ACTUALIZACION DE LOS REGISTROS DE PRODUCTOS'

• Sintaxis para borrar un TRIGGER:

DROP TRIGGER TX_PRODUCTOS

Funcionamiento de los Disparadores

• A. Disparador de Inserción
  • Cuando se inserta una nueva fila en una tabla, SQL Server inserta los nuevos valores en la tabla INSERTED el cual es una tabla del sistema.
  • Cree un TRIGGER que permita insertar los datos de un Producto siempre y cuando la descripción o nombre del producto sea único.

CREATE TRIGGER TX_PRODUCTO_INSERTA
ON COMPRAS.PRODUCTOS
FOR INSERT
AS
IF
(SELECT  COUNT (*) FROM INSERTED, COMPRAS.PRODUCTOS
WHERE INSERTED.NOMPRODUCTO = PRODUCTOS.NOMPRODUCTO) >1
BEGIN
ROLLBACK TRANSACTION
PRINT 'LA DESCRIPCION DEL PRODUCTO SE ENCUENTRA REGISTRADO'
END
ELSE
PRINT 'EL PRODUCTO FUE INGRESADO EN LA BASE DE DATOS'
GO

• B. Disparador de Eliminación
  • Cuando se elimina una fila de una tabla, SQL Server inserta los valores que fueron eliminados en la tabla DELETED el cual es una tabla del sistema
  • Cree un TRIGGER el cual permita eliminar Clientes los cuales no han registrado algún pedido.

CREATE TRIGGER TX_ELIMINA_ELIMINA
ON VENTAS.CLIENTES
FOR DELETE
AS
IF EXISTS (SELECT * FROM VENTAS.PEDIDOSCABE
WHERE PEDIDOSCABE.IDCLIENTE = (SELECT IDCLIENTE FROM DELETED) )
BEGIN
ROLLBACK TRANSACTION
PRINT 'EL CLIENTE TIENE REGISTRADO POR LO MENOS 1 PEDIDOS'
END

• C. Disparador de Actualización
  • Cuando se actualiza una fila de una tabla, SQL Server inserta los valores que antiguos en la tabla DELETED y los nuevos valores los inserta en la tabla INSERTED
  • Cree un TRIGGER que valide el precio unitario y su Stock de un producto, donde dichos datos sean mayores a cero.

CREATE TRIGGER TX_PRODUCTO_ACTUALIZA
ON COMPRAS.PRODUCTOS
FOR UPDATE
AS
IF (SELECT PRECIOUNIDAD  FROM INSERTED)