1. No category

Módulo VI: Procedimientos y Desencadenadores


Procedimientos Almacenados
Implementación de Desencadenadores
© Maximiliano Odstrcil - 1
Procedimientos Almacenados

Introducción

Creación, Ejecución y Modificación

Uso de Parámetros

Procedimientos Extendidos

Manejo de Mensajes de Error
© Maximiliano Odstrcil - 2
Introducción

Definición
Ventajas de los SP

Proceso Inicial de los SP

Proceso Subsiguiente de los SP

© Maximiliano Odstrcil - 3
Definición (1)
 Un
Procedimiento Almacenado (Stored Procedure) es
una colección de sentencias SQL, con un nombre,
almacenadas en el servidor, dentro de la BD.
 La
finalidad de un procedimiento almacenado es análoga
a la de una función, subprograma o método en un
lenguaje de programación:
 encapsular tareas repetitivas.
© Maximiliano Odstrcil - 4
Definición (2)
 Características:
 Soportan la declaración de variables, ejecución
condicional y otras características de programación.
 Aceptan parámetros de entrada y devuelven valores.
 Devuelven un valor de estado que indica éxito o falla.
 Pueden llamar a otros procedimientos almacenados.
© Maximiliano Odstrcil - 5
Definición (3)
 Tipos





de procedimientos:
Del sistema
Locales
Temporales
Remotos
Extendidos
© Maximiliano Odstrcil - 6
Definición (4)
 Procedimientos
almacenados del sistema:
 Procedimientos almacenados con que cuenta el SGBDR.
Sirven para devolver la información de las tablas del sistema y
ejecutar tareas de mantenimiento.
 MySQL no tiene estos tipos de procedimientos.
 En SQL Server se almacenan en la BD master y sus nombres
comienzan con sp_. Ejemplo: sp_help.
© Maximiliano Odstrcil - 7
Definición (5)
 Procedimientos
almacenados locales:
 Procedimientos creados en las BD de usuario individuales.

Procedimientos almacenados temporales:
 En SQL Server sus nombres empiezan con # o ## (si son
locales o globales).
 No son soportados por MySQL.

Procedimientos almacenados remotos:
 Soportan la funcionalidad de consultas
distribuidas.
© Maximiliano Odstrcil - 8
y
operaciones
Definición (6)

Procedimientos almacenados extendidos:
 Procedimientos implementados como vínculos dinámicos a
librerías (DLLs) que se ejecutan fuera del ambiente del
servidor.
 No son soportados por MySQL.
 En SQL Server, sus nombres comienzan con xp_ y son
ejecutados de manera similar a los SP. Algunos
procedimientos almacenados de sistema llaman en su cuerpo
a estos procedimientos.
© Maximiliano Odstrcil - 9
Ventajas de los SP (1)
 Encapsulan
la lógica de negocio y crean una lógica
de la aplicación reusable:
 Las reglas o políticas de negocio encapsuladas en SP se
pueden modificar en un único lugar. Todos los clientes pueden
usar los mismos procedimientos almacenados asegurándose
un acceso consistente a los datos.

Ocultan a los usuarios la complejidad subyacente y detalles
internos de la BD:
 Si todas las funciones de negocio se soportan mediante un
conjunto de procedimientos almacenados, los usuarios no
deben acceder a las tablas directamente.
© Maximiliano Odstrcil - 10
Ventajas de los SP (2)
 Proveen
mecanismos de seguridad:
 Se puede asignar permisos a los usuarios para ejecutar un
procedimiento almacenado, aún cuando no tengan permisos
para acceder a las tablas o vistas a que hacen referencia los
procedimientos.

Mejoran la perfomance:
 Los procedimientos almacenados implementan varias tareas
mediante una serie de sentencias SQL. Se puede aplicar
lógica condicional a los resultados de las primeras sentencias
para determinar las siguientes. Todas estas sentencias forman
parte de un único plan de ejecución.
© Maximiliano Odstrcil - 11
Ventajas de los SP (3)
Reducen el tráfico en la red:
 En vez de mandar miles de sentencias SQL por la red, los
usuarios mandan una única sentencia (el llamado al SP al
servidor) y reciben la respuesta.
 Reducen la vulnerabilidad debido a los ataques de SQL
injection:
 Mediante el uso de parámetros en el código SQL se reduce la
posibilidad que se puedan embeber sentencias SQL en los
valores de los parámetros.

© Maximiliano Odstrcil - 12
Proceso Inicial de los SP (1)
 Plan
de ejecución: muestra cómo navega por las tablas
y vistas el SGBDR, y cómo utiliza los índices para
ejecutar una consulta.
 Se
puede ver el plan de ejecución de la consulta que se
ejecutó (plan real), o la forma en que el SGBDR
ejecutará la consulta (plan estimado). Estos planes
pueden diferir ligeramente ya que el SGBDR tiene en
cuenta otras operaciones que se estén ejecutando en el
plan real.
© Maximiliano Odstrcil - 13
Proceso Inicial de los SP (2)
 El
proceso de creación de un SP consiste de 4 etapas:
 Análisis sintáctico (parsing): el SGBDR controla la
sintaxis del cuerpo del procedimiento y lo prepara
para su optimización. En esta etapa no se controlan
los nombres de los objetos o columnas.
 Normalización: el SGBDR verifica que todos los
nombres de objetos y columnas referenciados en el
cuerpo del procedimiento sean correctos y válidos.
© Maximiliano Odstrcil - 14
Proceso Inicial de los SP (3)
 El
proceso de creación de un SP consiste de 4 etapas:
 Compilación: el SGBDR construye el plan de
ejecución para el SP, creando grafos para todas las
sentencias del tipo DML, los cuales son usados por el
optimizador para optimizar el plan de ejecución, el
cual se guarda en el cache de procedimientos.
© Maximiliano Odstrcil - 15
Proceso Inicial de los SP (4)
 El
proceso de creación de un SP consiste de 4 etapas:
 Optimización: el optimizador determina el costo de
los diferentes posibles planes de ejecución, eligiendo
el menor. Para esto tiene en cuenta:
 Cantidad de datos en las tablas y naturaleza de
sus índices.
 Los operadores involucrados en la cláusula
WHERE.
 La presencia de Joins, UNION, GROUP BY y
ORDER BY.
© Maximiliano Odstrcil - 16
Proceso Inicial de los SP (5)
 La
primera vez que se ejecuta un SP, el SGBDR arma un
plan de ejecución (optimizado) para el mismo y lo guarda
en el cache de procedimientos.
 El cache de procedimientos es un área de memoria que
guarda temporalmente los planes de ejecución
compilados.
 La permanencia de los planes de ejecución viene dada
por:
 El tiempo requerido para su recompilación (costo).
 Su frecuencia de uso (edad).
© Maximiliano Odstrcil - 17
Proceso Inicial de los SP (6)
 Un
plan de ejecución consiste del plan en sí mismo y el
contexto de ejecución:
Execution
context
connection 1
Execution
context
connection 2
Execution
context
connection 3
Parameter
value: 12
Parameter
value: 24
Parameter
value: 36
Query plan
SELECT *
FROM MyTable
WHERE PriKey = ?
© Maximiliano Odstrcil - 18
Procedure cache
Age
Cost
Proceso Inicial de los SP (7)
Creación
Creación
Parsing
Parsing
Ejecución
Ejecución
Optimización
Optimización
(primera
(primeravez
vez
oorecompilación)
recompilación)
Compilación
Compilación
© Maximiliano Odstrcil - 19
Entradas
Entradas en
en las
las tablas
tablas
sysobjects
sysobjects yy syscomments
syscomments
Plan
Plan de
de compilación
compilación
en
en el
el cache
cache de
de proc.
proc.
Proceso Subsiguiente de los SP (1)
 Una
vez que se ejecutó un SP, su plan de ejecución
permanece en memoria, por lo que sucesivas
ejecuciones, cada una con un contexto de ejecución
diferente (incluyendo parámetros y variables), pueden
compartirlo, evitando los pasos de análisis, compilación y
optimización (ejecución más rápida).
 La
reutilización de los planes de ejecución es una de las
mayores ventajas de los SP.
© Maximiliano Odstrcil - 20
Creación, Ejecución y Modificación




Creación de SP
Recomendaciones
Ejecución de SP
Modificación y Borrado de SP
© Maximiliano Odstrcil - 21
Creación de SP (1)
 Se
