BASE DE DATOS AVANZADO II 89

UNIDAD DE
APRENDIZAJE

PROGRAMACIÓN AVANZADA TRANSACT SQL

LOGRO DE LA UNIDAD DE APRENDIZAJE

• Al término de la unidad, el alumno, haciendo uso de las estructuras de

programación TRANSACT-SQL desarrolladas en clase, define e implementa
objetos de base de datos que permite realizar operaciones de consulta y
actualización de datos.

TEMARIO

1. Funciones definidas por el usuario

1.1. Funciones escalares
1.2. Funciones de tabla en línea
1.3. Funciones de tabla de multi sentencias
1.4. Limitaciones
2. Procedimientos almacenados
2.1. Especificar parámetros
2.2. Uso de cursores en procedimientos almacenados
2.3. Modificar datos con procedimientos almacenados
2.4. Transacciones en TRANSACT-SQL
2.4.1. Transacciones implícitas y explícitas
3. Triggers o disparadores
3.1. Definición de trigger
3.2. Creación de disparadores
3.2.1. Funcionamiento de los disparadores
3.3. Uso de INSTEAD OF

ACTIVIDADES PROPUESTAS

• Los alumnos programan objetos de base de datos para recuperar datos.

• Los alumnos programan objetos de base de datos utilizando TRANSACT-SQL para
recuperar y actualizar datos.

CIBERTEC CARRERAS PROFESIONALES

90

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 91

3.1 PROGRAMACIÓN AVANZADA TRANSACT SQL

3.1.1 Funciones definidas por el usuario

Las funciones son rutinas que permiten encapsular sentencias TRANSACT-SQL que
se ejecutan frecuentemente.

Las funciones definidas por el usuario, en tiempo de ejecución de lenguaje

TRANSACT-SQL o común (CLR), acepta parámetros, realiza una acción, como un
cálculo complejo, y devuelve el resultado de esa acción como un valor. El valor de
retorno puede ser un escalar (único) valor o una tabla.

Las funciones de usuario son definidas para crear una rutina reutilizables que se
pueden utilizar en las siguientes maneras:
• En TRANSACT-SQL como SELECT
• En las aplicaciones de llamar 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 a un procedimiento almacenado

Las funciones de usuario, según el tipo de retorno se clasifican en las siguientes:

1. Funciones Escalares
2. Funciones con valores de tabla de varias instrucciones
3. Funciones con valores de tabla en línea

3.1.1.1 Funciones escalares

Son aquellas funciones donde retornan un valor único: tipo de datos como int, Money,
varchar, real, etc. Pueden ser utilizadas en cualquier lugar, incluso, incorporada dentro
de las sentencias SQL.

CIBERTEC CARRERAS PROFESIONALES

92

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: Crear una función que retorne 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: Defina una función donde ingrese el id del empleado y retorne la cantidad de
pedidos registrados en el presente año

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

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 93

-- MOSTRAR EL RESULTADO DEL EMPLEADO DE CODIGO 4

PRINT DBO.PEDIDOSEMPLEADO(4)

3.1.1.2 Funciones de tabla en línea

Las funciones de tabla en línea son las funciones que devuelven la salida de una
simple declaración SELECT. La salida se puede utilizar adentro de JOINS o querys
como si fuera una tabla de estándar.

La sintaxis para una función de tabla en línea es como sigue:

CREATE FUNCTION [propietario.] nombre_funcion

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

Ejemplo: Defina una función que liste los registros de los clientes, e incluya el nombre
del Pais.

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

CIBERTEC CARRERAS PROFESIONALES

94

Ejemplo: Defina una función que liste los registros de los pedidos por un determinado
año, incluya 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

3.1.1.3 Funciones de tabla de multisentencias

Son similares a los procedimientos almacenados excepto que vuelven una tabla. Este
tipo de función se usa en situaciones donde se requiere más lógica y proceso. Lo que
sigue es la sintaxis para unas funciones de tabla de multisentencias:

CREATE FUNCTION [propietario.] nombre_funcion

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

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 95

