procedimientos almacenados y triggers

Procedimientos almacenados y triggers

Introduccin Los procedimientos almacenados son conjuntos de sentencias en leguaje Transact SQL que pueden almacenarse en el propio servidor. Los procedimientos almacenados de SQL Server, son ms potentes, porque permiten almacenar funciones y procedimientos compuestos por varias instrucciones, introducir saltos, bucles, etc. Tambin se pueden compilar procedimiento escritos en lenguaje C, para ampliar su potencia modularmente. Por ejemplo, podemos crear un procedimiento para recuperar el nombre de un autor, cuyo cdigo se pasa por parmetro.

CREATE PROCEDURE ObtenerNombre @au_id varchar(11) ASSELECT au_nameFROM authorWHERE author.au_id = @au_id

Cdigo fuente 142

Con esta sentencia, se crea un procedimiento almacenado, de nombre ObtenerNombre, al que se le pasa un parmetro, llamado @au_id, de tipo varchar(11), que realiza una consulta para obtener el nombre de la tabla author, cuyo cdigo coincida con el parmetro. De esta forma, si queremos obtener el nombre del autor cuyo cdigo sea '123', deberemos ejecutar el procedimiento pasndole comoargumento este valor:

Bases de datos con SQL Server 2000 Transact SQL Grupo EIDOS

170

Las llamadas a procedimientos almacenados se pueden realizar de las siguientes formas: Pasando los argumentos en el mismo orden que en el que se han declarado.

ObtenerNombre '123'

Cdigo fuente 143

Pasando los argumentos nombrados. En este caso no hace falta que los parmetros vayan en el mismo orden.

ObtenerNombre @au_id = '123'

Cdigo fuente 144

Parmetros por referencia Si ejecutamos las anteriores sentencias, obtendremos el resultado directamente en la ventana que tengamos abierta en SQL Server. Pero que pasa si queremos obtener un parmetro de salida, comoresultado de la ejecucin del procedimiento?. La solucin para este caso es utilizar la palabra reservada OUTPUT para los argumentos de salida. Si por ejemplo, queremos obtener el nmero de autores y el nmero de libros que tenemos en la base de datos, crearemos el procedimiento almacenado que muestra el Cdigo fuente 145.

CREATE PROCEDURE num_autores_libros @autores int OUTPUT, @libros int OUTPUT ASSELECT * FROM AuthorsSELECT @autores = @@ROWCOUNTSELECT * FROM TitlesSELECT @libros = @@ROWCOUNTRETURN (0)

Cdigo fuente 145

Vamos a estudiar el anterior ejemplo. Bsicamente es similar al anterior. Detrs de la palabra reservada PROCEDURE damos el nombre del procedimiento almacenado, y a continuacin proporcionamos los parmetros, junto con su tipo (que en este caso es entero), y diremos sin stos son de salida, en cuyo caso especificamos la palabra reservada OUTPUT a continuacin. Tras la palabra reservada AS se codifica el cuerpo del procedimiento. Primero contamos todas las filas de la tabla authors, realizando un SELECT * FROM Authors. A continuacin devolvemos en el parmetro @autores el valor obtenido, utilizando @@ROWCOUNT.Acto seguido se realiza lo mismo para la tabla titles. Ntese como la forma de asignar un valor a un atributo es mediante una sentencia SELECT, igualando el parmetro al valor.La funcin @@ROWCOUNT devuelve el nmero de filas que se han seleccionado. Es equivalente a la sentencia que aparece en el Cdigo fuente 146

Grupo EIDOS 19. Procedimientos almacenados y triggers

171

SELECT COUNT(*) FROM Authors

Cdigo fuente 146

El Cdigo fuente 146, por lo tanto, se podra sustituir por Cdigo fuente 147.

CREATE PROCEDURE num_autores_libros @autores int OUTPUT, @libros int OUTPUT ASSELECT @autores = (SELECT COUNT(*) FROM Authors)SELECT @libros = (SELECT COUNT(*) FROM Authors)RETURN (0)

Cdigo fuente 147