puede crear un SP de manera similar a la creación de
vistas, con un asistente o mediante la sentencia CREATE
PROCEDURE.
 Consideraciones:
 Los SP pueden referenciar tablas, vistas, SP y tablas temporales.
 Si un SP crea una tabla temporal local, ésta deja de existir
cuando termina la ejecución del SP.
 En SQL Server la sentencia CREATE PROCEDURE es un batch
por si mismo.
 La sentencia CREATE PROCEDURE puede incluir casi cualquier
sentencia SQL.
© Maximiliano Odstrcil - 22
Creación de SP (2)
 Consideraciones:
 En SQL Server, para ejecutar CREATE PROCEDURE se debe
ser miembro de los roles sysadmin o db_owner, o se debe
tener permiso de creación de procedimientos.
 SQL Server:
CREATE
CREATE PROC
PROC dbo.overdue_books
dbo.overdue_books
AS
AS
SELECT
SELECT **
FROM
FROM dbo.loan
dbo.loan
WHERE
WHERE due_date
due_date << GETDATE()
GETDATE()
GO
GO
© Maximiliano Odstrcil - 23
Creación de SP (3)

MySQL:
DELIMITER
DELIMITER //
//
CREATE
CREATE PROCEDURE
PROCEDURE verEmpleados()
verEmpleados()
BEGIN
BEGIN
SELECT
SELECT ** FROM
FROM Employees;
Employees;
END
END //
//
DELIMITER
DELIMITER ;;
© Maximiliano Odstrcil - 24
Creación de SP (4)
 Los
SP pueden anidarse (cuando un SP llama a otro
SP). La características son:
 Puede haber hasta 32 niveles de anidamiento (SQL Server).
 El nivel de anidamiento actual se almacena en la variable global
llamada @@nestlevel (SQL Server).
 Si un SP llama a un segundo SP, este último puede acceder a
todos los objetos definidos localmente en el primero.
© Maximiliano Odstrcil - 25
Creación de SP (5)
En SQL Server:
 Para ver información sobre los SP se pueden usar los
procedimientos sp_help, sp_helptext y sp_depends.
 Para obtener una lista de SP: sp_stored_procedures.
 En MySQL:
 Para ver información sobre un SP se puede ejecutar la
sentencia SHOW CREATE PROCEDURE nombre;
 Para obtener una lista de SP: SHOW PROCEDURE STATUS;

© Maximiliano Odstrcil - 26
Recomendaciones
En SQL Server, se debe tratar de evitar romper la cadena de
permisos, por lo que se recomienda que el usuario dbo sea el
dueño de los procedimientos (similar a las vistas).
 Realizar un procedimiento por cada tarea (cohesión).
 Crear, probar y corregir los procedimientos en el servidor, luego
hacer la prueba en los clientes.
 En SQL Server, evitar el uso del prefijo sp_ para los
procedimientos.
 Usar las mismas configuraciones de conexión para todos los SP.
 Minimizar el uso de procedimientos temporales.

© Maximiliano Odstrcil - 27
Ejecución de SP (1)
 Los
procedimientos almacenados se pueden ejecutar por
sí mismos:
 SQL Server: se emplea la sentencia EXEC[UTE]
seguida del nombre del SP y sus parámetros:
EXEC
EXEC overdue_books
overdue_books
 MySQL: se emplea la sentencia CALL seguida del
nombre del SP y sus parámetros:
CALL
CALL verEmpleados();
verEmpleados();
© Maximiliano Odstrcil - 28
Ejecución de SP (2)
 En
SQL Server los SP también se pueden ejecutar como
parte de una sentencia INSERT, la cual puede poblar una
tabla con el resultado (ResultSet) devuelto por un SP (la
tabla debe existir y los tipos de datos y columnas deben
coincidir):
INSERT
INSERT INTO
INTO customers
customers
EXEC
EXEC employee_customer
employee_customer
© Maximiliano Odstrcil - 29
Modificación y Borrado de SP (1)
 Los
SP son modificados en respuesta a los requisitos del
usuario o a cambios en las tablas subyacentes.
 En
SQL Server, para modificar un SP existente se puede
usar un asistente o bien la sentencia ALTER
PROC[EDURE].
 En
MySQL, la sentencia ALTER PROCEDURE sólo
permite modificar algunas características de un SP, no su
cuerpo ni parámetros.
© Maximiliano Odstrcil - 30
Modificación y Borrado de SP (2)
 Consideraciones
(SQL Server):
 Se deben incluir las opciones que correspondan para la
modificación del procedimiento, como WITH ENCRYPTION,
WITH RECOMPILE, etc.
 La sentencia sólo modifica un SP. Si el SP llama a otros, éstos
no se ven afectados.
 Se necesita tener determinados permisos para modificar el SP.
© Maximiliano Odstrcil - 31
Modificación y Borrado de SP (3)
 El
siguiente ejemplo modifica el SP overdue_books:
ALTER
ALTER PROC
PROC overdue_books
overdue_books
AS
AS
SELECT
SELECT CONVERT(char(8),due_date,1)
CONVERT(char(8),due_date,1) date_due,
date_due, isbn,
isbn, copy_no,
copy_no,
SUBSTRING(title,
SUBSTRING(title, 1,
1, 30)title,
30)title, member_no,
member_no, lastname
lastname
FROM
FROM OverdueView
OverdueView
ORDER
ORDER BY
BY due_date
due_date
GO
GO
© Maximiliano Odstrcil - 32
Modificación y Borrado de SP (4)
 Para
borrar un SP se puede usar un asistente o la
sentencia DROP PROC[EDURE].
 SQL Server:
DROP
DROP PROC
PROC overdue_books
overdue_books
 MySQL:
DROP
DROP PROCEDURE
PROCEDURE verEmpleados;
verEmpleados;
© Maximiliano Odstrcil - 33
Uso de Parámetros




Parámetros de Entrada
Ejecución de SP con Parámetros
Retorno de Valores de Salida
Recompilación Explícita de SP
© Maximiliano Odstrcil - 34
Parámetros de Entrada (1)
Permiten pasar información a los SP.
 Se los debe declarar en la sentencia CREATE PROCEDURE.
 Consideraciones:
 Verificar al principio del SP los valores de entrada para atrapar
posibles valores inválidos.
 Proveer valores por defecto apropiados (depende del SGBDR).
 El máximo número de parámetros depende del SGBDR.
 El máximo número de variables locales de un SP es limitado
solamente por la memoria disponible (depende del SGBDR).
 Los parámetros tienen un ámbito local al SP.

© Maximiliano Odstrcil - 35
Parámetros de Entrada (2)
 Sintaxis
para SQL Server:
 @parámetro tipo_dato [ = valor_defecto]
CREATE
CREATE PROC
PROC dbo.find_isbn
dbo.find_isbn
@title
@title longstring
longstring == null,
null,
@translation
@translation char(8)
char(8) == 'English'
'English'
AS
AS
IF
IF @title
@title is
is null
null
BEGIN
BEGIN
PRINT
PRINT "Please
"Please provide
provide aa title
title
(or
(or partial
partial title)
title) and
and the
the translation"
translation"
PRINT
PRINT "find_isbn
"find_isbn 'Oliver%',
'Oliver%', 'Japanese'"
'Japanese'"
..
..
..
© Maximiliano Odstrcil - 36
Parámetros de Entrada (3)
 Sintaxis
para MySQL:
 parámetro tipo_dato
DELIMITER
DELIMITER //
//
CREATE
CREATE PROCEDURE
PROCEDURE verUnEmpleado(ide
verUnEmpleado(ide INTEGER)
INTEGER)
BEGIN
BEGIN
SELECT
SELECT ** FROM
FROM Employees
Employees
WHERE
WHERE EmployeeID
EmployeeID == ide;
ide;
END
END //
//
DELIMITER
DELIMITER ;;
© Maximiliano Odstrcil - 37
Ejecución de SP con Parámetros (1)
 En
SQL Server se puede llamar un SP con parámetros
de 2 formas:
 Pasando los valores por referencia (forma explícita)
 Pasando los valores por posición
© Maximiliano Odstrcil - 38
Ejecución de SP con Parámetros (2)

Paso de valores por referencia:
 en la sentencia EXEC se especifica el parámetro de la forma
@parámetro = valor.
 El orden de los parámetros puede ser cualquiera.
 Se pueden omitir parámetros, en cuyo caso adoptan el valor
por defecto o el valor nulo.
 Se puede especificar @parámetro = DEFAULT.