Ejemplo: Defina una función que retorne 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: Defina una función que permita generar un reporte de ventas por empleado,
en un determinado año. En este proceso, la función debe retornar: los datos del
empleado, la cantidad de pedidos registrados y el monto total por empleado

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(*),

CIBERTEC CARRERAS PROFESIONALES

96

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

3.1.1.4 Limitaciones

Las funciones definidas por el usuario tienen algunas restricciones. No todas las
sentencias SQL son válidas dentro de una función. Las listas siguientes enumeran las
operaciones válidas e inválidas de las funciones:

Válido:
• Las sentencias de asignación
• Las 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álidas:
• 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

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 97

3.1.2 Procedimientos Almacenados

Los procedimientos almacenados son grupos formados por instrucciones SQL y el

lenguaje de control de flujo. Cuando se ejecuta un procedimiento, se prepara un plan
de ejecución para que la subsiguiente ejecución sea muy rápida. Los procedimientos
almacenados 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

La posibilidad de escribir procedimientos almacenados mejora notablemente 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. Además, los
procedimientos almacenados pueden ejecutarse en otro SQL Server si el servidor del
usuario y el remoto están configurados para permitir logins remotos.

Los procedimientos almacenados se diferencian de las instrucciones SQL ordinarias y

de lotes de instrucciones SQL en que están precompilados. La primera vez que se
ejecuta un procedimiento, el procesador de consultas SQL Server lo analiza y prepara
un plan de ejecución que se almacena en forma definitiva en una tabla de sistema.
Posteriormente, el procedimiento se ejecuta según el plan almacenado, puesto que ya
se ha realizado la mayor parte del trabajo de procesamiento de consultas, los
procedimientos almacenados se ejecutan casi de forma instantánea.

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. También, puede utilizar el nombre del
procedimiento almacenado solo, siempre que sea la primera palabra de una
instrucción o lote.

Sintaxis para crear un procedimiento almacenado:

CIBERTEC CARRERAS PROFESIONALES

98

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

-- Añadir parámetros al procedimiento almacenado
<@Param1> <Datatype_For_Param1> = <Default_Value_For_Param1>,
<@Param2> <Datatype_For_Param2> = <Default_Value_For_Param2>
AS
BEGIN
-- Insertar la sentencia para el procedimiento
Sentencia SQL
END

Sintaxis para modificar un procedimiento almacenado:

ALTER PROCEDURE NOMBRE_PROCEDIMIENTO

<@Param1> <Datatype_For_Param1> = <Default_Value_For_Param1>,
<@Param2> <Datatype_For_Param2> = <Default_Value_For_Param2>
AS
CONSULTA_SQL

Sintaxis para eliminar un procedimiento almacenado:

DROP PROCEDURE NOMBRE_PROCEDIMIENTO

Por ejemplo: Defina un procedimiento almacenado que liste todos los clientes

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

Como se aprecia, el procedimiento mostrado no tiene parámetros de entrada y para

ejecutarlo deberá usar una de las siguientes sentencias:

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 99

-- EJECUTANDO EL PROCEDIMIENTO ALMACENADO

EXEC USP_CLIENTES
GO

O simplemente

-- EJECUTANDO EL PROCEDIMIENTO ALMACENADO

USP_CLIENTES
GO

Por ejemplo: Cree un procedimiento almacenado que permita buscar los datos de los
pedidos registrados en una determinada fecha. El procedimiento deberá definir un
parámetro de entrada de tipo DateTime

CREATE PROCEDURE USP_PEDIDOSFECHAS

@F1 DATETIME
AS
SELECT *
FROM VENTAS.PEDIDOSCABE
WHERE FECHAPEDIDO = @F1
GO

Como se aprecia, el procedimiento mostrado tiene 1 parámetro de entrada y para

ejecutarlo deberá usar la siguiente sentencia:

-- EJECUTANDO EL PROCEDIMIENTO ALMACENADO

EXEC USP_PEDIDOSBYFECHAS @F1='10-01-1996'
GO

O simplemente, colocar la lista de los valores el cual será asignada a cada parámetro,
donde el primer valor le corresponde a @f1