Para ejecutar el anterior procedimiento, seguiremos los siguientes pasos: 1. Declarar las variables que vamos a utilizar para llamar al procedimiento. La sintaxis para

declarar una variable es utilizar la palabra reservada DECLARE, seguido del nombre de la variable y el tipo.

DECLARE @num_autores intDECLARE @num_libros int

Cdigo fuente 148

2. Ejecutar el procedimiento. La sintaxis es utilizar la palabra reservada EXEC, seguida del nombre del procedimiento, y los parmetros, separados por comas, especificando si son de retorno.

EXEC num_autores_libros @num_autores OUTPUT, @num_libros OUTPUT

Cdigo fuente 149

3. Mostrar los resultados.

SELECT autores = @num_autores, libros = @num_libros

Cdigo fuente 150

Tras ejecutar las anteriores sentencias, obtendremos como resultado el siguiente listado: autores libros--------------- ---------------23 18(1 row(s) affected)


172

Si queremos borrar un procedimiento almacenado, ejecutaremos la sentencia DROP PROCEDURE, seguido del nombre del procedimiento. Por ejemplo, si queremos borrar el procedimiento almacenado, creado en el anterior ejemplo, escribiremos el Cdigo fuente 151

DROP PROCEDURE num_autores_libros

Cdigo fuente 151

Procedimientos almacenados de sistema SQL Server nos ofrece una serie de procedimientos almacenados ya implementados, es decir, listos para ejecutar, cada uno con su propio objetivo o fin. Por ejemplo, si deseamos saber los usuarios conectados a nuestro sistema, podemos elaborar una consulta SELECT sobre la tabla de sistema que contiene los usuarios conectados, o ejecutar el procedimiento almacenado sp_who.

Figura 92

As, si escribimos sp_who, obtendremos una lista con todos los usuarios conectados, como podemos observar en la Figura 92. Si queremos obtener una lista con todas las tablas del sistema, disponemos de otro procedimiento almacenado denominado sp_tables. Del mismo modo, si deseamos conocer todos los atributos de una tabla, deberemos ejecutar sp_columns seguido del nombre de la tabla.


173

Por ejemplo, para listar los atributos de la tabla authors ejecutamos sp_columns authors, yobtenemos el resultado de la Figura 93 Existe una gran variedad de procedimientos almacenados, como por ejemplo para crear dispositivos, para comprobar el espacio usado por una tabla, etc. Aqu slo hemos visto dos ejemplos de aplicacin.

Figura 93

Extended stored proceduresUna de las principales ventajas que ofrece SQL Server es que permite la ejecucin de procedimientos almacenados escritos en otro lenguaje de programacin, como por ejemplo C, aprovechando de este modo toda la potencia que ofrece un compilador de este estilo. De todos es conocida la potencia que ofrece el lenguaje C, ya que es un compilador a bajo nivel. Esto es, permite interaccionar con el hardware, obtener datos del Sistema Operativo, etc. La tcnica que permite el aprovechamiento de un lenguaje externo a SQL Server, para el diseo de procedimientos almacenados se denomina Extended Stored Procedures. Este es un caso un poco ms complicado que el manejo de procedimientos almacenados intrnsecos al propio SQL Server. Los Extended Stored Procedures se implementan como DLLs, que sern privadas a SQL Server, y cuya gestin del espacio de memoria corresponde a ste. Una DLL (Dinamic Link Library) es un conjunto de funciones, que se cargan dinmicamente en memoria, esto es, se cargan en memoria cuando vayan a ser usadas, y se descargan cuando no se necesiten. Si por ejemplo, uno de estos procedimientos intenta acceder a una direccin que cae fuera del espacio de


174

direcciones permitido, SQL Server atrapa la excepcin, mata la tarea, y libera los bloqueos, continuando normalmente con su trabajo. Para registrar una Extended Stored Procedure, el administrador del sistema deber ejecutar la sentencia en Transact SQL que aparece en el Cdigo fuente 152.

sp_addextendedproc nombre_funcin, nombre_DLL

Cdigo fuente 152