EXEC
EXEC addadult
addadult
@firstname
@firstname == 'Linda',
'Linda', @lastname
@lastname ==
@street
@city
@street == 'Dogwood
'Dogwood Drive',
Drive',
@city
@state
@zip
@state == 'CA',
'CA',
@zip == '94203'
'94203'
© Maximiliano Odstrcil - 39
'LaBrie',
'LaBrie',
== 'Sacramento',
'Sacramento',
Ejecución de SP con Parámetros (3)

Paso de valores por posición:
 Se pasan los valores en el mismo orden en que fue definido el
SP.
 Se pueden omitir parámetros en valores por defecto, pero no
se debe interrumpir la secuencia.
 Se usan también valores nulos y DEFAULT como parámetros.
EXEC
EXEC addadult
addadult 'LaBrie',
'LaBrie', 'Linda',
'Linda', null,
null,
'Dogwood
'Dogwood Drive',
Drive', 'Sacramento',
'Sacramento', 'CA',
'CA',
'94203',
'94203', null
null
© Maximiliano Odstrcil - 40
Ejecución de SP con Parámetros (4)

En MySQL sólo se pueden pasar los valores por posición:
 Se pasan los valores en el mismo orden en que fue definido el
SP.
 Se usan también valores nulos como parámetros.
CALL
CALL verUnEmpleado('Sales
verUnEmpleado('Sales Representative',
Representative', 'Ms.');
'Ms.');
© Maximiliano Odstrcil - 41
Retorno de Valores de Salida (1)

Un SP puede devolver información al SP o cliente que lo llama en
forma de parámetros de salida.

En SQL Server, para indicar que un parámetro es de salida se
emplea la cláusula OUTPUT en la definición del SP:
CREATE
CREATE PROCEDURE
PROCEDURE dbo.mathtutor
dbo.mathtutor
@m1
@m1 smallint,
smallint,
@m2
@m2 smallint,
smallint,
@result
@result smallint
smallint OUTPUT
OUTPUT
AS
AS
SET
SET @result
@result == @m1
@m1 ** @m2
@m2
GO
GO
© Maximiliano Odstrcil - 42
Retorno de Valores de Salida (2)
 En
MySQL, los parámetros pueden ser de 3 tipos:
 IN: modo por defecto. Implica que quien llame al SP
debe proporcionar ese parámetro. El SP trabaja con
una copia de este parámetro.
 OUT: el valor de este parámetro se puede cambiar en
el SP. El SP no puede acceder al valor inicial de este
parámetro cuando empieza.
 INOUT: combinación de las 2 opciones anteriores.
© Maximiliano Odstrcil - 43
Retorno de Valores de Salida (3)
 Ejemplo
en MySQL:
DELIMITER
DELIMITER //
//
CREATE
CREATE PROCEDURE
PROCEDURE cantEmpleados(t
cantEmpleados(t VARCHAR(30),
VARCHAR(30),
toc
toc VARCHAR(25),
VARCHAR(25), out
out cant
cant integer)
integer)
BEGIN
BEGIN
SELECT
SELECT count(EmployeeID)
count(EmployeeID)
INTO
INTO cant
cant
FROM
FROM Employees
Employees
WHERE
WHERE (Title
(Title == t)
t) AND
AND (TitleOfCourtesy
(TitleOfCourtesy == toc)
toc) ;;
END
END //
//
DELIMITER
DELIMITER ;;
© Maximiliano Odstrcil - 44
Retorno de Valores de Salida (4)
 En
SQL Server, para llamar a un SP con parámetros de
salida también se emplea la cláusula OUTPUT.
 La sentencia que llama al SP debe tener una variable
para guardar el valor de salida, que puede ser de
cualquier tipo de datos, salvo text o image.
DECLARE
DECLARE @answer
@answer smallint
smallint
EXECUTE
EXECUTE mathtutor
mathtutor 5,
5, 6,
6, @answer
@answer OUTPUT
OUTPUT
SELECT
SELECT 'The
'The result
result is:
is: '' ,, @answer
@answer
© Maximiliano Odstrcil - 45
Retorno de Valores de Salida (5)
 En
MySQL, para llamar a un SP con parámetros de
salida se hace:
CALL
CALL cantEmpleados('Sales
cantEmpleados('Sales Representative',
Representative', 'Ms.',
'Ms.', @cant);
@cant);
SELECT
SELECT @cant;
@cant;
© Maximiliano Odstrcil - 46
Recompilación Explícita de SP (1)
A veces SQL Server necesita reoptimizar los planes de
ejecución de los SP:
 Se agregan nuevos índices
 Se cambian los datos en columnas indexadas
 Esta optimización se produce automáticamente cuando:
 Inicia SQL Server y se ejecuta la consulta por primera
vez.
 Se cambian las tablas subyacentes.
 Si se agrega un índice, por ejemplo, se debe recompilar
manualmente el SP.

© Maximiliano Odstrcil - 47
Recompilación Explícita de SP (2)
 Un
plan de ejecución consiste del plan en sí mismo y el
contexto de ejecución, el cual incluye los valores de los
parámetros.
 Si
estos valores no son los mismos en las sucesivas
llamadas al SP, la performance del SP puede disminuir,
por lo que también resulta ventajoso recompilar un SP.
© Maximiliano Odstrcil - 48
Recompilación Explícita de SP (3)
 Tres
formas de recompilar explícitamente un SP:
 Especificar la opción WITH RECOMPILE en la sentencia
CREATE PROCEDURE, con lo cual el SP no se guarda en el
cache y se recompila cada vez que se ejecuta.
 Especificar la opción WITH RECOMPILE en la sentencia
EXECUTE, con lo cual se crea un nuevo plan durante la
ejecución del SP y se guarda en el cache.
 Usar el SP de sistema sp_recompile, con lo cual el SP se
recompila la próxima vez que se ejecute. Si se lo ejecuta con
una tabla o vista, todos los SP asociados son recompilados.
© Maximiliano Odstrcil - 49
Procedimientos Extendidos
 Los
SP extendidos son funciones dentro de una DLL que
incrementan la funcionalidad de SQL Server.
 Características:
 Se programan usando la API de Open Data Services.
 Pueden incluir características de C y C++ por ejemplo.
 Pueden contener múltiples funciones.
 Se pueden llamar desde un cliente SQL Server.
 Se pueden agregar solamente a la BD master.
EXEC
EXEC master..xp_cmdshell
master..xp_cmdshell 'dir
'dir c:\mssql7'
c:\mssql7'
© Maximiliano Odstrcil - 50
Manejo de Mensajes de Error (1)
 Para
aumentar la efectividad de los SP, se deben incluir
mensajes de error que comuniquen el estado de la
transacción al usuario.
 En
SQL Server, para implementar los mensajes de error se
cuenta con:
 Sentencia RETURN
 Procedimiento sp_addmessage
 Función @@error
 Sentencia RAISERROR
© Maximiliano Odstrcil - 51
Manejo de Mensajes de Error (1)
 Sentencia
RETURN:
 Sale de una consulta o SP incondicionalmente. Puede retornar
un valor de estado entero llamado código de retorno.
 SQL Server devuelve automáticamente 0 de cualquier SP, a
menos que se especifique otro valor.
© Maximiliano Odstrcil - 52
Manejo de Mensajes de Error (2)
 Sentencia
RETURN:
CREATE
CREATE PROC
PROC AddDept
AddDept
@name
@name varchar(50),
varchar(50), @groupname
@groupname varchar(50),
varchar(50),
@deptid
@deptid smallint
smallint OUTPUT
OUTPUT
AS
AS
IF
IF ((@name
((@name == '')
'') OR
OR (@groupname
(@groupname == ''))
''))
RETURN
RETURN -1
-1
INSERT
INSERT INTO
INTO Department
Department (Name,
(Name, GroupName)
GroupName)
VALUES
(@name,
@groupname)
VALUES (@name, @groupname)
SET
SET @deptid
@deptid == SCOPE_IDENTITY()
SCOPE_IDENTITY()
RETURN
0
RETURN 0
GO
GO
© Maximiliano Odstrcil - 53
Manejo de Mensajes de Error (3)
 Sentencia
RETURN:
DECLARE
DECLARE @dept
@dept int,
int, @res
@res int
int
EXEC
EXEC @res
@res == AddDept
AddDept 'Refunds',
'Refunds', '',
'', @dept
@dept OUTPUT
OUTPUT
IF
IF (@res
(@res
SELECT
SELECT
ELSE
ELSE
SELECT
SELECT
© Maximiliano Odstrcil - 54
== 0)
0)
@dept
@dept
'Error'
'Error'
Manejo de Mensajes de Error (4)
 Procedimiento
sp_addmessage:
 Crea mensajes de error definidos por el usuario.
 Todos los mensajes de error (de usuario y sistema) se
almacenan en la tabla sysmessages.
 Se pueden escribir en el Visor de Sucesos de Windows.
EXEC
EXEC sp_addmessage
sp_addmessage
@msgnum
@msgnum == 50011,
50011,
@severity
@severity == 10,
10,
@msgtext
@msgtext == ‘El
‘El registro
registro no
no puede
puede ser
ser borrado’,
borrado’,
@with_log
@with_log == ‘TRUE’
‘TRUE’
© Maximiliano Odstrcil - 55
Manejo de Mensajes de Error (5)
 Función
del sistema @@error:
 Contiene el número de error de la sentencia Transact-SQL más
reciente.
 Se borra y resetea con cada sentencia que se ejecuta.
 Retorna un 0 cuando la sentencia se ejecuta satisfactoriamente.
 Se usa @@error para detectar un número de error específico o
para abandonar un SP condicionalmente.
..
..
IF
IF @error
@error <>
<> 00
BEGIN
BEGIN
ROLLBACK
ROLLBACK TRAN
TRAN
RETURN
RETURN
END
END
COMMIT
COMMIT TRAN
TRAN
© Maximiliano Odstrcil - 56
Manejo de Mensajes de Error (6)
 Sentencia
RAISERROR:
 Genera un mensaje de error definido por el usuario y avisa al
sistema que ha ocurrido un error.
 Permite a la aplicación poner un mensaje de error definido por
el usuario o bien una cadena construida dinámicamente.
 Puede escribir mensajes de error en el registro de errores de
SQL o en el Visor de Sucesos de Windows.
RAISERROR
RAISERROR (50011,10,1)
(50011,10,1) WITH
WITH LOG
LOG
© Maximiliano Odstrcil - 57
Manejo de Mensajes de Error (7)
/*
/* Mensajes
Mensajes de
de error
error definidos
definidos por
por el
el
EXEC
EXEC sp_addmessage
sp_addmessage 50010,
50010, 10,
10, 'Member
'Member
EXEC
EXEC sp_addmessage
sp_addmessage 50011,
50011, 10,
10, 'Member
'Member
EXEC
EXEC sp_addmessage
sp_addmessage 50012,
50012, 10,
10, 'Member
'Member
Juvenile
Juvenile member.'
member.'
EXEC
EXEC sp_addmessage
sp_addmessage 50013,
50013, 10,
10, 'Member
'Member
loan.‘
loan.‘
usuario.
usuario. */
*/
number
number not
not found.'
found.'
cannot
cannot be
be deleted.'
deleted.'
is
is responsible
responsible for
for
currently
currently has
has books
books on
on
CREATE
CREATE PROCEDURE
PROCEDURE dbo.removemember
dbo.removemember
@member_no
@member_no member_no
member_no == NULL
NULL
AS
AS
IF
IF @member_no
@member_no is
is null
null
BEGIN
BEGIN
PRINT
PRINT 'You
'You must
must supply
supply aa member
member number'
number'
RETURN
RETURN
END
END
/*Sigue*/
/*Sigue*/
© Maximiliano Odstrcil - 58
Manejo de Mensajes de Error (8)
IF
IF NOT
NOT EXISTS
EXISTS (SELECT
(SELECT ** FROM
FROM member
member
WHERE
WHERE member_no
member_no == @member_no)
@member_no)
BEGIN
BEGIN
RAISERROR
RAISERROR (50010,
(50010, 10,
10, 1)
1) --Member
--Member not
not found.
found.
RETURN
RETURN
END
END
IF
IF EXISTS
EXISTS (SELECT
(SELECT member_no
member_no FROM
FROM juvenile
juvenile
WHERE
WHERE @member_no
@member_no == adult_member_no)
adult_member_no)
BEGIN
BEGIN
RAISERROR
RAISERROR (50011,
(50011, 10,
10, 1)
1)
RAISERROR
RAISERROR (50012,
(50012, 10,
10, 1)
1)
PRINT
PRINT 'Remove
'Remove the
the juvenile
juvenile member(s)
member(s) first'
first'
RETURN
RETURN
END
END
/*Sigue*/
/*Sigue*/
© Maximiliano Odstrcil - 59
Manejo de Mensajes de Error (9)
IF
IF EXISTS
EXISTS (SELECT
(SELECT member_no
member_no FROM
FROM loan
loan
WHERE
WHERE member_no
member_no == @member_no)
@member_no)
BEGIN
BEGIN
RAISERROR
RAISERROR (50013,
(50013, 10,
10, 1)
1) --Books
--Books on
on loan.
loan.
RAISERROR
RAISERROR (50011,
(50011, 10,
10, 1)
1) --Member
--Member cannot
cannot be
be deleted.
deleted.
RETURN
RETURN
END
END
/*Sigue*/
/*Sigue*/
© Maximiliano Odstrcil - 60
Manejo de Mensajes de Error (10)
BEGIN
BEGIN TRANSACTION
TRANSACTION
IF
IF EXISTS
EXISTS (SELECT
(SELECT member_no
member_no FROM
FROM reservation
reservation
WHERE
WHERE member_no
member_no == @member_no)
@member_no)
BEGIN
BEGIN
PRINT
PRINT 'Deleting
'Deleting Loan
Loan Reservation
Reservation information'
information'
DELETE
reservation
DELETE reservation
WHERE
WHERE member_no
member_no == @member_no
@member_no
END
END
IF
IF EXISTS
EXISTS (SELECT
(SELECT member_no
member_no FROM
FROM juvenile
juvenile
WHERE
member_no
WHERE member_no == @member_no)
@member_no)
BEGIN
BEGIN
DELETE
DELETE juvenile
juvenile
WHERE
WHERE member_no
member_no == @member_no
@member_no
END
END
ELSE
ELSE IF
IF EXISTS
EXISTS (SELECT
(SELECT member_no
member_no FROM
FROM adult
adult WHERE
WHERE
member_no
=
@member_no)
member_no = @member_no)
BEGIN
BEGIN
DELETE
DELETE adult
adult
WHERE
member_no
WHERE member_no == @member_no
@member_no
END
END
/*Sigue*/
/*Sigue*/
© Maximiliano Odstrcil - 61
Manejo de Mensajes de Error (11)
GO
GO
DELETE
DELETE member
member
WHERE
WHERE member_no
member_no == @member_no
@member_no
SELECT
SELECT 'Member
'Member '' ++ CONVERT(char(6),
CONVERT(char(6), @member_no)
@member_no) ++
'has
'has been
been removed
removed from
from the
the library
library database.'
database.'
COMMIT
COMMIT TRANSACTION
TRANSACTION
© Maximiliano Odstrcil - 62
Manejo de Mensajes de Error (12)
 En