-- EJECUTANDO EL PROCEDIMIENTO ALMACENADO

EXEC USP_PEDIDOSBYFECHAS '10-01-1996'
GO

CIBERTEC CARRERAS PROFESIONALES

100

La sentencia ALTER PROCEDURE permite modificar el contenido del procedimiento

almacenado. En este procedimiento, realizamos la consulta de 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

3.1.2.1 Especificar parámetros

Un procedimiento almacenado se comunica con el programa que lo llama mediante

sus parámetros. Cuando un programa ejecuta un procedimiento almacenado, es
posible pasarle valores mediante los parámetros del procedimiento.

Estos valores se pueden utilizar como variables estándar en el lenguaje de

programación TRANSACT-SQL. El procedimiento almacenado también puede
devolver valores al programa que lo llama mediante parámetros OUTPUT. Un
procedimiento almacenado puede tener hasta 2.100 parámetros, cada uno de ellos
con un nombre, un tipo de datos, una dirección y un valor predeterminado

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 101

3.1.2.2 Especificar el nombre del parámetro

Cada parámetro de un procedimiento almacenado, debe definirse con un nombre

único. Los nombres de los procedimientos almacenados deben empezar por un solo
carácter @, como una variable estándar de TRANSACT-SQL, y deben seguir las
reglas definidas para los identificadores de objetos. El nombre del parámetro se puede
utilizar en el procedimiento almacenado para obtener y cambiar el valor del parámetro.

3.1.2.3 Especificar la dirección del parámetro

La dirección de un parámetro puede ser de entrada, que indica que un valor se pasa al
parámetro de entrada de un procedimiento almacenado o de salida, que indica que el
procedimiento almacenado devuelve un valor al programa que lo llama mediante un
parámetro de salida. El valor predeterminado es un parámetro de entrada.
Para especificar un parámetro de salida, debe indicar la palabra clave OUTPUT en la
definición del parámetro del procedimiento almacenado. El programa que realiza la
llamada también debe utilizar la palabra clave OUTPUT al ejecutar el procedimiento
almacenado, a fin de guardar el valor del parámetro en una variable que se pueda
utilizar en el programa que llama.

3.1.2.4 Especificar un valor de parámetro predeterminado

Puede crear un procedimiento almacenado con parámetros opcionales especificando

un valor predeterminado para los mismos. Al ejecutar el procedimiento almacenado, se
utilizará el valor predeterminado si no se ha especificado ningún otro.
Es necesario especificar valores predeterminados, ya que el sistema devuelve un error
si en el procedimiento almacenado no se especifica un valor predeterminado para un
parámetro y el programa que realiza la llamada no proporciona ningún otro valor al
ejecutar el procedimiento.

Por ejemplo: Cree un procedimiento almacenado que muestre 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. El procedimiento recibirá
como parámetro de entrada el código del cliente y el año. Considere que el parámetro
año tendrá un valor por defecto: el año del sistema.

CIBERTEC CARRERAS PROFESIONALES

102

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

Como el procedimiento almacenado ha definido un valor por defecto al parámetro

@año, podemos ejecutar el procedimiento enviando solamente el valor para el
parámetro @id

EXEC USP_PEDIDOSCLIENTEAÑO @ID='ALFKI'

GO

O simplemente enviando los valores a los dos parámetros

EXEC USP_PEDIDOSCLIENTEAÑO @ID='ALFKI', @AÑO=1997

GO

Por ejemplo: Implemente un procedimiento almacenado que retorne la cantidad de

pedidos y el monto total de pedidos, registrados por un determinado empleado
(parámetro de entrada su id del empleado) y en determinado año (parámetro de
entrada). Dicho procedimiento retornará la cantidad de pedidos y el monto total

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 103

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 el procedimiento almacenado, primero declaramos las variables de retorno

y al ejecutar, 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

3.1.2.5 Uso de cursores en procedimientos almacenados

Los cursores son especialmente útiles en procedimientos almacenados. Permiten

llevar a cabo la misma tarea utilizando sólo una consulta que, de otro modo, requeriría
varias. Sin embargo, 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. El cursor no está definido fuera del
alcance del procedimiento almacenado.

