Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II

Centro de Extensión y Proyección Social Carreras Técnicas

Instructor: Julio E. Flores Manco 2

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Instructor: Julio E. Flores Manco 3

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

PRESENTACIÓN

Este curso comprende la creación y uso de Indices, Transacciones,

Procedimientos almacenados, Funciones definidas por el Programador.
SQL Server 2014 es uno de los productos de Microsoft con bastante
popularidad en organizaciones medianas o grandes corporaciones, debido
a sus muchas ventajas y bondades que ofrece a los usuarios.

Al finalizar el curso, el participante estará capacitado para programar Procedimientos y

reglas de negocio en el Servidor por medio de la creación de procedimientos
almacenados y triggers en la base de datos

Espero que este trabajo pueda servir para aclarar los conceptos y
fundamentos de las aplicaciones Cliente-Servidor.

Julio Enrique Flores Manco

INSTRUCTOR DEL CURSO

Instructor: Julio E. Flores Manco 4

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

CAP I

Introducción a los
índices
Los índices de las bases de datos son similares a los índices que hay en los
libros. En un libro, un índice permite encontrar información rápidamente sin
necesidad de leer todo el libro. En una base de datos, un índice permite que el
programa de la base de datos busque datos en una tabla sin necesidad de
examinar toda la tabla. El índice de un libro es una lista de palabras con los
números de las páginas en las que se encuentra cada palabra. Un índice de una
base de datos es una lista de los valores de una tabla con las posiciones de
almacenamiento de las filas de la tabla donde se encuentra cada valor. Se
pueden crear índices en una sola columna o en una combinación de columnas
de una tabla; los índices se implementan en forma de árboles B. Un índice
contiene una entrada con una o varias columnas (la clave de búsqueda) de
cada fila de una tabla. Un árbol B se ordena con la clave de búsqueda y se
puede buscar de forma eficiente en cualquier subconjunto principal de la clave
de búsqueda. Por ejemplo, un índice en las columnas A, B y C puede buscarse
de forma eficiente en A, en A y B, y en A, B y C.

La mayoría de los libros contienen un índice general de palabras, nombres,

lugares, etc. Las bases de datos contienen índices para tipos o columnas de
datos seleccionados: Es parecido a un libro con un índice para los nombres de
las personas y otro índice para los lugares. Cuando cree una base de datos y la
optimice para mejorar el rendimiento, es recomendable que cree índices para
las columnas que se utilizan en las consultas con el fin de buscar datos.

Instructor: Julio E. Flores Manco 5

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

En la base de datos de ejemplo pubs suministrada con Microsoft® SQL Server™

2014, la tabla employee tiene un índice en la columna emp_id. En la siguiente
ilustración se muestra cómo almacena el índice cada valor emp_id y señala a
las filas de datos de la tabla con cada valor.

Cuando SQL Server ejecuta una instrucción para buscar datos en la tabla
employee a partir de un valor de emp_id específico, reconoce el índice para la
columna emp_id y lo utiliza para buscar los datos. Si no hay un índice, realiza
una exploración completa de la tabla empezando desde el principio de la tabla y
va buscando, fila por fila, el valor de emp_id especificado.
SQL Server crea automáticamente índices para determinados tipos de
restricciones (por ejemplo, restricciones PRIMARY KEY y UNIQUE). También
puede personalizar las definiciones de la tabla mediante la creación de índices
independientes de las restricciones.

No obstante, las ventajas que ofrecen los índices por lo que respecta al
rendimiento también tienen un costo. Las tablas con índices necesitan más
espacio de almacenamiento en la base de datos. Asimismo, es posible que los
comandos de inserción, actualización o eliminación de datos sean más lentos y
precisen más tiempo de proceso para mantener los índices. Cuando diseñe y
cree índices, deberá asegurarse de que las ventajas en el rendimiento
compensan suficientemente el costo adicional en cuanto a espacio de
almacenamiento y recursos de proceso.

Instructor: Julio E. Flores Manco 6

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Arquitectura de los índices

Cómo SQL Server almacena y tiene acceso a
los datos
Modo de almacenamiento de los datos

Las filas se almacenan en páginas de datos

Los montones son una colección de páginas de datos para una tabla

Acceso a los datos

Recorre todas las páginas de datos en una tabla

Mediante un índice que apunte a los datos de una página

Páginas de datos

Las páginas de datos contienen todos los datos de las filas de datos excepto los
de tipo text, ntext e image, que están almacenados en páginas separadas. Las
filas de datos se colocan en las páginas una a continuación de otra, empezando
inmediatamente después del encabezado. Al final de cada página se encuentra
una tabla de desplazamiento de filas. La tabla de desplazamiento de filas
contiene una entrada por cada fila de la página y cada entrada registra la
posición del primer byte de la fila con respecto al principio de la página. Las
entradas de la tabla de desplazamiento de filas están en orden inverso a la
secuencia de las filas de la página.

Instructor: Julio E. Flores Manco 7

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

En SQL Server, las filas no pueden continuar en otras páginas. En SQL Server
2014, la máxima cantidad de datos contenidos en una fila es de 8060 bytes, sin
incluir los datos text, ntext e image.

Razones para crear un índice

Acelerar el acceso a datos
Fuerzan la unicidad de las filas

Razones para no crear un índice

Consumen espacio en disco
Generan costos de procesamiento

Instructor: Julio E. Flores Manco 8

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Uso de montones
SQL Server utiliza las páginas de Mapa de asignación de índices que:

 Contienen información acerca del lugar donde están almacenadas las

extensiones de un montón
 Se utilizan para recorrer el montón y encontrar espacio disponible para
insertar nuevas filas

Conectan páginas de datos

Recupera espacio para las nuevas filas del montón cuando se elimina una fila

Uso de los índices agrupados

 Cada tabla sólo puede tener un índice agrupado

 El orden físico de las filas de la tabla y el orden de las filas en el índice
son el mismo
 La unicidad de los valores de clave se mantiene explícitamente o
implícitamente

Uso de los índices no agrupados

 Los índices no agrupados son los predeterminados de SQL Server
 Los índices no agrupados existentes se vuelven a generar
automáticamente
 Se quita un índice agrupado existente
 Se crea un índice agrupado
 Se utiliza la opción DROP_EXISTING para cambiar las columnas
que definen el índice agrupado

Instructor: Julio E. Flores Manco 9

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Cómo SQL Server recupera los datos

almacenados

 SQL Server utiliza la tabla sysindexes

 Búsqueda de filas sin índices
 Búsqueda de filas en un montón con un índice no agrupado
 Búsqueda de filas en un índice agrupado
 Búsqueda de filas en un índice agrupado con un índice no agrupado

Cómo SQL Server utiliza la tabla sysindexes

 Describe los índices

 Ubicación de IAM, primero y raíz de índices

 Número de páginas y filas
 Distribución de datos

Instructor: Julio E. Flores Manco 10

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Búsqueda de filas sin índices

Búsqueda de filas en un montón con un índice no

agrupado

Instructor: Julio E. Flores Manco 11

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Búsqueda de filas en un índice agrupado

Búsqueda de filas en un índice agrupado con un

índice no agrupado

Instructor: Julio E. Flores Manco 12

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Mantenimiento de las estructuras

de los índices y los montones
Divisiones de páginas en un índice

Puntero de reenvío en un montón

Instructor: Julio E. Flores Manco 13

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Actualización de filas
 Una actualización no suele hacer que una fila se mueva
 Una actualización puede ser una eliminación seguida de una inserción
 Las actualizaciones por lotes tocan cada índice una sola vez

Eliminación de filas
 Cómo las eliminaciones producen registros fantasmas
 Cómo SQL Server reclama espacio
 Cómo se pueden reducir los archivos

Las páginas de índice agrupadas se desplazan como una unidad

Los registros del montón se desplazan de forma individual

Decisión de las columnas que se van a indizar

 Comprensión de los datos
 Directrices de indización
 Elección del índice agrupado adecuado
 Creación de índices que admiten consultas
 Determinación de la selectividad
 Determinación de la densidad
 Determinación de la distribución de datos

Comprensión de los datos

 El diseño lógico y físico
 Las características de los datos
 Cómo se utilizan los datos
Los tipos de consultas realizadas
La frecuencia de las consultas más típicas

Instructor: Julio E. Flores Manco 14

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Elección del índice agrupado adecuado

 Tablas continuamente actualizadas
 Un índice agrupado con una columna de identidad mantiene las
páginas actualizadas en memoria

 Ordenación
 Un índice agrupado mantiene los datos preordenados

 Longitud de columna y tipo de datos

 Limita el número de columnas
 Reduce el número de caracteres
 Utiliza los tipos de datos más pequeños posibles

Creación de índices que admiten consultas

 Uso de argumentos de búsqueda

 Escritura de buenos argumentos de búsqueda

 Especificar una cláusula WHERE en la consulta

 Comprobar que la cláusula WHERE limita el número
de filas
 Comprobar que existe una expresión para cada tabla a la que se
hace referencia en la consulta
 Evitar el uso de caracteres comodines iniciales

Instructor: Julio E. Flores Manco 15

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Determinación de la selectividad

Determinación de la densidad

Instructor: Julio E. Flores Manco 16

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Procedimientos recomendados

Instructor: Julio E. Flores Manco 17

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejercicios de Laboratorio

USE EDUTEC2
GO
-- VERIFICAR LOS INDICES EN EL ADMINISTRADOR
CORPORATIVO/TABLA/TODAS LAS TAREAS/ADMINISTRAR INDICES...

CREATE UNIQUE CLUSTERED INDEX PKALUMNO