Para ejecutar una de estas funciones en una base de datos distinta de aquella en la que se ha registrado, se deber preceder el nombre de la base de datos al nombre de la funcin. Hay que destacar que estos procedimientos nicamente podrn ser ejecutados y registrados por el administrador del sistema. Si ste desea permitir la ejecucin a los dems usuarios, deber ejecutar el Cdigo fuente 153.

GRANT EXEC ON procedimiento TO PUBLIC

Cdigo fuente 153

Esta sentencia SQL otorga (GRANT) privilegios de ejecucin (EXEC) sobre el procedimiento especificado, a todos los usuarios de la base de datos. Si slo deseamos otorgar este privilegio a un usuario, deberemos especificar su identificador de usuario tras TO.

TriggersLos disparadores de procedimiento, ms comnmente conocidos como triggers, son una especie de procedimientos almacenados, a diferencia que se ejecutan cuando ocurre un evento sobre alguna tabla. Entendemos por evento, cualquier accin del tipo:

Insercin Borrado Actualizacin

La sintaxis de la sentencia de creacin de triggers es la siguiente: CREATE TRIGGER nombre ON tabla FOR accion AS codigo

donde accin especifica el evento que debe ocurrir para que se dispare el trigger, y que puede ser: UPDATE: actualizacin. INSERT: insercin. DELETE: borrado.


175

Por ejemplo, si queremos crear un trigger llamado modificacin_autor, sobre la tabla Authors, que muestre un mensaje cada vez que se actualiza una fila de la tabla, deberemos escribir el Cdigo fuente 154.

CREATE TRIGGER modificacion_autor ON authors FOR UPDATE ASprint "Han actualizado la tabla de autores"

Cdigo fuente 154

Para comprobar el funcionamiento de este trigger, podemos actualizar cualquier fila de la tabla de autores, por ejemplo con la sentencia que aparece en el Cdigo fuente 155.

UPDATE authors SET au_fname = 'Miguel' WHERE au_id = '267-41-2394'

Cdigo fuente 155

Con esto conseguimos dos cosas, actualizar el nombre del autor cuyo cdigo es el especificado a Miguel, y obtener el mensaje que se muestra como ejecucin del trigger de actualizacin. Sin embargo, los triggers en SQL Server tienen una serie de limitaciones:

1. No se puede disparar un trigger dentro de otro trigger, ya que dara lugar a un bucle infinito 2. Por esta razn, un trigger no puede ejecutar instrucciones DDL (lenguaje de definicin de

datos) 3. No se pueden ejecutar sentencias como SELECT INTO o de creacin de dispositivos dentro

de un trigger Del mismo modo, para borrar un trigger, deberemos ejecutar la sentencia DROP TRIGGER trigger. Por ejemplo, si queremos borrar el trigger anteriormente creado, ejecutaremos el Cdigofuente 156

DROP TRIGGER modificacion_autor

Cdigo fuente 156

Ejemplos prcticos de uso de procedimientos almacenados

Introduccin Los procedimientos almacenados es una de las principales funcionalidades que ofrece SQL-Server, que permite al usuario que los usa abstraerse de la complejidad interna que puede encerrar. Por ejemplo, pueden ser llamados desde otras aplicaciones, con los parmetros adecuados, siendo el servidor el encargado de realizar las operaciones, descargando de trabajo al cliente. Los procedimientos almacenados suelen utilizar sentencias de diverso tipo de T_SQL, como pueden ser las de control de flujo, las de iteracin, etc., que veremos en detalle mediante algunos ejemplos prcticos.

La sentencia IF ... ELSE Esta sentencia permite evaluar una expresin y provocar la entrada o ejecucin de un bloque u otro, dependiendo de que la condicin sea verdadera o falsa. El Cdigo fuente 157 muestra un procedimiento almacenado encargado de insertar un nuevo tipo de transporte si no est dado de alta en la base de datos. Para ello se comprueba que el nmero de filas que devuelve el select sea cero, en cuyo caso la condicin es verdadera, y se ejecutar el insert.


178

CREATE PROCEDURE sp_Insertar_TipoTransporte@TT_ID int,@TT_Nombre varchar(40)