CIBERTEC CARRERAS PROFESIONALES

104

Por ejemplo: Implemente un procedimiento almacenado que imprimir cada uno de los
registros de los productos, donde al finalizar, visualice el total del inventario (Suma de
cantidad de productos)

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

-- 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

-- LIBERAR LOS RECURSOS

DEALLOCATE CPRODUCTO

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 105

PRINT 'INVENTARIO DE PRODUCTOS:' + STR(@INV)

GO

En el siguiente ejemplo, definimos un procedimiemto que permita generar 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)

CIBERTEC CARRERAS PROFESIONALES

106

-- 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

CARRERAS PROFESIONALES CIBERTEC

107

Ejecutado el procedimiento almacenado

lista cada pedido por año, mostrando al
finalizar el total por cada año.

3.1.3 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

Por 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

CARRERAS PROFESIONALES CIBERTEC

108

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

En el siguiente ejemplo, definimos un procedimiento almacenado que permita evaluar

la existencia de un registro de empleado para insertar o actualizar sus datos: Si existe
el código del empleado, actualice sus datos; sino agregue el registro de empleados

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

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 109

Para este caso, hemos utilizado la instrucción MERGE que evalúa la existencia de un
registro. Si existe, actualizará los datos, sino agrega el registro.

En ocasiones, no sabemos si estamos realizando correctamente un proceso de

inserción, actualización o eliminación de datos a una tabla(s). El script de T-SQL
consiste en realizar un procedimiento almacenado que reciba los datos necesarios
para insertarlos en la tabla, para garantizar la ejecución correcta de las actualización
utilizo las transacciones “TRANSACT SQL” y para validar la reversión de la
transacción en caso de que ocurra un ERROR utilizo el control de Errores Try – Catch
con ROLLBACK.

3.1.4 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.

El ejemplo clásico de transacción es un registro de pedidos, donde agregamos un

registro a la tabla pedidoscabe y agregamos un registro a la tabla pedidosdeta
(producto solicitado por el cliente) y descontamos el stock del producto solicitado en el
pedido.

CREATE PROCEDURE USP_AGREGAPEDIDO

-- PARÁMETROS DE PEDIDOSCABE
@IDPED INT,
@IDCLI VARCHAR(5),
@IDEMP INT,
@FECPED DATETIME,

-- PARÁMETROS DE PEDIDOSDETA
@IDPROD INT,
@PRE DECIMAL,
@CANT INT
AS

CIBERTEC CARRERAS PROFESIONALES

110

-- 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

Esta forma de procesar las operaciones de pedidos sería errónea, ya que cada
instrucción se ejecutaría y confirmaría de forma independiente, por lo que un error en
alguna de las operaciones dejaría los datos erróneos en la base de datos.

3.1.4.1 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
/*Bloque de instrucciones a validar*/
END TRY

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 111

BEGIN CATCH
/*Bloque de instrucciones que se ejecutan si ocurre un ERROR*/
END CATCH

Sintaxis para el control de las transacciones

-- Inicio de transacción con nombre

BEGIN TRAN NombreTransaccion
/*Bloque de instrucciones a ejecutar en la Transacción*/
COMMIT TRAN NombreTransaccion--Confirmación de la transacción.
ROLLBACK TRAN NombreTransaccion--Reversión de la transacción.

En este ejemplo, definimos un procedimiento almacenado que agregue un registro a la

tabla de clientes. En este proceso, controlamos la operación a través de una
transacción llamada tcliente, evaluamos el proceso con la variable @@ERROR

CREATE PROCEDURE USP_INSERTACLIENTE

@ID VARCHAR(5),
@NOMBRE VARCHAR(50),
@DIRECCION VARCHAR(100),
@IDPAIS CHAR(3),
@FONO VARCHAR(15)
AS
-- INICIO DE LA TRANSACCION
BEGIN TRAN TCLIENTE
INSERT INTO VENTAS.CLIENTES(IDCLIENTE, NOMCLIENTE, DIRECCION,
IDPAIS, TELEFONO)
VALUES(@ID, @NOMBRE, @DIRECCION, @IDPAIS, @FONO)
GO