ON ALUMNO(IdAlumno)
GO
CREATE UNIQUE CLUSTERED INDEX PKCICLO
ON CICLO(IdCiclo)
GO
CREATE UNIQUE CLUSTERED INDEX PKPROFESOR
ON PROFESOR(IdPROFESOR)
GO
CREATE UNIQUE CLUSTERED INDEX PKEMPLEADO
ON EMPLEADO(IdEMPLEADO)
GO
CREATE UNIQUE CLUSTERED INDEX PKPARAMETRO
ON PARAMETRO(IdPARAMETRO)
GO

-- EN EL CASO DE MATRICULA VAMOS ACREAR UN INDICE COMPUESTO

CREATE UNIQUE CLUSTERED INDEX PKMATRICULA

ON MATRICULA(IdCursoProg,IdAlumno)
GO

-- VERIFICAR LOS INDICES CON ANALIZADOR DE CONSULTAS:

USE EDUTEC2
GO

EXEC SP_HELPINDEX ALUMNO

GO
EXEC SP_HELPINDEX MATRICULA
GO

-- CREANDO UN FACTOR DE LLENADO

CREATE UNIQUE CLUSTERED INDEX PKCURSOPROGRAMADO

ON CURSOPROGRAMADO(IdCURSOPROG)
WITH FILLFACTOR = 75
-- Se esta dejando un 25% para llenar otros registros

Instructor: Julio E. Flores Manco 18

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

GO
-- VERIFICANDO LAS CARACTERISTICAS DEL INDICE EN LA TABLA DE
SISTEMA SYSINDEXES
SELECT * FROM SYSINDEXES WHERE NAME = 'PKCURSOPROGRAMADO'
GO

-- CREACION DE INDICES NO AGRUPADOS ( NONCLUSTERED )

CREATE NONCLUSTERED INDEX APELLIDO
ON ALUMNO(ApeAlumno)
GO
EXEC SP_HELPINDEX ALUMNO
GO

CREATE NONCLUSTERED INDEX APENOM

ON PROFESOR(ApeProfesor,NomProfesor)
GO
EXEC SP_HELPINDEX PROFESOR
GO

CREATE NONCLUSTERED INDEX APELLIDOEMP

ON EMPLEADO(ApeEmpleado)
GO
EXEC SP_HELPINDEX EMPLEADO
GO

CREATE NONCLUSTERED INDEX CARGOAPE

ON EMPLEADO(Cargo,ApeEmpleado)
GO
EXEC SP_HELPINDEX EMPLEADO
GO

CREATE NONCLUSTERED INDEX FECHAM

ON MATRICULA(FecMatricula)
GO
EXEC SP_HELPINDEX MATRICULA
GO

-- USANDO LOS INDICES

SELECT ApeAlumno,TelAlumno FROM ALUMNO

(INDEX=APELLIDO) -- NOMBRE DEL INDICE (APELLIDO)
GO

SELECT FecMatricula,IdAlumno FROM MATRICULA

(INDEX=FechaM)
GO

Instructor: Julio E. Flores Manco 19

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

 CONSULTAS CUBIERTAS

SELECT Cargo,ApeEmpleado FROM EMPLEADO

(INDEX=CARGOAPE) -- LOS CAMPOS QUE SE CONSULTAN FORMAN
EL INDICE
GO

SELECT ApeProfesor,NomProfesor FROM PROFESOR

(INDEX=APENOM)
GO

-- REGENERACION DE UN INDICE

CREATE UNIQUE CLUSTERED INDEX PKCURSOPROGRAMADO

ON CURSOPROGRAMADO(IdCursoProg)
WITH DROP_EXISTING, FILLFACTOR=65
-- REGORGANIZA LAS PAGINAS DE HOJA, QUITA LA FRAGMENTACION Y
VUELVE A CALCULAR LAS ESTADISTICAS DE INDICES
GO

SELECT * FROM SYSINDEXES WHERE NAME = 'PKCURSOPROGRAMADO'

GO

-- ELIMINANDO INDICES

-- PRIMERO LOS NO AGRUPADOS

DROP INDEX [Link]

-- tabla indice
GO

-- LUEGO EL AGRUPADO
DROP INDEX [Link]
-- tabla indice
GO

EXEC SP_HELPINDEX ALUMNO

GO

-- CREACION DE CLAVES PRIMARIAS Y FORANEAS

-- VERIFICAR EL SCRIPT CreaTablas QUE CREA LAS TABLAS DE EDUTEC

Instructor: Julio E. Flores Manco 20

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

CAP II
Procesos por Lotes y
Transacciones
Procesos por Lotes
Un proceso por lotes o batch es un conjunto de instrucciones SQL que se
envían al servidor y se ejecutan en conjunto.

Ejemplo:

Use EduTec
Select * From Tarifa
Select * From Curso
Select * From Alumno
Go

 Todas las instrucciones dentro de un proceso por lotes se analizan como

una sola unidad.

 Ejemplo:

1. Use EduTec
2. Select * From Tarifa
3. Select * Fom Curso
4. Select * From Alumno
5. Go

 No se ejecuta ningún Select y se obtiene el siguiente mensaje de error

de compilación:

Servidor: mensaje 170, nivel 15, estado 1, línea 2

Línea 2: sintaxis incorrecta cerca de 'Fom'.

Instructor: Julio E. Flores Manco 21

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

 SQL Server utiliza la resolución de nombres de objetos aplazada.

 Ejemplo:

1. Use EduTec
2. Select * From Tarifa
3. Select * From Cursos
4. Go

 Se ejecuta el primer Select y se obtiene el siguiente mensaje de error

por el segundo Select:

Servidor: mensaje 208, nivel 16, estado 1, línea 1

El nombre de objeto 'Cursos' no es válido.

 GO no es una instrucción SQL. Las herramientas cliente interpretan GO

como una señal de que deben enviar el lote actual de instrucciones SQL
al Servidor SQL Server.

 El hecho de que sea la herramienta cliente la que procesa el comando

GO y no SQL Server puede dar lugar a ciertos comportamientos
inesperados, veamos el siguiente ejemplo:

1. Select * From Curso

2. /*
3. Go
4. Select * From Alumno
5. Go
6. */
7. Select * From Profesor
8. Go

 Para que el cliente no interprete la instrucción GO debemos incluirlo

entre comentarios con el doble guion, como se ilustra a continuación:

1. Select * From Curso

2. /*
3. --Go
4. Select * From Alumno
5. --Go
6. */
7. Select * From Profesor
8. Go

Instructor: Julio E. Flores Manco 22

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

 El ámbito de las variables locales (definidas por el usuario) está limitado

a un batch y no es posible referirse a ellas después del comando GO. Por
ejemplo si ejecutamos el siguiente batch:

1. DECLARE @MyMsg VARCHAR(50)

2. SET @MyMsg = ‘Arriba Alianza'
3. GO
4. PRINT @MyMsg
5. GO

 Obtenemos el siguiente mensaje de error:

Servidor: mensaje 137, nivel 15, estado 2, línea 1

Debe declarar la variable '@MyMsg'.

Instructor: Julio E. Flores Manco 23

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Transacciones
Una transacción es una unidad única de trabajo. 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 deshacerse, se borran todas
las modificaciones de los datos.

SQL Server funciona en tres modos de transacción:

Transacciones de confirmación automática

Transacciones explícitas
Transacciones implícitas

Una unidad lógica de trabajo debe exhibir cuatro propiedades, conocidas como
propiedades ACID (atomicidad, coherencia, aislamiento y durabilidad), para ser
calificada como transacción.

Atomicidad:

Una transacción debe ser una unidad atómica de trabajo, tanto si se realizan
todas sus modificaciones en los datos, como si no se realiza ninguna de
ellas.

Coherencia:

Cuando finaliza, una transacción debe dejar todos los datos en un estado
coherente.

Aislamiento:

Las modificaciones realizadas por transacciones simultáneas se deben aislar de

las modificaciones llevadas a cabo por otras transacciones simultáneas.

Durabilidad:

Una vez concluida una transacción, sus efectos son permanentes en el sistema.
Las modificaciones persisten aún en el caso de producirse un error del
sistema.

Instructor: Julio E. Flores Manco 24

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Es responsabilidad de un sistema de base de datos corporativo como SQL

Server proporcionar los mecanismos que aseguren la integridad física de cada
transacción. SQL Server proporciona:

 Servicios de bloqueo que preservan el aislamiento de la transacción.

 Servicios de registro que aseguran la durabilidad de la transacción. Aún

en el caso de que falle el hardware del servidor, el sistema operativo o el
propio SQL Server. SQL Server utiliza registros de transacciones, para
que cuando nuevamente se reinicie, pueda deshacer automáticamente
las transacciones incompletas en el momento en que se produjo el error
en el sistema.

 Características de administración de transacciones que exigen la

atomicidad y coherencia de la transacción. Una vez iniciada una
transacción, debe concluirse correctamente o SQL Server deshará todas
las modificaciones de datos realizadas desde que se inició la transacción.

Transacciones de Confirmación Automática

 Cada instrucción individual es una transacción.

 Ejemplo:

1. USE EduTec
2. GO
3. CREATE TABLE Demo07 ( ColA INT PRIMARY KEY, ColB CHAR(3))
4. GO
5. INSERT INTO Demo07 VALUES (1, 'aaa')
6. INSERT INTO Demo07 VALUES (2, 'bbb')
7. INSERT INTO Demo07 VALUSE (3, 'ccc') /* Error de Sintaxis */
8. GO
9. SELECT * FROM Demo07 /* No Retorna Filas */
10. GO

 Otro Ejemplo:
1. USE EduTec
2. GO
3. CREATE TABLE Demo08 ( ColA INT PRIMARY KEY, ColB CHAR(3))
4. GO
5. INSERT INTO Demo08 VALUES (1, 'aaa')
6. INSERT INTO Demo08 VALUES (2, 'bbb')
7. INSERT INTO Demo08 VALUES (1, 'ccc') /* Error Clave Duplicada
*/
8. GO
9. SELECT * FROM Demo08 /* Retorna las Filas 1 y 2 */
10. GO

Instructor: Julio E. Flores Manco 25

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

 Otro Ejemplo:

1. USE EduTec
2. GO
3. CREATE TABLE Demo09 ( ColA INT PRIMARY KEY, ColB CHAR(3))
4. GO
5. INSERT INTO Demo09 VALUES (1, 'aaa')
6. INSERT INTO Demo09 VALUES (2, 'bbb')
7. INSERT INTO Demo90 VALUES (1, 'ccc') /* Error en Nombre de
Tabla */
8. GO
9. SELECT * FROM Demo09 /* Retorno las Filas 1 y 2 */
10. GO

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
TRANSACTION o ROLLBACK TRANSACTION.

 BEGIN TRANSACTION

Marca el punto de inicio de una transacción explícita.

 COMMIT TRANSACTION o COMMIT WORK

Se utiliza para finalizar una transacción correctamente si no hubo errores.

Todas las modificaciones de datos realizadas en la transacción se convierten
en parte permanente de la base de datos. Los recursos mantenidos por la
transacción se liberan.

 ROLLBACK TRANSACTION o ROLLBACK WORK

Se utiliza para eliminar una transacción en la que se encontraron errores. Se

devuelven todos los datos que modifica la transacción al estado en que
estaba al inicio de la transacción. Los recursos mantenidos por la
transacción se liberan.

 Mientras que un batch es un concepto del lado del cliente, que controla
cuántas instrucciones se envían al servidor SQL Server para procesarlas
como una sola unidad; una transacción es un concepto del lado del
servidor que se encarga de determinar cuánto trabajo debe realizar el
servidor SQL Server antes de considerar confirmados los cambios.

Instructor: Julio E. Flores Manco 26

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

 Las transacciones y los procesos por lotes pueden tener una relación de
varios a varios. Una única transacción puede estar distribuida en varios
batch (aunque es algo malo desde la perspectiva del rendimiento), y un
batch puede contener varias transacciones.

 Ejemplo 1:

1. Use EduTec
2. GO
3. Begin Tran
4. Update Tarifa Set PreTarifa = PreTarifa * 1.05
5. GO
6. Insert Into Curso Values( 'C900','C','PHP' )
7. GO
8. Commit Tran
9. GO

 Ejemplo 2:

1. Use EduTec
2. GO
3. Begin Tran
4. Update Matricula Set ExaParcial = 16 Where IdAlumno = 'A0001'
5. Insert Tarifa Values ('H',150.00,'Seminarios de 12 Horas')
6. Commit Tran
7. Begin Tran
8. Insert Curso Values('C901','H','Comercio Electrónico')
9. Insert Alumno(IdAlumno,ApeAlumno,NomAlumno)
Values('A9000','Diaz Romero','José Manuel')
10. Commit Tran
11. GO

Instructor: Julio E. Flores Manco 27

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Transacciones Implícitas
 Cuando una conexión funciona en modo de transacciones implícitas, SQL
Server inicia automáticamente una nueva transacción después de
confirmar o deshacer la transacción actual. No tiene que realizar ninguna
acción para especificar el inicio de una transacción, sólo tiene que
confirmar o deshacer cada transacción. El modo de transacciones
implícitas genera una cadena continua de transacciones.

 Tras establecer el modo de transacciones implícitas en una conexión,

SQL Server inicia automáticamente una transacción la primera vez que
ejecuta una de estas instrucciones:

ALTER TABLE INSERT CREATE

OPEN DELETE REVOKE
DROP SELECT FETCH
TRUNCATE TABLE GRANT UPDATE

 SET IMPLICIT_TRANSACTIONS { ON | OFF }

Establece el modo de transacción implícita para la conexión.

 Ejemplo

1. Use EduTec
2. Go
3. Set Implicit_Transactions ON
4. Go
5. Print '@@TranCount (1) = ' + Convert(VarChar(5),@@TranCount)
6. Insert Alumno(IdAlumno,ApeAlumno,NomAlumno)
Values('A9001','Valencia Torres','Claudia')
7. Print '@@TranCount (2) = ' + Convert(VarChar(5),@@TranCount)
8. Insert Matricula(IdCursoProg,IdAlumno,FecMatricula)
Values(1,'A9001','20010804')
9. Print '@@TranCount (3) = ' + Convert(VarChar(5),@@TranCount)
10. Commit Tran
11. Print '@@TranCount (4) = ' + Convert(VarChar(5),@@TranCount)
12. Go
13. Set Implicit_Transactions Off
14. Go

Instructor: Julio E. Flores Manco 28

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Control de Errores

 Las transacciones de varias instrucciones deberán comprobar los errores

verificando la variable @@Error después de cada instrucción. Si se
encuentra un error no fatal y no se toma ninguna acción, el
procesamiento pasará a la siguiente instrucción. Solo errores fatales
provocan la cancelación automática del batch.

 Una consulta que no encuentra ninguna fila que satisfaga el criterio de la

cláusula WHERE o una instrucción UPDATE que no afecte a ninguna fila,
no son errores y @@Error retorna 0 (lo que significa que ha habido
error) en cualquiera de estos casos. Si queremos comprobar que no
existe ninguna fila afectada, debemos verificar el valor de la variable
@@RowCount.

 Los errores mas habituales son:

 Falta de permisos sobre un objeto

 Violación de restricciones
 Se encuentran duplicados al intentar actualizar o insertar una fila
 Violaciones NOT NULL
 Valor ilegal para el tipo de dato actual

 Analicemos el siguiente ejemplo:

1. Create Table TA( a Char(1) Primary Key )

2. Go
3. Create Table TB( b Char(1) References TA )
4. Go
5. Create Table TC( c Char(1) )
6. Go
7. Create Procedure Test1 As
8. Begin Tran
9. Insert TC Values( 'X' )
10. Insert TB Values( 'X' ) -- ¡ Falla la Referencia !
11. Commit Tran
12. Go
13. Exec Test1
14. Go
15. Select * From TC -- ¡ Retorn X !

Instructor: Julio E. Flores Manco 29

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

 Modificación del Procedimiento:

1. Create Procedure Test2 As

2. Begin Tran
3. Insert TC Values( 'Y' )
4. If ( @@Error <> 0 ) Goto on_error
5. Insert TB Values( 'Y' ) -- ¡ Falla la Referencia !
6. If ( @@Error <> 0 ) Goto on_error
7. Commit Tran
8. Return (0)
9. on_error:
10. Rollback Tran
11. Return (1)
12. Go
13. Exec Test2
14. Go
15. Select * From TC
16. Go

 SET XACT_ABORT { ON | OFF }

Especifica si SQL Server debe deshacer automáticamente la transacción actual

si una instrucción SQL causa un error en tiempo de ejecución.

 Veamos el siguiente ejemplo:

1. Create Procedure Test3

2. As
3. Begin Tran
4. Insert TC Values( 'Z' )
5. Insert TB Values( 'Z' ) -- ¡ Falla la Referencia !
6. Commit Tran
7. Go
8. SET XACT_ABORT ON
9. GO
10. Exec Test3
11. Go
12. Select * From TC
13. Go
14. SET XACT_ABORT OFF
15. GO

Instructor: Julio E. Flores Manco 30

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

CAP III
Procedimientos almacenados
Al crear una aplicación con Microsoft® SQL Server™ 2014, el lenguaje de
programación Transact-SQL es la principal interfaz de programación entre las
aplicaciones y la base de datos SQL Server. Cuando utilice programas Transact-
SQL, dispone de dos métodos para almacenar y ejecutar los programas.

Puede almacenar localmente los programas y crear aplicaciones que envíen los
comandos a SQL Server y procesen los resultados, o bien almacenar los
programas como procedimientos almacenados en SQL Server y crear
aplicaciones que ejecuten los procedimientos almacenados y procesen los
resultados.

Los procedimientos almacenados de SQL Server son similares a los

procedimientos de otros lenguajes de programación en el sentido de que
pueden:

 Aceptar parámetros de entrada y devolver varios valores en forma de

parámetros de salida al lote o al procedimiento que realiza la llamada.
 Contener instrucciones de programación que realicen operaciones en la
base de datos, incluidas las llamadas a otros procedimientos.
 Devolver un valor de estado a un lote o a un procedimiento que realiza
una llamada para indicar si la operación se ha realizado correctamente o
ha habido un error (y el motivo del mismo).

Puede utilizar la instrucción EXECUTE de Transact-SQL para ejecutar un

procedimiento almacenado. Los procedimientos almacenados difieren de las
funciones en que no devuelven valores en lugar de sus nombres ni pueden
utilizarse directamente en una expresión.

Instructor: Julio E. Flores Manco 31

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Utilizar procedimientos almacenados en SQL Server en vez de programas

Transact-SQL almacenados localmente en equipos clientes presenta las
siguientes ventajas:

Permiten una programación modular.

Puede crear el procedimiento una vez, almacenarlo en la base de datos y

llamarlo desde el programa tantas veces como desee. Un especialista en
programación de bases de datos puede crear procedimientos
almacenados, que luego será posible modificar independientemente del
código fuente del programa.

Permiten una ejecución más rápida.

En los casos en que la operación requiere una gran cantidad de código