MySQL, para implementar los mensajes de error se
cuenta con la sentencia LEAVE:
DELIMITER
DELIMITER //
//
CREATE
CREATE PROCEDURE
PROCEDURE borrarOrden(ido
borrarOrden(ido integer)
integer)
SALIR:BEGIN
SALIR:BEGIN
DECLARE
DECLARE mensaje
mensaje VARCHAR(100);
VARCHAR(100);
IF
IF (ido
(ido is
is null)
null) THEN
THEN
SET
SET mensaje
mensaje == 'Debe
'Debe especificar
especificar una
una orden';
orden';
SELECT
SELECT mensaje;
mensaje;
LEAVE
LEAVE SALIR;
SALIR;
END
END IF;
IF;
...
...
END
END //
//
DELIMITER
DELIMITER ;;
© Maximiliano Odstrcil - 63
Implementación de Desencadenadores




Introducción a Triggers
Definición de Triggers
Ejemplos de Triggers
Consideraciones de Perfomance
© Maximiliano Odstrcil - 64
Introducción a Triggers



Qué es un Trigger
Usos de Triggers
Consideraciones de Uso
© Maximiliano Odstrcil - 65
Qué es un Trigger (1)
 Un
desencadenador (trigger) es un tipo especial de SP
que se ejecuta automáticamente cuando se modifican los
datos de la tabla sobre la que se lo creó (trigger DML):
 Cuando ocurre una inserción, modificación o borrado
de datos en una tabla, y se definió un trigger para la
misma
y
para
esa
acción,
se
ejecuta
automáticamente, no pudiéndose detener.
 Hay otros triggers que se ejecutan cuando se emplean