-- CONTROLAR EL PROCESO
IF @@ERROR = 0
BEGIN
-- CONFIRMACIÓN DE LA INSERCIÓN
COMMIT TRAN TCLIENTE
PRINT 'CLIENTE REGISTRADO'
END

CIBERTEC CARRERAS PROFESIONALES

112

ELSE
BEGIN
PRINT @@ERROR
-- DESHACER LA INSERCIÓN
ROLLBACK TRAN TCLIENTE
END
GO

Para un mejor control de los errores, ejecutamos los procesos dentro del bloque TRY
CATCH, donde en caso que las operaciones hayan tenido éxito, ejecutará COMMIT
TRAN, pero en caso de error CATCH, ejecutará ROLLBACK TRAN, tal como se
muestra en el siguiente ejercicio.

CREATE PROCEDURE USP_AGREGAPEDIDO

-- PARÁMETROS DE PEDIDOSCABE
@IDPED INT,
@IDCLI VARCHAR(5),
@IDEMP INT,
@FECPED DATETIME,
-- PARÁMETROS DE PEDIDOSDETA
@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)

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 113

-- 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

En el siguiente ejercicio, implementamos un procedimiento almacenado para realizar

el BACKUP a la tabla ClienteBAK. En este proceso, los registros de la tabla Clientes
se irán agregando (si no existen) o actualizando sus datos (si existe el registro) o
eliminar el registro en ClienteBAK si éste no existe en Clientes.

-- 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 =

CIBERTEC CARRERAS PROFESIONALES

114

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

3.1.5 TRIGGERS O DISPARADORES

Los disparadores pueden usarse para imponer la integridad de referencia de los datos
en toda la base de datos. Los disparadores también 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.

3.1.5.1 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. Integridad de referencia significa que los valores de las llaves primarias y los
valores correspondientes de las llaves foráneas deben coincidir de forma exacta.

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.

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 115

3.1.5.2 Creación de disparadores

Un disparador es un objeto de la base de datos. Cuando se crea un disparador, se

especifica la tabla y los comandos de modificación de datos que deben “disparar” o
activar el disparador. Luego, se indica la acción o acciones que debe llevar a cabo 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 [ ; ]

A continuación, se muestra un ejemplo sencillo. Este disparador imprime un mensaje

cada vez que alguien trata de insertar, eliminar o actualizar datos de la tabla Productos

CREATE TRIGGER TX_Productos

ON Compras.Productos
FOR INSERT, UPDATE, DELETE
AS
PRINT 'Actualizacion de los registros de Productos'

Dependencia del TRIGGER o

Disparador en relación a la tabla.

CIBERTEC CARRERAS PROFESIONALES

116

Para modificar el TRIGGER, se utiliza la siguiente sintaxis:

ALTER TRIGGER TX_PRODUCTOS

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

Para borrar un TRIGGER, se utiliza la siguiente sintaxis:

DROP TRIGGER TX_PRODUCTOS

3.1.5.3 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. Está tabla toma la misma
estructura del cual se originó el TRIGGER, de tal manera que se pueda verificar los
datos y ante un error podría revertirse los cambios.

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

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 117

En este ejemplo, verificamos el número de productos que tienen la misma descripción

y de encontrarse más de un registro de productos no se deberá permitir ingresar los
datos del producto. Este disparador imprime un mensaje si la inserción se revierte y
otro si se acepta.

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. Está tabla toma la
misma estructura del cual se origino el TRIGGER, de tal manera que se pueda
verificar los datos y ante un error podría revertirse los cambios. En este caso, la
reversión de los cambios significará restaurar los datos eliminados.

Cree un TRIGGER el cual permita eliminar Clientes los cuales no han registrado algún
pedido. De eliminarse algún Cliente que no cumpla con dicha condición la operación
no deberá ejecutarse.

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

En este ejemplo, verificamos si el cliente tiene pedidos registrados, de ser así la