Transact-SQL o se realiza repetidas veces, los procedimientos
almacenados pueden ser más rápidos que los lotes de código Transact-
SQL.
Los procedimientos son analizados y optimizados en el momento de su
creación, y es posible utilizar una versión del procedimiento que se
encuentra en la memoria después de haberlo ejecutado una primera vez.
Las instrucciones de Transact-SQL que se envían varias veces desde el
cliente cada vez que deben ejecutarse tienen que ser compiladas y
optimizadas siempre que SQL Server las ejecuta.

Pueden reducir el tráfico de red.

Una operación que necesite centenares de líneas de código Transact-SQL

puede realizarse mediante una sola instrucción que ejecute el código en
un procedimiento, en vez de enviar cientos de líneas de código por la red.
Pueden utilizarse como mecanismo de seguridad.
Es posible conceder permisos a los usuarios para ejecutar un
procedimiento almacenado, incluso si no cuentan con permiso para
ejecutar directamente las instrucciones del procedimiento.

Para crear un procedimiento almacenado de SQL Server se utiliza la instrucción

CREATE PROCEDURE de Transact-SQL; asimismo, puede utilizarse la instrucción
ALTER PROCEDURE para modificar la instrucción. La definición del
procedimiento almacenado contiene dos componentes principales: la
especificación del nombre del procedimiento y sus parámetros, y el cuerpo del
procedimiento que contiene las instrucciones de Transact-SQL que pueden
realizar las operaciones del procedimiento.

Instructor: Julio E. Flores Manco 32

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Definición de procedimientos almacenados

 Los SP son objetos de la Base de Datos; residen en un archivo de BD y se

trasladan junto al archivo si desmonta o replica la BD.
 Colecciones con nombre de instrucciones Transact-SQL
 Encapsulado de tareas repetitivas
 Admiten cinco tipos (del sistema, locales, temporales, remotos y extendidos)
 Aceptar parámetros de entrada que le permiten pasar datos al
procedimiento para manejarlos y devolver valores en parametros de salida.
 Devolver valores de estado para indicar que se ha ejecutado
satisfactoriamente o se ha producido algún error
 Los SP se ejecutan en forma optimizada, dando lugar a una ejcución más
rápida.

Procesamiento inicial de los procedimientos almacenados

Instructor: Julio E. Flores Manco 33

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Procesamientos posteriores de los

procedimientos almacenados

Intercambiar datos con Procedimientos Almacenados

 Los SP proporcionan dos métodos de comunicación con procesos externos:

» Parametros
» Valores de retorno

 Los Parametros son una clase especial de variable local declarada como
parte del SP. Se puede utilizar parametros para pasar información
(parametros de entrada) o recibir valores desde el SP (parametros de
salida).

 Un valor de retorno es similar al resultado de una función y puede asignarse

a una variable local de la misma forma.

 Los valores de retorno son siempre enteros. Pueden usarse teóricamente

para devolver cualquier resultado, pero por convención se usan para
devolver el estado de la ejecución del SP.
 Por ejemplo un SP podría devolver 0 si todo fue bien, o -1 si hubo algún
error. Los SP más sofisticados pueden devolver valores de retorno diferentes
para indicar la naturaleza del error encontrado.

Instructor: Julio E. Flores Manco 34

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

 Un SP puede contener cualquier número de sentencias SELECT que

devolverán varios conjuntos de resultados. No se tiene que utilizar un
parámetro para recibirlos, se devuelven a la aplicación en forma
independiente.

Creación de procedimientos almacenados

Utilice la instrucción CREATE PROCEDURE para crearlos en la base de datos
activa

Por ejemplo:

USE Northwind
GO
CREATE PROC [Link]
AS
SELECT *
FROM [Link]
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
GO

 Puede anidar hasta 32 niveles

 Use sp_help para mostrar información

Recomendaciones para la creación de

procedimientos almacenados
 El usuario dbo debe ser el propietario de todos los procedimientos
almacenados
 Un procedimiento almacenado por tarea
 Crear, probar y solucionar problemas
 Evite sp_Prefix en los nombres de procedimientos almacenados
 Utilice la misma configuración de conexión para todos los procedimientos
almacenados
 Reduzca al mínimo la utilización de procedimientos almacenados
temporales
 No elimine nunca directamente las entradas de Syscomments

Instructor: Julio E. Flores Manco 35

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejecución de procedimientos almacenados

 Ejecución de un procedimiento almacenado por separado

EXEC OverdueOrders

 Ejecución de un procedimiento almacenado en una instrucción INSERT

INSERT INTO Customers

EXEC EmployeeCustomer

Alteración y eliminación de procedimientos almacenados

 Modificación de procedimientos almacenados
 Incluya cualquiera de las opciones en ALTER PROCEDURE
 No afecta a los procedimientos almacenados anidados

USE Northwind
GO
ALTER PROC [Link]
AS
SELECT CONVERT(char(8), RequiredDate, 1) RequiredDate,
CONVERT(char(8), OrderDate, 1) OrderDate,
OrderID, CustomerID, EmployeeID
FROM Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
ORDER BY RequiredDate
GO

 Eliminación de procedimientos almacenados

 Ejecute el procedimiento almacenado sp_depends para determinar si los
objetos dependen del procedimiento almacenado

Instructor: Julio E. Flores Manco 36

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Utilización de parámetros en los procedimientos

almacenados
 Utilización de parámetros de entrada
 Ejecución de procedimientos almacenados con parámetros de
entrada
 Devolución de valores mediante parámetros de salida
 Volver a compilar explícitamente procedimientos almacenados

Utilización de parámetros de entrada

 Valide primero todos los valores de los parámetros

de entrada
 Proporcione los valores predeterminados apropiados
e incluya las comprobaciones de Null

CREATE PROCEDURE dbo.[Year to Year Sales]

@BeginningDate DateTime, @EndingDate DateTime
AS
IF @BeginningDate IS NULL OR @EndingDate IS NULL
BEGIN
RAISERROR('NULL values are not allowed', 14, 1)
RETURN
END
SELECT [Link],
[Link],
[Link],
DATENAME(yy,ShippedDate) AS Year
FROM ORDERS O INNER JOIN [Order Subtotals] OS
ON [Link] = [Link]
WHERE [Link] BETWEEN @BeginningDate AND
@EndingDate
GO

Instructor: Julio E. Flores Manco 37

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejecución de procedimientos almacenados con

parámetros de entrada
 Paso de valores por el nombre del parámetro

EXEC AddCustomer
@CustomerID = 'ALFKI',
@ContactName = 'Maria Anders',
@CompanyName = 'Alfreds Futterkiste',
@ContactTitle = 'Sales Representative',
@Address = 'Obere Str. 57',
@City = 'Berlin',
@PostalCode = '12209',
@Country = 'Germany',
@Phone = '030-0074321'

 Paso de valores por posición

EXEC AddCustomer 'ALFKI2', 'Alfreds Futterkiste', 'Maria Anders',
'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209',
'Germany', '030-0074321'

Devolución de valores mediante parámetros de

salida

Instructor: Julio E. Flores Manco 38

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Volver a compilar explícitamente

procedimientos almacenados

Volver a compilar cuando

 El procedimiento almacenado devuelve conjuntos de resultados que varían

considerablemente
 Se agrega un nuevo índice a una tabla subyacente
 El valor del parámetro es atípico

Volver a compilar mediante

 CREATE PROCEDURE [WITH RECOMPILE]

 EXECUTE [WITH RECOMPILE]
 sp_recompile

Control de mensajes de error

 La instrucción RETURN sale incondicionalmente de una consulta o
procedimiento
 sp_addmessage crea mensajes de error personalizados
 @@error contiene el número de error de la instrucción ejecutada más
recientemente
 Instrucción RAISERROR
Devuelve un mensaje de error del sistema definido por el usuario
Establece un indicador del sistema para registrar un error

Consideraciones acerca del rendimiento

 Monitor de sistema de Windows 2014

Objeto: SQL Server: Administrador de caché

Objeto: Estadísticas de SQL

 Analizador de SQL

Puede supervisar eventos

Puede probar cada instrucción en un procedimiento almacenado

Instructor: Julio E. Flores Manco 39

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Procedimientos recomendados

Instructor: Julio E. Flores Manco 40

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejercicios de PROCEDIMIENTOS ALMACENADOS

PARTE 1

/*Base de datos de prueba*/

CREATE DATABASE Ejemplos
/*Tabla de ejemplo*/
CREATE TABLE Clientes (
[cod_cli] [int] NOT NULL ,
[nombre] [char] (30) NOT NULL ,
[ciudad] [varchar] (15) NOT NULL ,
[telefono] [varchar] (8) NULL )
GO
--CLIENTES
insert into Clientes values(1,'Maria Euguren','Lima','3245876')
insert into Clientes values(2,'Alejandro Mezco Caballero','La Plata','4959554')
insert into Clientes values(3,'Daniela Velasquez Marquez','Arequipa','4791004')
insert into Clientes values(4,'Daniel Hacha Gonzales','Lima','4151004')
insert into Clientes values(5,'Jose Peña','Huaraz','4568741')
insert into Clientes values(6,'Pedro Picapiedra','Huaraz','4568741')
GO
--Se comprueban los datos:
Select * from Clientes
GO
/*Ejemplos de Stored Procedure*/
--(16)Sin Recibir ni Devolver parámetros:
CREATE PROCEDURE ListaClientes
AS
SELECT * FROM Clientes
GO
--Ejecución:
EXECUTE ListaClientes
GO
--(18) Con parámetros que Recibe:
CREATE PROCEDURE AgregarClientes
( @xcod int, @xnombre char(30), @xciudad varchar(15), @xtelefono
varchar(8)='000-0000' )
AS
INSERT INTO Clientes VALUES ( @xcod, @xnombre, @xciudad, @xtelefono )
SELECT * FROM Clientes
GO
--Ejecución:
EXECUTE AgregarClientes 6,'Pedro Picapiedra','Huaraz','4568741'
GO
EXECUTE AgregarClientes 7,'Vilma Picapiedra','Huaraz'
GO