ASif (select count(*) from TipoTransporte where TT_ID = @TT_ID) = 0

insert into TipoTransportevalues (@TT_ID, @TT_Nombre)

Cdigo fuente 157

Probemos a insertar un tipo de transporte que exista, y otro que no exista, para comprobar comofunciona. Para ello ejecutamos el Cdigo fuente 158.

exec sp_Insertar_TipoTransporte 1, 'Metro'exec sp_Insertar_TipoTransporte 4, 'Tranva'

Cdigo fuente 158

Para comprobar lo que ha ocurrido en la tabla de tipos de transporte, ejecutamos el siguiente cdigo.

SELECT *FROM TipoTransporte

Cdigo fuente 159

Y como podemos comprobar, slo se ha insertado el ltimo registro. Podemos hacerlo un poco ms elegante, y devolver un parmetro que nos indique si ha habido error o no.

CREATE PROCEDURE sp_Insertar_TipoTransporte@TT_ID int,@TT_Nombre varchar(40),@error char(1) output

ASif (select count(*) from TipoTransporte where TT_ID = @TT_ID) = 0 begin

set @error = 'N'insert into TipoTransportevalues (@TT_ID, @TT_Nombre)

endelse set @error = 'S'

Cdigo fuente 160

Podemos observar una novedad, y es que se ha encerrado entre las palabras begin...end el bloque que deseamos se ejecute cuando se cumple la condicin, adems del uso de la sentencia set para asignar un valor a un parmetro o variable. Para ejecutar ahora el procedimiento, necesitamos un parmetro ms de llamada, que devolver S si se ha producido error, y N si se ha insertado el registro con xito, cuyo tipo debe coincidir con el tipo del parmetro de salida declarado en el procedimiento.

Grupo EIDOS 20. Ejemplos prcticos de uso de procedimientos almacenados

179

declare @error char(1)exec sp_Insertar_TipoTransporte 1, 'Metro', @error outputselect @error

Cdigo fuente 161

Al ejecutar este cdigo, mostramos el parmetro de salida y vemos como, efectivamente, se devuelveuna S, lo que indica que se ha producido un error. Sin embargo, si ejecutamos este otro cdigo, se devolver una N, lo que indica que el registro se ha insertado correctamente, al no existir todava en la base de datos.

declare @error char(1)exec sp_Insertar_TipoTransporte 5, 'Aeropuerto', @error outputselect @error

Cdigo fuente 162

Otro ejemplo nos muestra como comprobar si existen filas para un parmetro determinado, con el fin de mostrar un mensaje determinado.

CREATE PROCEDURE sp_Hay_Comerciales@Prv varchar(40)

ASif (select count(*)

from Provinciainner join Comercial on Comercial.Prv_ID = Provincia.Prv_ID

and Provincia.Prv_Nombre = @Prv) > 0print 'Hay comerciales asociados a la provincia de ' + @Prv

else print 'No hay comerciales asociados a la provincia de ' + @Prv

Cdigo fuente 163

En este cdigo podemos comprobar como se obtiene el nmero de registros de un inner join, para obtener el identificador de la provincia que coincide con el nombre que se pasa como parmetro. Si dicho atributo es nulo, querr decir, al ser un inner join, que o bien no existe la provincia, o bien no existe ningn comercial asociado a ella. Ejecutemos el procedimiento para comprobar si existen comerciales para la provincia de Barcelona.

exec sp_Hay_Comerciales 'Barcelona'

Cdigo fuente 164

La sentencia CASE Una variante a la instruccin if...else es la sentencia case, muy til sobre todo cuando tenemos muchos if anidados. Por ejemplo, el cdigo expuesto antes utilizando if...else, equivaldra al siguiente, utilizando case.


180

CREATE PROCEDURE sp_Hay_Comerciales2@Prv varchar(40)

ASselectcase (select count(*) from Provincia

inner join Comercial on Comercial.Prv_ID = Provincia.Prv_IDand Provincia.Prv_Nombre = @Prv)

when 0 then 'No hay comerciales asociados a la provincia de ' + @Prvelse 'Hay comerciales asociados a la provincia de ' + @Prvend