operación deberá ser cancelada.

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. Usando

CIBERTEC CARRERAS PROFESIONALES

118

estas dos tablas se podrá verificar los datos y ante un error podrían revertirse los
cambios.

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) <=0 OR
(SELECT UNIDADESENEXISTENCIA FROM INSERTED)<=0
BEGIN
PRINT 'EL PRECIO O UNIDADESENEXISTENCIA DEBEN SER MAYOR A CERO'
ROLLBACK TRANSACTION
END

Cree un TRIGGER que bloquee actualizar el id del producto en el proceso de

actualización.

CREATE TRIGGER TX_PRODUCTO_ACTUALIZA_ID

ON COMPRAS.PRODUCTOS
FOR UPDATE
AS
IF UPDATE(IDPRODUCTO)
BEGIN
PRINT 'NO SE PUEDE ACTUALIZAR EL ID DEL PRODUCTO'
ROLLBACK TRANSACTION
END

3.1.5.4 Uso de INSTEAD OF

Los desencadenadores INSTEAD OF pasan por alto las acciones estándar de la

instrucción de desencadenamiento: INSERT, UPDATE o DELETE. Se puede definir un
desencadenador INSTEAD OF para realizar comprobación de errores o valores en una
o más columnas y, a continuación, realizar acciones adicionales antes de insertar el
registro.

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 119

Por ejemplo, cuando el valor que se actualiza en un campo de tarifa de una hora de
trabajo de una tabla de planilla excede un valor específico, se puede definir un
desencadenador para producir un error y revertir la transacción o insertar un nuevo
registro en un registro de auditoría antes de insertar el registro en la tabla de planilla.

A. INSTEAD OF INSERT
Se pueden definir desencadenadores INSTEAD OF INSERT en una vista o tabla para
reemplazar la acción estándar de la instrucción INSERT. Normalmente, el
desencadenador INSTEAD OF INSERT se define en una vista para insertar datos en
una o más tablas base.

Las columnas de la lista de selección de la vista pueden o no admitir valores NULL. Si

una columna de la vista no admite valores NULL, una instrucción INSERT debe
proporcionar los valores para la columna.

-- CREAR UNA VISTA QUE LISTE LAS COLUMNAS DE CLIENTES.

CREATE VIEW INSTEADVIEW
AS
SELECT IDCLIENTE,
NOMCLIENTE,
DIRCLIENTE,
IDPAIS,
FONOCLIENTE
FROM VENTAS.CLIENTES
GO

-- CREAR UN TRIGGER INSTEAD OF INSERT PARA LA VISTA.

CREATE TRIGGER INSTEADTRIGGER ON INSTEADVIEW
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO VENTAS.CLIENTESBAK
SELECT IDCLIENTE, NOMCLIENTE, DIRCLIENTE, IDPAIS, FONOCLIENTE
FROM INSERTED
END
GO

CIBERTEC CARRERAS PROFESIONALES

120

B. INSTEAD OF UPDATE
Se pueden definir desencadenadores INSTEAD OF UPDATE en una vista o tabla para
reemplazar la acción estándar de la instrucción UPDATE. Normalmente, el
desencadenador INSTEAD OF UPDATE se define en una vista para modificar datos
en una o más tablas. Las instrucciones UPDATE que hacen referencia a vistas que
contienen desencadenadores INSTEAD OF UPDATE deben suministrar valores para
todas las columnas que no admiten valores NULL a las que hace referencia la cláusula
SET

C. INSTEAD OF DELETE
Se pueden definir desencadenadores INSTEAD OF DELETE en una vista o una tabla
para reemplazar la acción estándar de la instrucción DELETE. Normalmente, el
desencadenador INSTEAD OF DELETE se define en una vista para modificar datos en
una o más tablas. Las instrucciones DELETE no especifican modificaciones de los
valores de datos existentes. Las instrucciones DELETE sólo especifican las filas que
se van a eliminar. La tabla inserted pasada a un desencadenador DELETE siempre
está vacía. La tabla deleted enviada a un desencadenador DELETE contiene una
imagen de las filas en el estado que tenían antes de emitir la instrucción DELETE