Instructor: Julio E. Flores Manco 41

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

--(21) Retornando un valor:

CREATE PROCEDURE ClienteRepetido
@xcod smallint
AS
IF ( SELECT count(*) FROM Clientes WHERE cod_cli = @xcod ) > 1
RETURN 1
ELSE
RETURN 2
GO
--Ejecución:
DECLARE @existe int
EXECUTE @existe = ClienteRepetido 6
SELECT @existe
GO
--(23)Con parámetros que Recibe y que Devuelve:
CREATE PROCEDURE buscarCliente
@xcod smallint, @xnombre CHAR(30) OUTPUT
AS
SELECT @xnombre = nombre FROM Clientes
WHERE cod_cli =@xcod
GO
--Ejecución:
DECLARE @xnom char(30)
EXECUTE buscarCliente 6, @xnom OUTPUT
SELECT 'El nombre es : ', @xnom
GO
--(25) Modificación de un Stored Procedure:
ALTER PROCEDURE ClienteRepetido
@xcod smallint
AS
IF ( SELECT count(*) FROM Clientes WHERE cod_cli = @xcod ) > 1
RETURN 99
ELSE
RETURN 1
GO
--Comprobando la modificación:
DECLARE @existe int
EXECUTE @existe = ClienteRepetido 6
SELECT @existe
GO
--(26) Eliminación de un Stored Procedure:
DROP PROCEDURE ListaClientes
GO
--Comprobando la eliminación:
EXECUTE ListaClientes
GO

Instructor: Julio E. Flores Manco 42

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

--Obteniendo Información de los Stored Procedure:

--Mostrar información de la lista de procedimientos almacenados del entorno
actual.
sp_stored_procedures
--Mostrar información acerca de un objeto de la base de datos cualquiera de la
tabla sysobjects.
sp_help ClienteRepetido
--Mostrar información del objeto del cual depende un Stored Procedure.
sp_depends ClienteRepetido
--Mostrar el texto que contiene un Stored Procedure.
sp_helptext ClienteRepetido

PARTE 2

1) Procedimiento sencillo con una instrucción SELECT

CREATE PROCEDURE sp_CursoTarifa

AS
SELECT [Link] as Curso,[Link] as Costo
FROM Tarifa T INNER JOIN Curso C
ON [Link]=[Link]
GO
Probando el SP:
EXEC sp_CursoTarifa

2) Procedimiento sencillo con parámetros:

CREATE PROCEDURE sp_ListaCursos2

@Nombre varchar(50)='M%'
AS
SELECT * FROM Curso
WHERE NomCurso LIKE @Nombre
Probando el SP:
EXEC sp_ListaCursos2 ‘P%’

3) Usar parámetros OUTPUT

CREATE PROCEDURE sp_CuentaCursosProf

@idProf Char(4),
@NumCur Integer OUTPUT
AS
SELECT @NumCur = Count(*)
FROM CursoProgramado
WHERE IdProfesor = @idProf
GO

Instructor: Julio E. Flores Manco 43

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Probando el SP:
DECLARE @Cursos INTEGER
EXEC sp_CuentaCursosProf ‘P004’,@Cursos OUTPUT
SELECT @Cursos AS [Cantidad de Cursos]

4) Usar parámetro OUTPUT cursor

CREATE PROCEDURE sp_CursorDeSalida

@IdTar CHAR(1),
@CursosTar CURSOR VARYING OUTPUT
AS
SET @CursosTar = CURSOR
FORWARD_ONLY STATIC FOR
SELECT * FROM Curso
WHERE IdTarifa = @IdTar
OPEN @CursosTar
GO
Probando el SP:
DECLARE @MiCursor CURSOR
EXEC sp_CursorDeSalida ‘A’,
@CursosTar=@MiCursor OUTPUT
FETCH NEXT FROM @MiCursor
WHILE (@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM @MiCursor
END
CLOSE @MiCursor
DEALLOCATE @MiCursor
GO
5) Devolviendo código de Retorno (RETURN)
CREATE PROCEDURE CPmasDe2V
@IdCP Char(4)
AS
IF (SELECT Count(*) FROM CursoProgramado
WHERE IdCurso=@IdCP) > 2
RETURN 1
ELSE
RETURN 2
GO
Probando el SP:
DECLARE @CP INT
EXECUTE @CP=CPmasDe2V ‘C001’
SELECT @CP
- Probar con C901

Instructor: Julio E. Flores Manco 44

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejercicio propuesto de Cursores:

CREATE PROCEDURE sp_AlumnoMatricula

@IdAlu Char(5), @ListaAluMat CURSOR VARYING OUTPUT
AS
SET @ListaAluMat = CURSOR
FORWARD_ONLY STATIC FOR
SELECT * FROM Matricula WHERE IdAlumno=@IdAlu
OPEN @ListaAluMat
GO

PARTE 3

Ejemplo 1

Crear un SP spu_CantPedPorProd que muestre la cantidad Pedida para un

Articulo determinado (@Nombre);
usar la BD NortWind . Escriba el Script necesario para probar el SP

SOLUCION:

USE NorthWind
GO
CREATE PROCEDURE spu_CantPedPorProd
@Nombre varchar(40)='%Queso%'
AS
SELECT [Link] AS [NOMBRE DEL PRODUCTO],SUM([Link])AS
[CANTIDAD PEDIDA]
FROM Products P INNER JOIN [Order Details] OD
ON [Link] = [Link]
GROUP BY [Link]
HAVING [Link] LIKE @Nombre
GO
EXEC spu_CantPedPorProd '%Tofu%'
GO

Ejemplo 2

Crear un SP spu_GuiaTranspDirec que muestre el Nombre del

transportispa(@NP) y la dirección del local (@DIR),
teniendo como dato de entrada al Código de la Guía (@GUIA).
Usar la BD MarketPERU. Escriba el Script necesario para probar el SP

Instructor: Julio E. Flores Manco 45

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

SOLUCION:

USE MarketPERU
GO
CREATE PROCEDURE spu_GuiaTranspDirec
@GUIA INT,@NP VARCHAR(30) OUTPUT,@DIR VARCHAR(60) OUTPUT
AS
SELECT @NP =TRANSPORTISTA,@DIR = DIRECCION
FROM GUIA G INNER JOIN LOCAL L
ON [Link] = [Link]
WHERE [Link] = @GUIA
GO
DECLARE @N VARCHAR(30),@D VARCHAR(60)
EXECUTE PRE4B 5,@N OUTPUT,@D OUTPUT
SELECT @N,@D
GO

Ejemplo 3

Desarrollar un SP que permita matricular a un Alumno spu_MatriculaAlumno en

la BD EduTec.
Se debe verificar si existen vacantes y si el curso está activo,
Además se debe incrementar el número de matriculados y disminuir las
vacantes.
Escriba el Script necesario para probar el SP.

Los valores de retorno son:

0 Ok 1 Valor nulo
2 Curso no programado 3 Alumno no
registrado
4 No hay vacantes para el curso 5 El curso ya no esta
activo.
6 Error de BD

Instructor: Julio E. Flores Manco 46

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

SOLUCION:

USE EduTec
GO
CREATE PROCEDURE spu_MatriculaAlumno
@cursoprog TINYINT,
@idalumno char(5),
@fecmatricula DATETIME
AS
DECLARE @vacantes TINYINT
DECLARE @activo TINYINT
IF (@cursoprog IS NULL) OR (@idalumno IS NULL) OR (@fecmatricula IS
NULL)
BEGIN
PRINT 'VALOR NULO'
RETURN 1
END
IF NOT EXISTS(SELECT idcursoprog FROM cursoprogramado WHERE
idcursoprog = @cursoprog)
BEGIN
PRINT 'ESTE CURSO NO ESTA PROGRAMADO'
RETURN 2
END
IF NOT EXISTS(SELECT apealumno + ', ' nomalumno FROM alumno WHERE
IDALUMNO = @idalumno)
BEGIN
PRINT 'EL ALUMNO NO ESTA REGISTRADO'
RETURN 3
END
SELECT @vacantes = vacantes, @activo = activo FROM cursoprogramado
WHERE idcursoprog = @cursoprog
IF @vacantes = 0
BEGIN
PRINT 'YA NO HAY VACANTES PARA ESTE CURSO'
RETURN 4
END
IF @activo = 0
BEGIN
PRINT 'EL CURSO YA NO ESTA ACTIVO'
RETURN 5
END
BEGIN TRAN
UPDATE CursoProgramado
SET vacantes = vacantes -1, matriculados = matriculados + 1
WHERE idcursoprog = @cursoprog
INSERT INTO matricula (idcursoprog,idalumno,fecmatricula)
VALUES (@cursoprog,@idalumno,@fecmatricula)
IF @@ERROR <> 0

Instructor: Julio E. Flores Manco 47

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

BEGIN
PRINT ' ERROR EN LA BD'
ROLLBACK TRAN
RETURN 6
END
COMMIT TRAN
RETURN 0
GO

-- Prueba del SP
DECLARE @RET INT
EXEC @RET = spu_MatriculaAlumno 6,'A0008','1999-01-03'
SELECT @RET
GO
-- Verificando si el Alumno esta matriculado
select * from matricula where idalumno='A0008'
GO
select * from cursoprogramado where idcursoprog = '6'
GO
select * from matricula where idcursoprog = 6
GO

PARTE 4

USE MARKETPERU

Ejemplo 1

Seleccionar Proveedores de Un Departamento Determinado y Que Empiece El

Nombre Con una Letra Determinada