Cdigo fuente 165

Ejecucin dinmica de sentencias: la instruccin EXECComo hemos visto, la instruccin exec se utiliza para ejecutar un procedimiento almacenado, pero no slo eso, sino que adems sirve para evaluar una expresin de tipo carcter. Por ejemplo, el siguiente cdigo muestra como ejecutar un select de todas las provincias.

Exec (select * from Provincia)

Cdigo fuente 166

Esto puede resultar muy til para montar sentencias en tiempo de ejecucin, cuyos valores no conocemos a priori, o para los cuales deseamos realizar un tratamiento distinto dependiendo de los valores que tomen. El siguiente cdigo mostrara un procedimiento almacenado que se encarga de realizar bsquedas de clientes. Recibe dos parmetros, uno es la provincia y otro es el cdigo postal. Si se especifica nicamente la provincia, se realizar una bsqueda de todos los clientes que sean de esta provincia, mientras que si especifica el cdigo postal, se realizar una bsqueda, adems, por este atributo.

CREATE PROCEDURE sp_Clientes@Prv varchar(40),@CP varchar(5)

AS-- La variable condicion almacena la clasula where y la variable join los

joindeclare @condicion varchar(255)declare @join varchar(255)set @condicion = ''set @join = ''

-- Si se ha especificado la variable @Prv, montar la condicion y el joinif not @Prv is null begin

set @condicion = ' Provincia.Prv_Nombre = ' + '''' + @Prv + ''''set @join = ' inner join Provincia on Provincia.Prv_ID =

Cliente.Prv_ID'end

-- Si se ha especificado el cod postal, concatenar la condicinif not @CP is null begin

if @condicion ''set @condicion = @condicion + ' and '

Grupo EIDOS 20. Ejemplos prcticos de uso de procedimientos almacenados

181

set @condicion = @condicion + ' Cli_CodPostal = ' + '''' + @CP + ''''endif @condicion

set @condicion = where + @condicionexec ('select * from cliente ' + @join + @condicion)

Cdigo fuente 167

Manejamos dos variables, una almacenar la condicin, que se ira concatenando, dependiendo de los parmetros que se indiquen, y otra almacenar el join que se deber realizar con la tabla de provincia, en el caso de que se especifique como parmetro el nombre de la provincia. Una vez montadas estas cadenas, se ejecuta la sentencia exec, para evaluar la expresin resultante. Si ejecutamos el anterior procedimiento almacenado, sin especificar ninguno de los parmetros, obtendremos todos los clientes

exec sp_Clientes null, null

Cdigo fuente 168

Si especificamos el parmetro provincia = Madrid, obtendremos todos los clientes de la provincia de Madrid

exec sp_Clientes Madrid, null

Cdigo fuente 169

Si adems especificamos el parmetro codigo postal, obtendremos lo siguiente.

exec sp_Clientes 'Madrid', 28023

Cdigo fuente 170

Conversin de tipos Muchas veces nos encontraremos la necesidad de convertir un valor de un tipo a otro tipo para operar con l. Es entonces cuando intervienen dos instrucciones que nos permiten realizar esto. Dichas instrucciones son cast y convert. La sintaxis de la expresin cast es la siguiente. Cast (expresion as tipo)

y la de convert. Convert (tipo, expresion)


182

Por ejemplo, podemos utilizarlo para convertir un tipo de datos carcter a fecha.

Cast (01/01/01 as datetime)

Cdigo fuente 171

O un carcter a entero

cast (3 as int)

Cdigo fuente 172

La sentencia WHILE La instruccin while permite iterar un numero de veces que no conocemos a priori, hasta que deje de cumplirse la condicin boleana. Su sintaxis es la siguiente. WHILE expresionsentencias[BREAK][CONTINUE]

La clasula break es optativa y indica al bucle que deje de ejecutarse, mientras que la clasula continue, que tambin es optativa, indica al bucle que se reinicie. En el siguiente ejemplo, se recorre los comerciales, mientras que la categora sea distinta de 6, y vaduplicando la misma, hasta que alguno supera el valor de 6.