3.1.5.5 Restricciones de los disparadores

A continuación, se describen algunas limitaciones o restricciones impuestas a los

disparadores por SQL Server:
• Una tabla puede tener un máximo de tres disparadores: uno de actualización, uno de
inserción y uno de eliminación.

• Cada disparador puede aplicarse a una sola tabla. Sin embargo, un mismo disparador
se puede aplicar a las tres acciones del usuario: UPDATE, INSERT y DELETE.

• No se puede crear un disparador en una vista ni en una tabla temporal, aunque los
disparadores pueden hacer referencia a las vistas o tablas temporales.

• Los disparadores no se permiten en las tablas del sistema. Aunque no aparece ningún
mensaje de error su crea un disparador en una tabla del sistema, el disparador no se
utilizará.

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 121

Resumen
Las funciones definidas por el usuario, en tiempo de ejecución de lenguaje
TRANSACT-SQL o común (CLR), acepta parámetros, realiza una acción, como un
cálculo complejo, y devuelve el resultado de esa acción como un valor. El valor de
retorno puede ser un escalar (único) valor o una tabla.
Las funciones escalares son aquellas funciones donde retornan un valor único: tipo
de datos como int, Money, varchar, real, etc. Pueden ser utilizadas en cualquier
lugar, incluso, incorporada dentro de las sentencias SQL. Las funciones de tabla en
línea son las funciones que devuelven la salida de una simple declaración
SELECT. La salida se puede utilizar adentro de JOINS o querys como si fuera una
tabla de estándar. Las funciones de tabla multisentencias son similares a los
procedimientos almacenados excepto que vuelven una tabla
Los procedimientos almacenados son grupos formados por instrucciones SQL y el
lenguaje de control de flujo. Cuando se ejecuta un procedimiento, se prepara un
plan de ejecución para que la subsiguiente ejecución sea muy rápida. Los
procedimientos almacenados pueden:
1. Incluir parámetros
2. Llamar a otros procedimientos
3. 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.
4. Devolver valores de parámetros a un procedimiento de llamada o lote
5. Ejecutarse en SQL Server remotos
Un procedimiento almacenado se comunica con el programa que lo llama
mediante sus parámetros. Cuando un programa ejecuta un procedimiento
almacenado, es posible pasarle valores mediante los parámetros del
procedimiento. Estos valores se pueden utilizar como variables estándar en el
lenguaje de programación TRANSACT-SQL. El procedimiento almacenado
también puede devolver valores al programa que lo llama mediante parámetros
OUTPUT. Un procedimiento almacenado puede tener hasta 2.100 parámetros,
cada uno de ellos con un nombre, un tipo de datos, una dirección y un valor
predeterminado

CIBERTEC CARRERAS PROFESIONALES

122

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
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.
Los disparadores pueden usarse para imponer la integridad de referencia de los
datos en toda la base de datos. Los disparadores también 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.
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. Está tabla toma la
misma estructura del cual se originó el TRIGGER, de tal manera que se pueda
verificar los datos y ante un error podría revertirse los cambios
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. Está tabla toma
la misma estructura del cual se origino el TRIGGER, de tal manera que se pueda
verificar los datos y ante un error podría revertirse los cambios. En este caso la
reversión de los cambios significará restaurar los datos eliminados.
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. Usando estas dos tablas se podrá verificar los datos y ante un error
podrían revertirse los cambios

Si desea saber más acerca de estos temas, puede consultar las siguientes
páginas:
 http://www.devjoker.com/contenidos/catss/292/Transacciones-en-Transact-SQL.aspx
Aquí hallará los conceptos de Transacciones en TRANSACT SQL.
 http://www.sqlmax.com/func2.asp

En esta página, hallará los conceptos funciones

CARRERAS PROFESIONALES CIBERTEC

BASE DE DATOS AVANZADO II 123

 http://msdn.microsoft.com/es-es/library/ms189260.aspx

Aquí hallará los conceptos de manejo de parámetros en procedimientos

almacenados

CIBERTEC CARRERAS PROFESIONALES