Create Procedure ProvDep

@Depcli varchar(30), @Nom varchar(50)
As
Select * from proveedor p
where [Link] = @Depcli and [Link] like @Nom
Go

--Ejecutar

Declare @Depcli Varchar(30), @Nom Varchar(50)

Exec ProvDep 'LIMA', 'D%'

Instructor: Julio E. Flores Manco 48

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejemplo 2

Crear un SP que totalice Los Totales de Las Guias Mayores a un Número

Determinado
Create Procedure TotalesGuiasMayorA
@Valor float
As
Select [Link], Sum([Link] * [Link])
from Guia G inner join Guia_Detalle GD
on [Link] = [Link]
Group by [Link]
Having Sum([Link] * [Link]) > @Valor
Go

--Ejecutar

Declare @Valor Float

exec TotalesGuiasMayorA 10000

Ejemplo 3

Cree un SP que muestre la cantidad de productos vendidos por cada Categoria

Create procedure CategoriasVendidas
As
Select [Link], count([Link]) as 'Cantidad Vendida'
from Guia_Detalle G inner join Producto P
on [Link] = [Link]
inner join Categoria C
on [Link] = [Link]
group by categoria
Go
--Ejecutar
Exec CategoriasVendidas

Ejemplo 4

Crear un Sp que muestre la cantidad de unidades vendidas de un Producto en

particular
Create Procedure ProdVendidosID
@IdProd varchar(10)
As
Select [Link], [Link], Sum([Link]) as 'Cantidad Vendida'
from Guia_Detalle G inner join Producto P
on [Link] = [Link]
inner join Categoria C
on [Link] = [Link]
group by [Link], [Link]
having [Link] =@idprod

Instructor: Julio E. Flores Manco 49

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Go
--Ejecutar
declare @Idprod varchar(10)
exec ProdVendidosId '54'

Ejemplo 5

Crear un Sp que muestre los Productos Comprados a un Proveedor

determinado

create procedure ComprasProveedor

@Idprov int
AS
Select [Link], [Link], [Link], [Link]
from Producto P Inner join Proveedor PV
on [Link] = [Link]
where [Link]=@Idprov order by [Link] asc
Go
--Ejecutar
declare @Idprov int
Exec ComprasProveedor 12

Ejercicios propuestos:

Ejemplo 4

Mostrar en un CURSOR los Cursos para un Alumno determinado.

Usar la BD EduTec.
Ejemplo 5

En la BD PUBS Crear un SP que muestre una lista de libros cuyo titulo empiece
con "T".

Instructor: Julio E. Flores Manco 50

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

CAP IV
Triggers o Desencadenantes
Un Trigger es un mecanismo que ofrece SQL Server para exigir reglas de
negocio e integridad de datos .
Un desencadenante es un procedimiento almacenado de tipo especial que actúa
automáticamente cuando se modifican los datos de la tabla.
Los desencadenantes se invocan en respuesta a las instrucciones INSERT,
UPDATE y DELETE.

Un desencadenador puede consultar otras tablas e incluir instrucciones

Transact-SQL complejas.
El desencadenante y la instrucción que la activa se tratan como una sola
transacción que puede deshacerse desde el mismo desencadenante.
Si se detecta un error grave (por ejemplo, no hay suficiente espacio en disco),
se deshace automáticamente toda la transacción.

Los desencadenantes pueden realizar cambios en cascada por medio de tablas

relacionadas de la base de datos; sin embargo, estos cambios pueden
ejecutarse de manera más eficaz mediante restricciones de integridad
referencial en cascada.
Los desencadenantes pueden exigir restricciones más complejas que las
restricciones CHECK

Los desencadenantes pueden evaluar el estado de una tabla antes y después

de realizar una modificación de datos y actuar en función de la diferencia.

Varios desencadenantes del mismo tipo (INSERT, UPDATE o DELETE) en una

tabla permiten realizar distintas acciones en respuesta a una misma instrucción
de modificación.

Tanto las restricciones como los desencadenantes ofrecen ventajas específicas

que resultan útiles en determinadas situaciones. La principal ventaja de los
desencadenantes consiste en que pueden contener una lógica de proceso
compleja que utilice código Transact-SQL.
Por tanto, los desencadenante permiten toda la funcionalidad de las
restricciones; sin embargo, no son siempre el mejor método para realizar una
determinada función.

Instructor: Julio E. Flores Manco 51

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Diseño de Desencadenantes
Podemos diseñar desencadenantes INSTEAD OF y AFTER.

Los desencadenantes INSTEAD OF se ejecutan antes de la instrucción que lo

invoca y pueden diseñarse para tablas o vistas. Los desencadenantes
INSTEAD OF en vistas con una o más tablas base, pueden ampliar los tipos
de actualizaciones que puede admitir una vista.

Los desencadenadores AFTER se ejecutan después de llevar a cabo una

acción de las instrucciones INSERT, UPDATE o DELETE. La especificación de
AFTER produce el mismo efecto que especificar FOR, que es la única opción
disponible en las versiones anteriores de SQL Server. El desencadenador
AFTER sólo puede especificarse en tablas.

Cuadro de Resumen

Instructor: Julio E. Flores Manco 52

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

-- Este demo1 debe ejecutar paso a paso, batch por batch

-- Establecer la base de datos

use edutec

-- Paso 01
-- Creación de un desencadenante AFTER para INSERT

create trigger tr_insert_curso

on curso
after insert
as
print 'Se ha ejecutado Trigger AFTER de INSERT'
GO

-- Paso 02
-- Probar el trigger

insert curso values( 'C001','A','Microsoft Word XP')

-- Comente el resultado
--

-- Paso 03
-- Probar el trigger

insert curso values( 'C902','A','Microsoft Word XP')

go
Select * From Curso
go

-- Comente el resultado
--

-- Paso 04
-- Eliminar el trigger

drop trigger tr_insert_curso

-- Paso 05
-- Creación de un desencadenante INSTEAD OF para INSERT

Create trigger tr_insert_curso

on curso
instead of insert
as
print 'Se ha ejecutado Triger INSTEAD OF de INSERT'

Instructor: Julio E. Flores Manco 53

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

GO

-- Paso 06
-- Probar el trigger

insert curso values( 'C900','A','Microsoft Word XP')

go
Select * From Curso
go

-- Comente el resultado
--

-- Paso 07
-- Probar el trigger

insert curso values( 'C901','A','Microsoft Excel XP')

go
Select * From Curso
go

-- Comente el resultado
--

-- Paso 08
-- Modificando el desencadenante INSTEAD OF para INSERT

Alter trigger tr_insert_curso

on curso
instead of insert
as
print 'Se ha ejecutado Triger INSTEAD OF de INSERT'
print 'El contenido de la tabla inserted es:'
select * from inserted
GO

-- Paso 09
-- Probar el trigger

insert curso values( 'C900','A','Microsoft Word XP')

go
Select * From Curso
go

-- Comente el resultado
--

-- Paso 10

Instructor: Julio E. Flores Manco 54

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

-- Probar el trigger

insert curso values( 'C901','A','Microsoft Excel XP')

go
Select * From Curso
go

-- Comente el resultado
--

-- Paso 11
-- Modificando el desencadenante INSTEAD OF para INSERT

Alter trigger tr_insert_curso

on curso
instead of insert
as
print 'Se ha ejecutado Triger INSTEAD OF de INSERT'
print 'El contenido de la tabla inserted es:'
select * from inserted
insert into curso select * from inserted
GO

-- Paso 12
-- Probar el trigger

insert curso values( 'C900','A','Microsoft Word XP')

go
Select * From Curso
go

-- Comente el resultado
--

-- Paso 13
-- Probar el trigger

insert curso values( 'C901','A','Microsoft Excel XP')

go
Select * From Curso
go

-- Comente el resultado
--

-- Paso 14
-- Eliminar el desencadenante

Instructor: Julio E. Flores Manco 55

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Drop trigger tr_insert_curso

-- Paso 15
-- Trigger que cancela una Transacción

create trigger tr_insert_curso

on curso
after insert
as
print 'Se ha ejecutado Triger AFTER de INSERT'
select * from inserted
print 'Se cancela la inserción'
rollback tran
GO

-- Paso 16
-- Probar el trigger

insert curso values( 'C902','A','Microsoft Access XP')

go
Select * From Curso
go

-- Comente el resultado
--

-- Paso 17
-- Eliminar el desencadenante

Drop trigger tr_insert_curso

Instructor: Julio E. Flores Manco 56

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Creación de Desencadenantes
Antes de crear un desencadenante, tenga en cuenta que:

 La instrucción CREATE TRIGGER debe ser la primera del batch. Las

demás instrucciones del batch se interpretan como parte de la definición
de la instrucción CREATE TRIGGER.

 De forma predeterminada, el permiso para crear un desencadenante

corresponde al propietario de la tabla, y no puede transferirlo a otros
usuarios.

 Los desencadenantes son objetos de base de datos y sus nombres deben

ajustarse a las reglas definidas para los identificadores.

 Sólo se pueden crear desencadenantes en la base de datos actual,

aunque un desencadenante puede hacer referencia a objetos que se
encuentren fuera de esta base de datos.

 No es posible crear un desencadenante en una tabla temporal o del

sistema, aunque los desencadenantes pueden hacer referencia a tablas
temporales. No se debe hacer referencia a las tablas del sistema; en su
lugar, utilice las vistas de esquema de información.

 No se puede definir desencadenantes INSTEAD OF DELETE y INSTEAD

OF UPDATE en una tabla que tenga una clave externa definida con una
acción DELETE o UPDATE.
 Aunque una instrucción TRUNCATE TABLE sea como una instrucción