sentencias del tipo CREATE, ALTER y DROP (trigger
DDL).
© Maximiliano Odstrcil - 66
Qué es un Trigger (2)
 Un
trigger, junto con la sentencia que lo dispara, son
tratados como una sola transacción (no hace falta iniciar
explícitamente la transacción). Esta transacción se
puede volver atrás dentro del cuerpo del trigger.
 Cualquier
sentencia SQL que sea admitida por un SP se
puede incluir dentro de un trigger.
© Maximiliano Odstrcil - 67
Usos de Triggers (1)
 Características:
 Se usan para mantener la integridad de datos.
 No devuelven valores de consulta.
 Su principal ventaja es que manejan complejidad de
procesamiento.
© Maximiliano Odstrcil - 68
Usos de Triggers (2)
 Usos:
 Cambios en cascada de tablas relacionadas: un trigger para
borrado en la tabla “Compras” puede borrar todas las filas de la
tabla “LineasCompras” (estos cambios se pueden ejecutar más
eficientemente empleando integridad referencial en cascada).
 Forzar integridad de datos de manera compleja: los triggers
pueden proteger contra inserciones, borrados y modificaciones
incorrectas, e imponer restricciones más complejas que las
definidas mediante constraints de tipo CHECK (pueden
referenciar columnas en otras tablas).
© Maximiliano Odstrcil - 69
Usos de Triggers (3)
 Usos:
 Definición de mensajes de error de usuario: los triggers
pueden mostrar mensajes de error definidos por el usuario.
 Comparar los estados antes y después: los triggers pueden
comparar el estado de una modificación de datos antes y
después de dicha modificación y luego ejecutar determinadas
acciones según esta diferencia.
© Maximiliano Odstrcil - 70
Consideraciones de Uso (1)
 Los
triggers son reactivos mientras que las constraints son
proactivas.
 Los triggers se disparan después de la ejecución de
sentencias INSERT, UPDATE o DELETE.
 Las constraints se verifican primero:
 Se verifican antes de la sentencia y del trigger.
 Si se violan las constraints, el trigger nunca se ejecuta.
 Las tablas pueden tener múltiples triggers del mismo tipo
(INSERT, UPDATE o DELETE) sin orden de ejecución.
© Maximiliano Odstrcil - 71
Consideraciones de Uso (2)
 Los
dueños de tablas deben tener permisos para crear,
modificar y borrar triggers.
 No
se puede crear triggers en vistas ni en tablas
temporales, pero sí las pueden referenciar.
 No
deben retornar ResultSets:
 Aunque pueden hacerlo, no tiene sentido devolver filas ya
que las aplicaciones no lo esperan.
© Maximiliano Odstrcil - 72
Consideraciones de Uso (3)
 No
admiten parámetros.
 En
SQL Server se puede usar la función del sistema
@@ROWCOUNT para determinar el número de filas
afectadas en una transacción.
© Maximiliano Odstrcil - 73
Definición de Triggers





Creación de Triggers
Triggers INSERT
Triggers DELETE
Triggers UPDATE
Modificación y Borrado de Triggers
© Maximiliano Odstrcil - 74
Creación de Triggers (1)
 Desde
la versión 2005 de SQL Server hay 2 tipos de
trigger DML:
 Triggers AFTER: se ejecutan después que se
produce la acción que dispara el trigger. Estos
triggers sólo se pueden definir en tablas, y se pueden
definir varios en una misma tabla.
 Triggers INSTEAD OF: se ejecutan en lugar de la