CREATE PROCEDURE sp_ActualizaComercialAS

WHILE (SELECT Com_Categoria FROM Comercial) '6'BEGIN

UPDATE ComercialSET Com_Categoria = cast(Com_Categoria as int) * 2

IF (SELECT MAX(Com_Categoria) FROM Comercial) > 6BREAK

ELSECONTINUE

END

Cdigo fuente 173

Triggers en SQL-Server 2000

Introduccin Como ya se ha comentado, los triggers o desencadenadores son una especie de procedimientos almacenados, que se ejecutan cuando ocurre una accin dentro de la base de datos. As, si por ejemplo se ejecuta una insercin, una actualizacin, o un borrado de una tabla, se ejecutaran las sentencias definidas para el trigger en concreto de esa tabla especfica. Recordamos cual es su sintaxis: CREATE TRIGGER nombreON tablaFOR [DELETE | INSERT | UPDATE]ASsentencias

Las palabras reservadas DELETE, INSERT y UPDATE corresponden a cada una de las acciones para las cuales se puede definir un desencadenador dentro de la tabla especificada. El bloque de sentencias permite prcticamente cualquier tipo de ellas dentro del lenguaje T-SQL, pero con ciertas limitaciones. Por ejemplo, no se podr utilizar la sentencia select, ya que un trigger no puede devolver datos al usuario, sino que simplemente se ejecuta para cambiar o comprobar los datos que se van a insertar, actualizar o borrar.


184

Las tablas deleted e insertedDentro de la definicin de un trigger, podemos hacer referencia a un par de tablas lgicas, cuyaestructura es similar a la tabla donde se esta ejecutando el trigger; es decir, es una copia de la tabla en la cual se van a insertar o borrar los datos, y que contiene, precisamente, los datos que van a ser aadidos o borrados. La utilidad de estas dos tablas es la de realizar comprobaciones entre los datos antiguos y los nuevos. As, por ejemplo, si queremos recuperar los datos de la tabla que estamos borrando, dentro del trigger, se deber ejecutar el siguiente cdigo:

SELECT *FROM deleted

Cdigo fuente 174

Tipos de desencadenadoresSQL-Server permite la definicin de varios tipos de triggers, entre los cuales cabe destacar los siguientes: Desencadenadores mltiples: para una misma tabla, se pueden definir distintos triggers para la

misma accin, es decir, si definimos un trigger para insert, y resulta que dicha tabla ya tena definido un trigger para esa misma accin, se ejecutarn ambos triggers cuando ocurra dicho evento sobre la tabla.

Desencadenadores recursivos: se permite la recursividad entre las llamadas a los triggers, es decir, un trigger puede llamar a su vez a otro, bien de forma directa, bien de forma indirecta.

Desencadenadores anidados: si un trigger cambia una tabla en la que se encuentra definido otro trigger, se provoca la llamada de este ltimo que, si a su vez vuelve a modificar otra tabla, puede provocar la ejecucin de otro trigger, y as sucesivamente. Si se supera el nivel de anidamiento permitido, se cancelar la ejecucin de los triggers.

Limitaciones de los triggersAunque ya se han comentado algunas de las limitaciones a la hora de programar triggers, veamos en detalle las restricciones que implica la definicin de triggers: Un trigger slo se puede aplicar a una tabla Aunque un trigger se defina dentro una sola base de datos, puede hacer referencia a objetos que se

encuentran fuera de la misma La misma accin del desencadenador puede utilizarse para definir ms de un trigger sobre la

misma tabla La opcin SET elegida dentro de la ejecucin de un desencadenador, volver a su estado

previamente definido una vez concluya la ejecucin del mismo

Grupo EIDOS 21. Triggers en SQL Server 2000

185

As mismo, no se permite la utilizacin de las sentencias del DDL dentro de la definicin de un trigger

En una vista no se puede utilizar un desencadenador

Resolucin diferida de nombresLa resolucin diferida de nombres es una utilidad que permite escribir triggers que hagan referencia a tablas que no existen en el momento de la compilacin. Por ejemplo, el siguiente cdigo hace referencia a una tabla x, que no existe en el momento de escribir el trigger.