DELETE sin la cláusula WHERE (elimina todas las filas), no provoca la
activación de los desencadenantes DELETE, porque es una instrucción
que no se registra.

 La instrucción WRITETEXT no provoca la activación de los

desencadenantes INSERT ni UPDATE.

Cuando creamos un desencadenante, debemos especificar:

 El nombre.

 La tabla en la que se define el desencadenante.

 El momento de activar el desencadenante.

Instructor: Julio E. Flores Manco 57

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

 Las instrucciones de modificación de datos que activarán el

desencadenante. Son opciones válidas: INSERT, UPDATE o
DELETE. Varias instrucciones de modificación de datos pueden
activar el mismo desencadenante. Por ejemplo, se puede activar
un desencadenante mediante instrucciones INSERT y UPDATE.

 Las instrucciones que debe ejecutar el desencadenante.

Demo2

-- Este demo2 debe ejecutar paso a paso, batch por batch

-- Establecer la base de datos

use edutec

-- Paso 01
-- Creación de un desencadenante

create trigger tr_insert_matricula

on matricula
after insert
as
declare @vacantes int
select @vacantes = [Link]
from CursoProgramado CP, Inserted I
where [Link] = [Link]
if (@Vacantes = 0)
begin
print 'No hay vacantes'
rollback tran
end
else
begin
update CursoProgramado
set Vacantes = Vacantes - 1,
Matriculados = Matriculados + 1
from Inserted I
where [Link] = [Link]
print 'Base de Datos actualizada'
end
GO

-- Paso 02
-- Probar el desencadenante

select * from CursoProgramado where IdCursoProg = 1

Instructor: Julio E. Flores Manco 58

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

go

insert into Matricula(IdCursoProg,IdAlumno,FecMatricula)

values(1,'A0011','19990105')
go

select * from cursoprogramado where IdCursoProg = 1

go

select * from matricula where idcursoprog = 1

go

-- Paso 03
-- Probar el desencadenante

update CursoProgramado
set Vacantes = 0
where IdCursoProg = 1
go

select * from CursoProgramado where IdCursoProg = 1

go

insert into Matricula(IdCursoProg,IdAlumno,FecMatricula)

values(1,'A0012','19990105')
go

select * from cursoprogramado where IdCursoProg = 1

go

select * from matricula where idcursoprog = 1

go

Instructor: Julio E. Flores Manco 59

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Desencadenantes Múltiples
 Una tabla puede tener varios desencadenantes AFTER de un tipo
determinado, siempre que tengan nombres distintos.

 Todo desencadenante puede llevar a cabo numerosas funciones. Sin

embargo, sólo se puede aplicar a una tabla.

 Un único desencadenante puede aplicarse a cualquier subconjunto de las

tres acciones del usuario (UPDATE, INSERT y DELETE).

-- Este demo3 debe ejecutar paso a paso, batch por batch

-- Establecer la base de datos

use edutec

-- Paso 01
-- Creación de un desencadenante

create trigger tr_insert_alumno1

on alumno
after insert
as
print 'Primer desencadenante ejecutado'
GO

-- Paso 02
-- Probar el desencadenante

insert into Alumno(IdAlumno,ApeAlumno,NomAlumno)

values('A9000','AGUILAR OLARTE','WALTER')
go

select * from alumno

go

-- Paso 03
-- Crear otro desencadenante

create trigger tr_insert_alumno2

on alumno
after insert
as
print 'Segundo desencadenante ejecutado'
GO

Instructor: Julio E. Flores Manco 60

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

-- Paso 04
-- Probar el desencadenante

insert into Alumno(IdAlumno,ApeAlumno,NomAlumno)

values('A9001','ARENAS MATA','FANNY ROCIO')
go

select * from alumno

go

-- Paso 05
-- Crear otro desencadenante

create trigger tr_insert_alumno3

on alumno
after insert,update
as
print 'Tercer desencadenante ejecutado'
GO

-- Paso 06
-- Probar el desencadenante

insert into Alumno(IdAlumno,ApeAlumno,NomAlumno)

values('A9002','BRUNO SALAZAR','MILAGROS')
go

select * from alumno

go

-- Paso 07
-- Probar el desencadenante

update alumno
set emailalumno = 'mbruno@[Link]'
where idalumno = 'A9002'
go

select * from alumno

go

Instructor: Julio E. Flores Manco 61

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Programar Desencadenantes
Para programar desencadenantes podemos utilizar casi todas las
instrucciones Transact-SQL que se puedan escribir dentro de un batch,
excepto los siguientes:

ALTER DATABASE CREATE DATABASE DISK INIT

DISK RESIZE DROP DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE RESTORE DATABASE
RESTORE LOG

Las instrucciones DISK RESIZE, DISK INIT, LOAD DATABASE y LOAD LOG se
incluyen en Microsoft SQL Server 2014 sólo por compatibilidad con versiones
anteriores y puede que no se admitan en el futuro.

Probar Cambios en Columnas

 La utilización de la cláusula IF UPDATE (nombre_columna) en la
definición de un desencadenante puede servir para determinar si una
instrucción INSERT o UPDATE ha afectado a una determina columna de
la tabla. La cláusula da como resultado el valor TRUE (verdadero)
siempre que se haya asignado un valor a la columna.

 Ya que no es posible eliminar un valor específico de una columna

mediante la instrucción DELETE, la cláusula IF UPDATE no se aplica a
esta instrucción.

 Como alternativa, la cláusula IF COLUMNS_UPDATED() se puede utilizar

para comprobar qué columnas de la tabla se actualizaron mediante una
instrucción INSERT o UPDATE. Esta cláusula utiliza una máscara de bits
de enteros para especificar las columnas que deben comprobarse.

-- Este demo4 debe ejecutar paso a paso, batch por batch

-- Establecer la base de datos

use edutec

-- Paso 01
-- Creación de un desencadenante

create trigger tr_update_alumno1

on alumno
after update
as

Instructor: Julio E. Flores Manco 62

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

if update(Apealumno)
begin
print 'No se puede modificar el apellido del alumno'
rollback tran
end
GO

-- Paso 02
-- Probar el desencadenante

select * from alumno where IdAlumno = 'A0001'

go

update alumno
set ApeAlumno = 'Prueba'
where IdAlumno = 'A0001'
go

select * from alumno where IdAlumno = 'A0001'

go

-- Paso 03
-- Creación de un desencadenante

create trigger tr_update_alumno2

on alumno
after update
as
if columns_updated() = 4
begin
print 'No se puede modificar el nombre del alumno'
rollback tran
end
GO

-- Paso 04
-- Probar el desencadenante

select * from alumno where IdAlumno = 'A0001'

go

update alumno
set NomAlumno = 'Prueba'
where IdAlumno = 'A0001'
go

select * from alumno where IdAlumno = 'A0001'

go

Instructor: Julio E. Flores Manco 63

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Tablas inserted y deleted

 En los desencadenantes se utilizan dos tablas especiales: la tabla deleted
y la tabla inserted. SQL Server 2014 crea y administra automáticamente
estas tablas. Puede utilizar estas tablas temporales residentes en la
memoria para probar los efectos de algunas modificaciones de datos y
para establecer condiciones para acciones del desencadenante; sin
embargo, no puede modificar directamente los datos de estas tablas.

 Las tablas inserted y deleted se utilizan principalmente en

desencadenantes para:

 Ampliar la integridad referencial entre tablas.

 Insertar o actualizar datos de tablas base subyacentes a una vista.

 Comprobar errores y realizar acciones en función del error.

 Diferenciar entre el estado de una tabla antes y después de
realizar una modificación en los datos para actuar en función de
esa diferencia.

 La tabla deleted almacena copias de las filas afectadas por las

instrucciones DELETE y UPDATE. Durante la ejecución de una instrucción
DELETE o UPDATE, las filas se eliminan de la tabla del desencadenante y
se transfieren a la tabla deleted. La tabla deleted y la tabla del
desencadenante no suelen tener filas en común.

 La tabla inserted almacena copias de las filas afectadas durante las

instrucciones INSERT y UPDATE. Durante una transacción de inserción o
actualización, se agregan nuevas filas a la tabla inserted y a la tabla del
desencadenante de forma simultánea. Las filas de la tabla inserted son
copias de las nuevas filas de la tabla del desencadenante.

Demo5

-- Este demo5 debe ejecutar paso a paso, batch por batch

-- Establecer la base de datos

use edutec

-- Paso 01
-- Creación de un desencadenante

Instructor: Julio E. Flores Manco 64

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

create trigger tr_insert_profesor

on profesor
after insert
as
if ( select count(*)
from profesor p, inserted i
where ([Link] = [Link]) and
([Link] = [Link]) ) > 1
begin
print 'El profesor ya se encuentra registrado.'
print 'Acción Cancelada'
rollback tran
end
else
print 'Datos Registrados Satisfactoriamente.'
GO

-- Paso 02
-- Probar el desencadenante

select * from profesor where IdProfesor = 'P001'

go

Insert into Profesor(IdProfesor,ApeProfesor,NomProfesor)

Values('P800','Valencia Morales','Pedro Hugo')
go

select * from profesor where IdProfesor = 'P001'

go

-- Paso 03
-- Probar el desencadenante

Insert into Profesor(IdProfesor,ApeProfesor,NomProfesor)

Values('P800','Castro Escobar','Lidia Rosa')
go

select * from profesor

go

-- Paso 04
-- Creación de un desencadenante

Create trigger tr_update_profesor

on profesor
after update
as

Instructor: Julio E. Flores Manco 65

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

if ( Select Count(*)
From Inserted I, Deleted D
Where [Link] = [Link] ) = 0
begin
print 'No se puede modificar el código del profesor'
rollback tran
end
else
print 'Datos Actualizados Satisfactoriamente.'
GO