acción usual del trigger. Se pueden definir también en
vistas (por tabla o vista sólo se puede definir uno.
© Maximiliano Odstrcil - 75
Creación de Triggers (2)
 Teniendo
en cuenta los 2 tipos de triggers DML en SQL
Server, si una tabla tiene definidas constraints, triggers
del tipo AFTER y del tipo INSTEAD OF:
 Las constraints se verifican después de la ejecución
de los triggers INSTEAD OF, pero antes de la de los
triggers AFTER.
 Si se violan las constraints, las acciones de los
triggers INSTEAD OF se vuelven atrás y los triggers
AFTER nunca se disparan.
© Maximiliano Odstrcil - 76
Creación de Triggers (3)
 En
MySQL, al momento de definir un trigger DML se
puede especificar el momento en el que se disparará:
 AFTER: el trigger se dispara después que se
modifique cada fila.
 BEFORE: el trigger se dispara antes que se
modifique cada fila.
© Maximiliano Odstrcil - 77
Creación de Triggers (4)
 Los
triggers se pueden crear con un asistente o bien con
la sentencia CREATE TRIGGER.
 En
la sentencia se especifica:
 El nombre del trigger
 La tabla a la cual pertenece el trigger
 El o los eventos por los que se dispara el trigger
 El cuerpo del procedimiento
© Maximiliano Odstrcil - 78
Creación de Triggers (5)
 En
SQL Server se puede emplear la cláusula IF UPDATE
(columna) para focalizar la acción a una determinada
columna, y no a todas las columnas de una tabla.
 Para
crear un trigger se requiere determinados permisos.
En SQL Server:
 Se debe ser miembro de los roles db_owner o sysadmin.
 Es recomendable que el dbo sea dueño de los objetos
subyacentes para no romper la cadena de permisos.
© Maximiliano Odstrcil - 79
Creación de Triggers (6)
 Los
triggers pueden aceptar cualquier sentencia SQL,
salvo:
 Todas las sentencias CREATE
 Todas las sentencias DROP
 ALTER (TABLE, DATABASE, PROCEDURE,
TRIGGER)
 GRANT, REVOKE y DENY
 UPDATE STATISTICS
 RECONFIGURE
 LOAD DATABASE y RESTORE DATABASE
© Maximiliano Odstrcil - 80
VIEW
y
Triggers INSERT (1)
 Un
trigger INSERT se ejecuta cuando se agregan filas a
una tabla sobre la que está creado.
 Funcionamiento
(SQL Server):
 Cuando se dispara un trigger INSERT (porque se agregaron
filas a la tabla sobre la que está creado) también se agregan
filas a una tabla llamada inserted.
 La tabla inserted es una tabla lógica que mantiene una copia
de las filas que se agregaron (contiene el log de la actividad de
inserción).
 La tabla inserted permite referenciar sus filas desde el trigger.
© Maximiliano Odstrcil - 81
Triggers INSERT (2)
 Funcionamiento
(SQL Server):
 Las filas de la tabla inserted siempre son un duplicado de una
o más filas en la tabla del trigger.
 El trigger puede examinar la tabla inserted para determinar
las acciones a ejecutar.
 La actividad (INSERT, UPDATE, DELETE) se registra pero
queda ilegible y puede leerse solamente a través de la tabla
inserted.
© Maximiliano Odstrcil - 82
Triggers INSERT (3)
Se realiza una inserción a una tabla con un trigger INSERT
INSERT
INSERT loan
loan VALUES
VALUES
(603,
4,
11,
(603, 4, 11, 123,
123, GETDATE(),
GETDATE(), (GETDATE()
(GETDATE() ++ 30))
30))
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no outdate
outdate
11
44
33
© Maximiliano Odstrcil - 83
11
22
11
duedate
duedate
1001
1001 1001
1001 02/13/91
02/13/91 02/27/91
02/27/91
1004
1004 1002
1002 02/14/91
02/14/91 02/28/91
02/28/91
1002
1002 1003
1003 02/14/91
02/14/91 02/28/91
02/28/91
Triggers INSERT (4)
Se realiza una inserción a una tabla con un trigger INSERT
INSERT
INSERT loan
loan VALUES
VALUES
(603,
(603, 4,
4, 11,
11, 123,
123, GETDATE(),
GETDATE(), (GETDATE()
(GETDATE() ++ 30))
30))
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no outdate
outdate
11
44
603
4343
33
© Maximiliano Odstrcil - 84
11
41212
2121
11
1001
1001
1004
11
1004
1002
1004
1002
1004
1002
1002
1001
1001
1002
1001
123
1001
1002
1003
1002
1003
1002
1003
1003
02/13/91
02/13/91
02/14/91
02/13/91
02/15/91
02/13/91
02/14/91
02/14/91
02/14/91
02/14/91
02/14/91
duedate
duedate
02/27/91
02/27/91
02/28/91
02/27/91
03/17/91
02/27/91
02/28/91
02/28/91
02/28/91
02/28/91
02/28/91
Triggers INSERT (5)
Se realiza una inserción a una tabla con un trigger INSERT
INSERT
INSERT loan
loan VALUES
VALUES
(603,
(603, 4,
4, 11,
11, 123,
123, GETDATE(),
GETDATE(), (GETDATE()
(GETDATE() ++ 30))
30))
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no outdate
outdate
11
44
603
4343
33
11
41212
2121
11
1001
1001
1004
11
1004
1002
1004
1002
1004
1002
1002
1001
1001
1002
1001
123
1002
1001
1003
1002
1003
1002
1003
1003
02/13/91
02/13/91
02/14/91
02/13/91
02/15/91
02/14/91
02/13/91
02/14/91
02/14/91
02/14/91
02/14/91
duedate
duedate
02/27/91
02/27/91
02/28/91
02/27/91
03/17/91
02/28/91
02/27/91
02/28/91
02/28/91
02/28/91
02/28/91
Se registra la inserción en la tabla inserted
inserted
inserted
603
44
603
© Maximiliano Odstrcil - 85
11
11
123
123
02/15/91
02/15/91 03/17/91
03/17/91
Triggers INSERT (6)
Se ejecutan las acciones del trigger
USE
USE library
library
CREATE
CREATE TRIGGER
TRIGGER loan_insert
loan_insert
ON
ON loan
loan
FOR
FOR INSERT
INSERT
AS
AS
UPDATE
UPDATE cc
SET
SET on_loan
on_loan == 'Y'
'Y'
FROM
copy
c
INNER
FROM copy c INNER JOIN
JOIN inserted
inserted II
ON
ON c.isbn
c.isbn == i.sbn
i.sbn AND
AND c.copy_no
c.copy_no == i.copy_no
i.copy_no
GO
GO
copy
copy
isbn
isbn copy_no
copy_no title_no
title_no on_loan
on_loan
11
44
603
44
33
© Maximiliano Odstrcil - 86
11
411
22
11
1001
1001
1004
11
1004
1004
1004
1002
1002
YY
YY
NN
NN
Triggers INSERT (7)
 En
el caso de MySQL, para crear un trigger INSERT:
DELIMITER
DELIMITER //
//
CREATE
TRIGGER
CREATE TRIGGER Telefonos_Insert
Telefonos_Insert AFTER
AFTER INSERT
INSERT ON
ON Telefonos
Telefonos
FOR
FOR EACH
EACH ROW
ROW
BEGIN
BEGIN
--- cuerpo
cuerpo del
del trigger
trigger
END//
END//
DELIMITER
DELIMITER ;;

Si se especifica la opción FOR EACH ROW, el trigger se ejecuta
una vez para cada fila, y si no se especifica esta opción, el trigger
se ejecuta una única vez sin importar el número de filas.
© Maximiliano Odstrcil - 87
Triggers INSERT (8)
 Así
como en el caso de SQL Server se tenía acceso
dentro del cuerpo de trigger a una tabla llamada
inserted, en MySQL se tiene acceso a una tabla llamada
NEW:
 En una inserción, la tabla NEW tiene las filas a
insertar.
 En una modificación, la tabla NEW tiene las filas con
los valores modificados.
 La tabla NEW es insensible a mayúsculas.
© Maximiliano Odstrcil - 88
Triggers DELETE (1)
 Un
trigger DELETE se ejecuta cuando se borran filas de
una tabla sobre la que está creado.
 Funcionamiento:
 Cuando se dispara un trigger DELETE, porque se borraron
filas de la tabla sobre la que está creado, estas filas agregan a
una tabla llamada deleted.
 La tabla deleted es una tabla lógica que guarda una copia de
las filas que han sido borradas (las filas en la tabla original no
existen).
© Maximiliano Odstrcil - 89
Triggers DELETE (2)
 Funcionamiento:




La tabla borrada y la tabla deleted no tienen filas en común.
La tabla deleted está siempre en el cache (memoria).
La tabla deleted permite referenciar sus filas desde el trigger.
Este trigger no se ejecuta con TRUNCATE TABLE.
© Maximiliano Odstrcil - 90
Triggers DELETE (3)
Se borran filas de una tabla con un trigger DELETE
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no outdate
outdate
DELETE
DELETE loan
loan
WHERE
isbn
WHERE isbn == 44 AND
AND copy_no
copy_no == 11
11
44
44
33
11
11
22
11
1001
1001
1004
1004
1004
1004
1002
1002
1001
1001
1001
1001
1002
1002
1003
1003
02/13/91
02/13/91
02/13/91
02/13/91
02/14/91
02/14/91
02/14/91
02/14/91
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no outdate
outdate
11
44
44
33
© Maximiliano Odstrcil - 91
11
11
22
11
1001
1001
1004
1004
1004
1004
1002
1002
1001
1001
1001
1001
1002
1002
1003
1003
02/13/91
02/13/91
02/13/91
02/13/91
02/14/91
02/14/91
02/14/91
02/14/91
duedate
duedate
02/27/91
02/27/91
02/27/91
02/27/91
02/28/91
02/28/91
02/28/91
02/28/91
duedate
duedate
02/27/91
02/27/91
02/27/91
02/27/91
02/28/91
02/28/91
02/28/91
02/28/91
Triggers DELETE (4)
Se registra el borrado en la tabla deleted
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no outdate
outdate
11
44
33
deleted
deleted
44
11
© Maximiliano Odstrcil - 92
11
22
11
duedate
duedate
1001
1001 1001
1001 02/13/91
02/13/91 02/27/91
02/27/91
1004
1004 1002
1002 02/14/91
02/14/91 02/28/91
02/28/91
1002
1002 1003
1003 02/14/91
02/14/91 02/28/91
02/28/91
1004
1004
1001
1001
02/13/91
02/13/91 02/27/91
02/27/91
Triggers DELETE (5)
Se ejecutan las acciones del trigger
USE
USE library
library
CREATE
CREATE TRIGGER
TRIGGER loan_delete
loan_delete
ON
ON loan
loan
FOR
FOR DELETE
DELETE
AS
AS
UPDATE
UPDATE cc
SET
SET on_loan
on_loan == 'N'
'N'
FROM
FROM copy
copy cc INNER
INNER JOIN
JOIN deleted
deleted dd
ON
ON c.isbn
c.isbn == d.isbn
d.isbn AND
AND c.copy_no
c.copy_no == d.copy.no
d.copy.no
GO
GO
copy
copy
isbn
isbn copy_no
copy_no title_no
title_no on_loan
on_loan
11
44
44
33
© Maximiliano Odstrcil - 93
11
11
22
11
1001
1001
1004
1004
1004
1004
1002
1002
YY
YY
N
NN
NN
Triggers DELETE (6)
 En
MySQL, dentro de un trigger DELETE se tiene acceso
a una tabla llamada OLD:
 Tiene las filas antes que sean modificadas o
borradas.
 La tabla OLD es insensible a mayúsculas.
© Maximiliano Odstrcil - 94
Triggers UPDATE (1)
 Un
trigger UPDATE se ejecuta cuando se modifican filas
de una tabla sobre la que está creado.
 Funcionamiento:
 Cuando se dispara un trigger UPDATE, porque se modificaron
filas de la tabla sobre la que está creado, las filas originales se
mueven a la tabla deleted, y las filas modificadas se insertan a
la tabla inserted.
 El trigger puede estas 2 tablas, además de la tabla donde se
están haciendo las modificaciones.
© Maximiliano Odstrcil - 95
Triggers UPDATE (2)
 Funcionamiento:
 En SQL Server se puede definir el trigger para que monitorice
los cambios de una columna específica solamente, para esto
se usa la sintaxis: IF UPDATE columna.
© Maximiliano Odstrcil - 96
Triggers UPDATE (3)
Se modifican filas de una tabla con un trigger UPDATE
UPDATE
UPDATE member
member
SET
SET member_no
member_no == 10021
10021
WHERE
WHERE member_no
member_no == 1234
1234
© Maximiliano Odstrcil - 97
member
member
member_no
member_no lastname
lastname firstname
firstname middleinitial
middleinitial photograph
photograph
10020
10020
10021
1234
10021
10022
10022
10023
10023
Anderson
Anderson
Barr
Barr
Barr
Barr
Anderson
Anderson
Andrew
Andrew AA
Andrew
Andrew RR
Bill
NULL
Bill
NULL
Bill
BB
Bill
~~~
~~~
~~~
~~~
~~~
~~~
Triggers UPDATE (4)
Se modifican filas de una tabla con un trigger UPDATE
UPDATE
UPDATE member
member
SET
SET member_no
member_no == 10021
10021
WHERE
WHERE member_no
member_no == 1234
1234
member
member
member_no
member_no lastname
lastname firstname
firstname middleinitial
middleinitial photograph
photograph
10020
10020
10021
1234
10021
10022
10022
10023
10023
Anderson
Anderson
Barr
Barr
Barr
Barr
Anderson
Anderson
Andrew
Andrew AA
Andrew
Andrew RR
Bill
NULL
Bill
NULL
Bill
BB
Bill
Se registra la modificación como 2 pasos
© Maximiliano Odstrcil - 98
deleted
deleted
1234
Barr
1234
Barr
Andrew
Andrew
RR
~~~
~~~
inserted
inserted
10021
10021 Barr
Barr
Andrew
Andrew
RR
~~~
~~~
~~~
~~~
~~~
~~~
~~~
~~~
Triggers UPDATE (5)
Se ejecutan las acciones del trigger
USE
USE library
library
GO
GO
CREATE
CREATE TRIGGER
TRIGGER member_update
member_update ON
ON member
member FOR
FOR UPDATE
UPDATE
AS
AS
IF
IF UPDATE
UPDATE (member_no)
(member_no)
BEGIN
BEGIN
RAISERROR
RAISERROR ('Transaction
('Transaction cannot
cannot be
be processed.\
processed.\
*****
Member
number
cannot
be
modified.',
***** Member number cannot be modified.', 10,
10, 1)
1)
ROLLBACK
TRANSACTION
ROLLBACK TRANSACTION
...
...
member
member
member_no
member_no lastname
lastname firstname
firstname middleinitial
middleinitial photograph
photograph
10020
10020
10021
1234
10021
10022
10022
10023
10023
© Maximiliano Odstrcil - 99
Anderson
Anderson
Barr
Barr
Barr
Barr
Anderson
Anderson
Andrew
Andrew AA
Andrew
Andrew RR
Bill
NULL
Bill
NULL
Bill
BB
Bill
~~~
~~~
~~~
~~~
~~~
~~~
Triggers UPDATE (6)
 En
MySQL, dentro de un trigger UPDATE se tiene
acceso a las tablas OLD y NEW.
© Maximiliano Odstrcil - 100
Modificación y Borrado de Triggers (1)
 Al
igual que un SP, se puede modificar o borrar un
trigger.
 Modificación (SQL Server):
 Se puede usar un asistente o la sentencia ALTER TRIGGER.
 Al igual que en un SP, se tiene la resolución de nombres
posterior, que permite referenciar objetos todavía inexistentes.
ALTER
ALTER TRIGGER
TRIGGER loan_insert
loan_insert ON
ON loan
loan FOR
FOR INSERT
INSERT
AS
AS
UPDATE
UPDATE copy
copy
SET
SET on_loan
on_loan == 'y'
'y'
FROM
FROM copy
copy INNER
INNER JOIN
JOIN inserted
inserted
ON
ON copy.isbn
copy.isbn == inserted.isbn
inserted.isbn
AND
copy.copy_no
AND copy.copy_no == inserted.copy_no
inserted.copy_no
© Maximiliano Odstrcil - 101
Modificación y Borrado de Triggers (2)
 Borrado
de triggers:
 Los triggers se pueden borrar empleando un asistente o bien la
sentencia DROP TRIGGER nombre_trigger.
 Cuando la tabla se borra, todos sus triggers asociados se
borran también.
 El permiso para borrar un trigger lo tiene el dueño de la tabla
únicamente.
DROP
DROP TRIGGER
TRIGGER loan_insert
loan_insert
© Maximiliano Odstrcil - 102
Otros

Triggers Anidados

Triggers Recursivos

Consideraciones de Performance
© Maximiliano Odstrcil - 103
Triggers anidados (1)
 Un
trigger puede contener sentencias INSERT, UPDATE
y DELETE que afecten a otras tablas y, por ende,
disparen otros triggers.
 En
el caso de SQL Server, se puede habilitar o
deshabilitar la opción de anidamiento en los triggers
mediante el SP sp_configure (por defecto está
habilitada esta opción).
© Maximiliano Odstrcil - 104
Triggers anidados (2)
 Consideraciones:
 Si la opción de anidamiento está habilitada, estos disparos
sucesivos pueden ocurrir hasta en 32 niveles (cuando un
trigger en una cadena de anidamiento supera este nivel, el
mismo termina y deshace la transacción).
 Un trigger no se dispara 2 veces en un llamado (autollamada).
 Como el trigger es una sola transacción, cualquier error en
cualquier nivel causa el retroceso de toda la transacción.
 Se puede ver el nivel de anidamiento con la función
@@NESTLEVEL.
© Maximiliano Odstrcil - 105
Triggers anidados (3)
 El
anidamiento es una herramienta muy poderosa para
mantener la integridad de datos, pero hay ocasiones en
que es necesario desactivarlo:
 Porque requieren un diseño planeado y una complejidad
apreciable.
 Porque las modificaciones de las tablas no siguen el orden
esperado.
 Para
desactivar el anidamiento:
 sp_configure ‘nested triggers’, 0
© Maximiliano Odstrcil - 106
Triggers anidados (4)
loan_update
loan_update
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no outdate
outdate
11
44
44
33
copy_update
copy_update
11
11
22
11
1001
1001
1004
1004
1004
1004
1002
1002
1001
1001
1001
1001
1002
1002
1003
1003
02/13/91
02/13/91
02/13/91
02/13/91
02/14/91
02/14/91
02/14/91
02/14/91
duedate
duedate
02/27/91
02/27/91
02/27/91
02/27/91
02/28/91
02/28/91
02/28/91
02/28/91
El trigger de loan_update
se ejecuta
copy
copy
isbn
isbn copy_no
copy_no title_no
title_no on_loan
on_loan Ejecuta sentencia UPDATE
11
44
44
33
11
11
22
11
1001
1001
1004
1004
1004
1004
1002
1002
YY
YY
NN
NN
en la tabla copy
El trigger copy_update
se ejecuta
Item will be due on 5/22/98
© Maximiliano Odstrcil - 107
Se envía el menaje
Triggers Recursivos (1)
 Un
trigger puede tener sentencias UPDATE, INSERT o
DELETE que afecten a la misma tabla o a otra tabla.
 La
activación de recursividad hace que el trigger pueda
activarse él mismo hasta una condición de parada.
 Hay
2 tipos de recursividad:
 Directa
 Indirecta
© Maximiliano Odstrcil - 108
Triggers Recursivos (2)

Recursividad directa: Cuando una modificación de la tabla T1
causa que el trigger Trig1 se dispare, modifique la tabla T1 y se
dispare nuevamente.

Recursividad indirecta: Cuando una modificación de la tabla T1
cause que se dispare el trigger Trig1, que modifica la tabla T2, se
dispara el trigger Trig2 de T2 que modifica la tabla T1, disparando
Trig1 nuevamente.

Los triggers recursivos son muy complejos y difíciles de
implementar, pero brindan soluciones excelentes para relaciones
autoreferenciadas.
© Maximiliano Odstrcil - 109
Triggers Recursivos (3)

Consideraciones:
 La recursividad admite 32 niveles de llamadas, y por defecto
está deshabilitada.
 Las tablas inserted y deleted contienen las filas que
corresponden sólo a las sentencias del último trigger.
 Si la opción de anidamiento está deshabilitada, también lo está
la opción de recursividad.
 Si la opción de anidamiento está habilitada, la recursividad
indirecta se puede producir, aunque la opción de recursividad
esté deshabilitada.
© Maximiliano Odstrcil - 110
Triggers Recursivos (4)

Para activar la recursividad:
 sp_dboption nombre_db, ‘recursive triggers’, TRUE

También se puede hacer:
 ALTER DATABASE DB SET RECURSIVE_TRIGGERS ON
© Maximiliano Odstrcil - 111
Información sobre Triggers


En SQL Server:
 Para ver los triggers de una tabla:
nombre_tabla.
 Para
ver
la
definición
del
trigger:
nombre_trigger.
sp_depends
sp_helptext
En MySQL:
 Para ver información sobre los triggers: SELECT * FROM
information_schema.triggers
© Maximiliano Odstrcil - 112
Consideraciones de Perfomance
 Los
triggers se ejecutan rápidamente porque las
tablas inserted y deleted están en el cache.
 El
tiempo de ejecución está determinado por:
 Número de tablas referenciadas.
 Número de filas afectadas.
 Las
acciones contenidas en un trigger son parte
implícita de la misma transacción.
© Maximiliano Odstrcil - 113
Ejemplos de Triggers

Forzado de la Integridad de Datos

Forzado de las Reglas de Negocio

Auditoría
© Maximiliano Odstrcil - 114
Forzado de la Integridad de Datos
CREATE
CREATE TRIGGER
TRIGGER reservation_delete
reservation_delete
ON
loan
FOR
ON loan FOR INSERT
INSERT
AS
AS
IF
IF (SELECT
(SELECT r.member_no
r.member_no FROM
FROM reservation
reservation rr JOIN
JOIN
inserted
i
ON
r.member_no
=
i.member_no
inserted i ON r.member_no = i.member_no
AND
AND r.isbn
r.isbn == i.isbn
i.isbn )) >> 00
BEGIN
BEGIN
DELETE
DELETE rr FROM
FROM reservation
reservation rr INNER
INNER JOIN
JOIN inserted
inserted ii
ON
ON r.member_no
r.member_no == i.member_no
i.member_no AND
AND r.isbn
r.isbn == i.isbn
i.isbn
END
END
loan
loan
reservation
reservation
isbn
isbn copy_no
copy_no mem_no
mem_no
11
44
44
33
11
11
11
22
11
22
© Maximiliano Odstrcil - 115
11
77
44
33
11
isbn
isbn mem_no
mem_no log_date
log_date remarks
remarks
Insertado
Insertado
Trigger
Triggerborra
borrafila
fila
11
11
22
33
44
11
22
11
11
77
07/14/98
07/14/98
07/12/98
07/12/98
06/07/98
06/07/98
07/14/98
07/14/98
07/14/98
07/14/98
~~~
~~~
~~~
~~~
~~~
~~~
~~~
~~~
~~~
~~~
Forzado de las Reglas de Negocio (1)
Los miembros con préstamos adeudados no pueden ser borrados
Sentencia DELETE ejecutada en la tabla member
member
member
member_no
member_no lastname
lastname firstname
firstname
11
2323
3434
44
© Maximiliano Odstrcil - 116
Anderson
Anderson
Barr
Barr
Anderson
Barr
Anderson
Barr
Anderson
Anderson
Andrew
Andrew
Bill
Andrew
Bill
Andrew
Bill
Bill
Bill
Bill
Forzado de las Reglas de Negocio (2)
Los miembros con préstamos adeudados no pueden ser borrados
Sentencia DELETE ejecutada en la tabla member
member
member
member_no
member_no lastname
lastname firstname
firstname
11
2323
3434
44
© Maximiliano Odstrcil - 117
Anderson
Anderson
Barr
Barr
Anderson
Barr
Anderson
Barr
Anderson
Anderson
Andrew
Andrew
Bill
Andrew
Bill
Andrew
Bill
Bill
Bill
Bill
Forzado de las Reglas de Negocio (3)
Los miembros con préstamos adeudados no pueden ser borrados
Sentencia DELETE ejecutada en la tabla member
member
member
member_no
member_no lastname
lastname firstname
firstname
11
33
44
© Maximiliano Odstrcil - 118
Anderson
Anderson
Barr
Barr
Anderson
Anderson
Andrew
Andrew
Bill
Bill
Bill
Bill
Forzado de las Reglas de Negocio (4)
Los miembros con préstamos adeudados no pueden ser borrados
IF
IF (Select
(Select Count
Count (*)
(*) FROM
FROM loan
loan INNER
INNER JOIN
JOIN deleted
deleted
ON
ON loan.member_no
loan.member_no == deleted.member_no)
deleted.member_no) >> 00
ROLLBACK
ROLLBACK TRANSACTION
TRANSACTION
El código del trigger controla la tabla loan
member
member
member_no
member_no lastname
lastname firstname
firstname
11
33
44
© Maximiliano Odstrcil - 119
Anderson
Anderson
Barr
Barr
Anderson
Anderson
Andrew
Andrew
Bill
Bill
Bill
Bill
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no
11
44
44
33
11
11
22
11
11
44
44
33
11
11
22
33
Forzado de las Reglas de Negocio (5)
Los miembros con préstamos adeudados no pueden ser borrados
IF
IF (Select
(Select Count
Count (*)
(*) FROM
FROM loan
loan INNER
INNER JOIN
JOIN deleted
deleted
ON
ON loan.member_no
loan.member_no == deleted.member_no)
deleted.member_no) >> 00
ROLLBACK
ROLLBACK TRANSACTION
TRANSACTION
El código del trigger controla la tabla loan
member
member
member_no
member_no lastname
lastname firstname
firstname
11
33
44
Anderson
Anderson
Barr
Barr
Anderson
Anderson
Andrew
Andrew
Bill
Bill
Bill
Bill
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no
11
44
44
33
11
11
22
11
11
44
44
33
11
11
22
33
'No
'No se
se puede
puede procesar
procesar la
la transacción'
transacción'
'Este
miembro
tiene
libros
'Este miembro tiene libros prestados'
prestados'
© Maximiliano Odstrcil - 120
Forzado de las Reglas de Negocio (6)
Los miembros con préstamos adeudados no pueden ser borrados
IF
IF (Select
(Select Count
Count (*)
(*) FROM
FROM loan
loan INNER
INNER JOIN
JOIN deleted
deleted
ON
ON loan.member_no
loan.member_no == deleted.member_no)
deleted.member_no) >> 00
ROLLBACK
ROLLBACK TRANSACTION
TRANSACTION
Transacción
vuelta atrás
El código del trigger controla la tabla loan
member
member
member_no
member_no lastname
lastname firstname
firstname
11
2323
3434
44
Anderson
Anderson
Barr
Barr
Anderson
Barr
Anderson
Barr
Anderson
Anderson
Andrew
Andrew
Bill
Andrew
Bill
Andrew
Bill
Bill
Bill
Bill
loan
loan
isbn
isbn copy_no
copy_no title_no
title_no mem_no
mem_no
11
44
44
33
11
11
22
11
11
44
44
33
11
11
22
33
'No
'No se
se puede
puede procesar
procesar la
la transacción'
transacción'
'Este
miembro
tiene
libros
'Este miembro tiene libros prestados'
prestados'
© Maximiliano Odstrcil - 121
Auditoría
CREATE
CREATE TABLE
TABLE `AuditProductos`
`AuditProductos` ((
`ID`
`ID` INT
INT NOT
NOT NULL
NULL AUTO_INCREMENT,
AUTO_INCREMENT,
`Tipo`
`Tipo` CHAR(1)
CHAR(1) NOT
NOT NULL,
NULL,
`Fecha`
`Fecha` DATETIME
DATETIME NOT
NOT NULL,
NULL,
`Host`
VARCHAR(45)
NOT
`Host` VARCHAR(45) NOT NULL,
NULL,
`Usuario`
VARCHAR(45)
NOT
`Usuario` VARCHAR(45) NOT NULL,
NULL,
`IDProducto`
INT
NOT
NULL,
`IDProducto` INT NOT NULL,
`Nombre`
`Nombre` VARCHAR(60)
VARCHAR(60) NOT
NOT NULL,
NULL,
`Descripcion`
`Descripcion` TEXT
TEXT NOT
NOT NULL,
NULL,
PRIMARY
KEY
(`ID`)
PRIMARY KEY (`ID`)
);
);
DELIMITER
DELIMITER //
//
CREATE
TRIGGER
CREATE TRIGGER `Trig_Productos_Ins`
`Trig_Productos_Ins`
AFTER
INSERT
ON
AFTER INSERT ON `Productos`
`Productos` FOR
FOR EACH
EACH ROW
ROW
BEGIN
BEGIN
INSERT
INSERT INTO
INTO AuditProductos
AuditProductos VALUES
VALUES
(DEFAULT,
'I',
NOW(),
(DEFAULT, 'I', NOW(),
SUBSTRING_INDEX(USER(),'@',-1),
SUBSTRING_INDEX(USER(),'@',-1),
SUBSTRING_INDEX(USER(),'@',
SUBSTRING_INDEX(USER(),'@', 1),
1),
NEW.IDProducto,
NEW.IDProducto, NEW.Nombre,
NEW.Nombre, NEW.Descripcion);
NEW.Descripcion);
END
//
END //
DELIMITER
DELIMITER ;;
© Maximiliano Odstrcil - 122