CREATE TRIGGER trigger1on authorsFOR INSERT, UPDATE, DELETEAS

SELECT a.au_lname, a.au_fname, x.infoFROM authors a INNER JOIN x

ON a.au_id = x.au_idGO

Cdigo fuente 175

Ejemplos Veamos a continuacin algunos ejemplos que complementen lo visto hasta ahora. El siguiente cdigo, muestra la forma de enviar un mensaje de correo electrnico a una persona, cuando se aade una nueva oferta.

CREATE TRIGGER enviar_correoON OfertaFOR INSERTAS

EXEC master..xp_sendmail 'Pepe','Tenemos una nueva oferta en nuestra base de datos.'

GO

Cdigo fuente 176

Sin embargo, si queremos que se enve el mensaje cada vez que se cambia algo en la tabla de ofertas, deberemos reescribir el trigger para las acciones update y delete, como se muestra en el siguiente cdigo.

DROP TRIGGER enviar_correoGO

CREATE TRIGGER enviar_correoON OfertaFOR INSERT, UPDATE, DELETEAS

EXEC master..xp_sendmail 'Pepe',


186

'La tabla de ofertas ha cambiado'GO

Cdigo fuente 177

En el Cdigo fuente 178, se muestra como crear un trigger que, utilizando la tabla inserted para comprobar los valores que se van a aadir o modificar, posibilite la cancelacin de la accin, si el nivel del trabajo no est dentro de los valores permitidos.

CREATE TRIGGER empleado_modificarON employeeFOR INSERT, UPDATEAS

/* Obtener el nivel para el trabajo actual */

DECLARE @min_lvl tinyint,@max_lvl tinyint,@emp_lvl tinyint,@job_id smallint

SELECT @min_lvl = min_lvl,@max_lvl = max_lvl,@emp_lvl = i.job_lvl,@job_id = i.job_id

FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_idJOIN jobs j ON j.job_id = i.job_id

IF (@job_id = 1) and (@emp_lvl 10)BEGIN

RAISERROR ('Debe especificar el nivel 10 para el trabajo 1', 16, 1)ROLLBACK TRANSACTION

ENDELSEIF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)BEGIN

RAISERROR ('El nivel para el trabajo:%d debe estar entre %d y %d.',16, 1, @job_id, @min_lvl, @max_lvl)

ROLLBACK TRANSACTIONEND

Cdigo fuente 178

Primero declaramos las variables min_lvl y max_lvl, que almacenarn los valores mnimo y mximopermitidos para un trabajo, emp_lvl, que almacenar el nivel para el empleado, y job_id que almacena el identificador del trabajo que se va a actualizar. A continuacin realizamos un join entre la tabla de empleados y la tabla inserted o, lo que es lo mismo, entre la tabla de empleados tal y como est antes de ejecutar el trigger, y la tabla de empleados tal y como quedara despus de ejecutarlo (en otras palabras, entre la antigua y la nueva), y a continuacin con la tabla de trabajos. Todo esto sirve para encontrar los valores de la tabla de trabajos que corresponden al trabajo que vamos a asignar al empleado. Ahora se comprueba si el trabajo que estamos asignando es el 1, en cuyo caso el valor para el nivel debera ser 10. Para ello comprobamos esta situacin mediante un if y, en caso de que sea verdadero, se lanza un error con la sentencia RAISERROR, y se cancela la transaccin con ROLLBACK TRANSACTION.

Grupo EIDOS 21. Triggers en SQL Server 2000

187

En otro caso se comprueba que el nivel de la tarea est dentro de los lmites permitidos para el empleado. Si esto no se cumple se volver a lanzar un error, y se cancelar la transaccin. Para los desconocedores del lenguaje C, quiz la ltima sentencia RAISERROR no les sea demasiado clara. Destacar, simplemente, que cada %d que aparece dentro de la cadena de texto ser sustituido respectivamente por el valor de cada una de las tres ltimas variables que aparecen dentro de dicha sentencia.

procedimientos almacenados y triggers

Documents