-- Paso 05
-- Probar el desencadenante

select * from profesor where Idprofesor = 'P042'

go

update profesor
set IdProfesor = 'AAAA'
where IdProfesor = 'P042'
go

-- Paso 06
-- Probar el desencadenante

select * from profesor where Idprofesor = 'P002'

go

update profesor
set EmailProfesor = 'gcoronel@[Link]'
where IdProfesor = 'P002'
go

select * from profesor where Idprofesor = 'P002'

go

Instructor: Julio E. Flores Manco 66

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejemplos adicionales

Ejemplo 1

CREATE TRIGGER tr_ActualizarPassword

ON Empleado
FOR UPDATE
AS
IF UPDATE(Password)
BEGIN
PRINT 'No se puede cambiar el Password'
ROLLBACK
END
GO

Ejemplo 2

USE Ejemplos
GO

CREATE TABLE CliBorrados (

[cod_cli] [int] NOT NULL ,
[nombre] [char] (30) NOT NULL ,
[ciudad] [varchar] (15) NOT NULL ,
[telefono] [varchar] (8) NULL
)
GO

CREATE TRIGGER tr_Copia_Borrados

ON Clientes FOR DELETE
AS
Insert Into CliBorrados Select * From deleted
Select * From Clientes
Select * From CliBorrados
GO
--Ejecución:
Select * From Clientes
Select * From CliBorrados
GO

Delete From CliBorrados Where cod_cli=2

GO

Instructor: Julio E. Flores Manco 67

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

CREATE TRIGGER tr_Recupera_Borrado

ON CliBorrados FOR DELETE
AS
Insert Into Clientes Select * From deleted
Select * From Clientes
Select * From CliBorrados
GO

Ejemplo 3

USE MarketPERU
GO

CREATE TRIGGER DescuentaStock

ON GUIA_DETALLE
FOR INSERT
AS
-- @Cantidad Almacena el numero de productos a pedir
-- @Unidades Almacena el Stock actual en la Tabla PRODUCTO

DECLARE @Cantidad Integer,

@Unidades Integer

-- Asignamos a la variable el contenido del campo

SELECT @Cantidad = [Link]

FROM GUIA_DETALLE GD INNER JOIN INSERTED I
ON [Link] = [Link]

-- Asignamos a la variable el contenido del campo

SELECT @Unidades = [Link]

FROM PRODUCTO P INNER JOIN INSERTED I
ON [Link] = [Link]

BEGIN TRANSACTION

--Verificamos la cantidad pedida

IF @Cantidad <= @Unidades

BEGIN
--ACTUALIZAMOS EL STOCK
UPDATE PRODUCTO
SET StockActual = @Unidades-@Cantidad
FROM PRODUCTO P INNER JOIN INSERTED I
ON [Link] = [Link]

Instructor: Julio E. Flores Manco 68

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

COMMIT TRANSACTION
PRINT 'Stock Actualizado'
END
ELSE
BEGIN
-- Deshacemos la Transaccion
PRINT 'No existen suficientes productos para satisfacer el pedido'
ROLLBACK
END
GO

--Probando el Trigger

INSERT GUIA VALUES(108,2,'15/06/2003','VELASQUEZ ORTIZ,FRANCISCO')

SELECT * FROM GUIA

SELECT * FROM PRODUCTO WHERE IdProducto = 2
SELECT * FROM GUIA_DETALLE WHERE IdGuia=108

INSERT GUIA_DETALLE VALUES(108,2,1.50,50)

Instructor: Julio E. Flores Manco 69

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

CAP V

Funciones definidas por el

programador
Las funciones, en los lenguajes de programación, son subrutinas que se utilizan
para encapsular lógica que se ejecuta frecuentemente. Un código que deba
ejecutar la lógica incorporada en una función puede llamarla en vez de tener
que repetir toda la lógica de la función.

SQL Server 2014 admite dos tipos de funciones:

 Funciones integradas

Funcionan como se define en la referencia de Transact-SQL y no se

pueden modificar. Sólo es posible hacer referencia a las funciones en
instrucciones Transact-SQL que utilizan la sintaxis definida en la
referencia de Transact-SQL. Para obtener más información acerca de
estas funciones integradas, consulte Utilizar las funciones.

 Funciones definidas por el usuario

Le permiten definir sus propias funciones Transact-SQL mediante la

instrucción CREATE FUNCTION. Para obtener más información acerca de
estas funciones integradas, consulteFunciones definidas por el usuario.

Las funciones definidas por el usuario no tienen ninguno o tienen varios

parámetros de entrada y devuelven un único valor. Algunas funciones definidas
por el usuario devuelven un único valor de datos escalar, como un valor int,
char o decimal.

Instructor: Julio E. Flores Manco 70

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

 Funciones escalares

 Similar a una función integrada

 Funciones con valores de tabla de varias instrucciones

 Contenido como un procedimiento almacenado

 Se hace referencia como una vista

 Funciones con valores de tabla en línea

 Similar a una vista con parámetros

 Devuelve una tabla como el resultado de una instrucción SELECT
única

Definición de funciones definidas por el

usuario

Creación de una función definida por el usuario

 Creación de una función

CREATE FUNCTION fn_Prueba01

(@N1 Int, @N2 Int)
RETURNS Int
BEGIN
DECLARE @Suma Int
SET @Suma = @N1 + @N2
RETURN @Suma
END

 Restricciones de las funciones

Creación de una función con enlace a esquema

 Todas las funciones definidas por el usuario y las vistas a las que la
función hace referencia también están enlazadas a esquema
 No se utiliza un nombre de dos partes para los objetos a los que hace
referencia
 La función y los objetos se encuentran todos en la misma base de datos
 Tiene permiso de referencia en los objetos requeridos

Instructor: Julio E. Flores Manco 71

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Establecimiento de permisos para funciones definidas por el usuario

 Necesita permiso para CREATE FUNCTION

 Necesita permiso para EXECUTE

 Necesita permiso para REFERENCE en las tablas, vistas o funciones

citadas

 Debe ser propietario de la función para utilizar la instrucción CREATE o

ALTER TABLE

Modificación y eliminación de funciones definidas por el usuario

 Modificación de funciones

ALTER FUNCTION dbo.fn_Nombre

<Nuevo Contenido>

 Conserva los permisos asignados

 Hace que la definición de la función nueva reemplace a la
definición existente

 Eliminación de funciones

DROP FUNCTION dbo.fn_Nombre

Ejemplos de funciones definidas por el usuario

 Uso de una función escalar definida por el usuario

 Ejemplo de una función escalar definida por el usuario
 Uso de una función con valores de tabla de varias instrucciones
 Ejemplo de una función con valores de tabla de varias instrucciones
 Uso de una función con valores de tabla en línea
 Ejemplo de una función con valores de tabla en línea

Uso de una función escalar definida por el usuario

 La cláusula RETURNS especifica el tipo de dato

 La función se define en un bloque BEGIN y END

 El tipo de devolución puede ser cualquier tipo de datos, excepto text,

ntext, image, cursor o timestamp

Instructor: Julio E. Flores Manco 72

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejemplo de una función escalar definida por el usuario

 Creación de la función

CREATE FUNCTION fn_DateFormat

(@indate datetime, @separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(dd,@indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(mm, @indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(yy, @indate))
END

 Llamada a la función

Select dbo.fn_DateFormat( GetDate(), ':' )

Uso de una función con valores de tabla de varias

instrucciones

 BEGIN y END contienen múltiples instrucciones

 La cláusula RETURNS especifica el tipo de datos de la tabla

 La cláusula RETURNS da nombre y define la tabla

Instructor: Julio E. Flores Manco 73

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejemplo de una función con valores de tabla de

varias instrucciones
 Creación de la función

CREATE FUNCTION fn_Cursos (@Ciclo Char(7))

RETURNS @fn_Cursos TABLE
(Id int PRIMARY KEY NOT NULL, Ciclo Char(7), Curso VarChar(50),
Profesor VarChar(60), Horario VarChar(24), Matriculados TinyInt)
AS
BEGIN
INSERT @fn_Cursos SELECT [Link], [Link],
[Link], [Link] + ', ' + [Link],
[Link], [Link]
FROM Curso C Inner Join CursoProgramado CP
On [Link] = [Link] Inner Join Profesor P
On [Link] = [Link]
Where [Link] = @Ciclo
RETURN
END

 Llamada a la función

Select * From fn_Cursos('1999-01')

Uso de una función con valores de tabla en línea

 El contenido de la función es una instrucción SELECT

 No utilice BEGIN y END

 RETURN especifica TABLE como el tipo de dato

 El formato se define por el conjunto de resultados

Instructor: Julio E. Flores Manco 74

Universidad Nacional de Ingeniería Curso: SQL Server 2014 –Nivel II
Centro de Extensión y Proyección Social Carreras Técnicas

Ejemplo de una función con valores de tabla en línea

 Creación de la función

CREATE FUNCTION fn_Notas ( @Alumno Char(5))

RETURNS table
AS
RETURN (
Select [Link], [Link], [Link], [Link],
[Link], [Link], [Link], [Link]
From Matricula M
Inner Join CursoProgramado CP
On [Link] = [Link]
Inner Join Curso C
On [Link] = [Link]
Where [Link] = @Alumno )

 Llamada a la función mediante un parámetro

Select * From fn_Notas( 'A0001' )

Recomendaciones

 Utilice funciones escalares complejas con conjuntos de resultados

pequeños

 Utilice funciones con varias instrucciones en lugar de procedimientos

almacenados que devuelven tablas

 Utilice funciones en línea para crear vistas parametrizadas

Instructor: Julio E. Flores Manco 75