ths decimo segunda sesion (1).pdf

7/25/2019 THS Decimo Segunda Sesion (1).pdf

http://slidepdf.com/reader/full/ths-decimo-segunda-sesion-1pdf 1/22

UNIVERSIDAD NACIONAL DE INGENIERIA

FACULTAD DE INGENIERIA INDUSTRIAL Y DE SISTEMAS

Rev.1.0 Fecha Efectiva : 11/06/2012 Pág. 1 de 22

CURSO: TALLER DE HERRAMIENTAS DE SOFTWARE

TALLER DE HERRAMIENTASDE SOFTWARE

DUODECIMA SESION

DOCENTE:

Ing. Ysabel Rojas S.







HISTORIAL DE LAS REVISIONES

Fecha: 01/03/2011 Autor/es - Cargo: Ing. Ysabel Rojas Solís

Revisado: Versión: 1.0Control de cambios:

(Indicar cambios si setrata de una versión

de documento)

Fecha Autor Versión Referencia del Cambio

01/03/11 Ysabel Rojas 1.0







AGENDA DECIMA SESION:

1.1 PROCEDIMIENTOS ALMACENADOS: .................................... 4

STORE PROCEDURES .............................................................. 4

2. TRANSACCIONES EN SQL SERVER: ..................................... 13

TRANSACTION ............................................................................ 13

3. TRIGGERS Y DESENCADENADORES ................................. 16







DECIMO PRIMERA SESION

1.1 PROCEDIMIENTOS ALMACENADOS:

STORE PROCEDURES

Los procedimientos almacenados son conjuntos de sentencias en lenguaje Transact SQLque pueden almacenarse en el propio servidor.

La potencia de los procedimientos almacenados de SQL Server radican en que permitenalmacenar funciones y procedimientos compuestos por varias instrucciones, introducirsaltos, bucles, etc. También se pueden compilar procedimiento escritos en lenguaje C,

para ampliar su potencia modularmente.EJEMPLO 1

Crear un procedimiento para recuperar el nombre de un Cliente, cuyo código se pasa porparámetro.

CREATE PROCEDURE ObtenerNombre @IDCLIENTE varchar(11) ASSELECT NOMBRECOMPAÑíAFROM CLIENTESWHERE IDCLIENTE = @IDCLIENTE

Con esta sentencia, se crea un procedimiento almacenado, de nombre ObtenerNombre, alque se le pasa un parámetro, llamado @idcliente, de tipo varchar(11), que realiza unaconsulta para obtener el nombre de la tabla clientes, cuyo código coincida con elparámetro. De esta forma, si queremos obtener el nombre del cliente cuyo código sea'8011', deberemos ejecutar el procedimiento pasándole como argumento este valor:

Las llamadas a procedimientos almacenados se pueden realizar de las siguientes formas

1) Pasando los argumentos en el mismo orden que en el que se han declarado

Exec ObtenerNombre '8011'

esta sentencia produce la siguiente la salida







2) Pasando los argumentos nombrados. En este caso no hace falta que los parámetrosvayan en el mismo orden.

OBTENERNOMBRE @AU_ID = 'ANTON'

EJEMPLO 2

Crear el procedimiento que me permita listar los pedidos de un cliente realizados dentroun periodo, mostrando el vendedor que lo atendió, la fecha de pedido, fecha de envío y lafecha de entrega

CREATE PROCEDURE LISTARPEDIDOS(@IDCLIENTE VARCHAR(7),@FECHAINICIAL SMALLDATETIME,@FECHAFINAL SMALLDATETIME

) ASSELECTPEDIDOS.IDPEDIDO,CLIENTES.NOMBRECOMPAÑÍA,EMPLEADOS.NOMBRE +EMPLEADOS.APELLIDOS ASEMPLEADO,PEDIDOS.CARGO,PEDIDOS.FECHAPEDIDOPEDIDOS.FECHAENVIO,PEDIDOS.FECHAENTREGAFROM PEDIDOSINNER JOIN CLIENTES ON PEDIDOS.IDCLIENTE=CLIENTES.IDCLIENTEINNER JOIN EMPLEADOS ON PEDIDOS.IDEMPLEADO=EMPLEADOS.IDEMPLEADOWHERE PEDIDOS.IDCLIENTE=@IDCLIENTE AND FECHAPEDIDOBETWEEN @FECHAINICIAL AND @FECHAFINAL

Ejecutando el procedimiento almacenado

EXEC LISTARPEDIDOS '8011','12/05/95','22/12/96'







1.2 USO DE OUTPUT: PARÁMETROS POR REFERENCIA

Al ejecutar las anteriores sentencias, se obtiene el resultado directamente en la ventanaque se tenga abierta en SQL Server. Pero si se quiere obtener un parámetro de salida,como resultado de la ejecución del procedimiento, la solución para este caso es utilizar lapalabra reservada OUTPUT para los argumentos de salida.

EJEMPLO 3

Se quiere obtener el número de clientes y el número de productos que se tiene en la basede datos crear el procedimiento almacenado y las instrucciones de ejecución.

Para ello se creara el procedimiento almacenado que muestra el Código fuente.

CREATE PROCEDURE num_clientes_productos @clientes int OUTPUT, @Productosint OUTPUT ASSELECT * FROM clientes

SELECT @clientes = @@ROWCOUNTSELECT FROM productosSELECT @productos = @@ROWCOUNTRETURN (0)

Básicamente es similar a los anteriores ejemplos. Detrás de la palabra reservadaPROCEDURE damos el nombre del procedimiento almacenado, y a continuaciónproporcionamos los parámetros, junto con su tipo (que en este caso es entero), y diremossin éstos son de salida, en cuyo caso especificamos la palabra reservada OUTPUT acontinuación. Tras la palabra reservada AS se codifica el cuerpo del procedimiento.

Primero contamos todas las filas de la tabla clientes, realizando un SELECT * FROM

clientes. A continuación devolvemos en el parámetro @clientes el valor obtenido,utilizando @@ROWCOUNT

Acto seguido se realiza lo mismo para la tabla Productos. Nótese como la forma de asignarun valor a un atributo es mediante una sentencia SELECT, igualando el parámetro al valor

La función @@ROWCOUNT devuelve el número de filas que se han seleccionado. Esequivalente a la sentencia que aparece en el Código fuente

SELECT COUNT(*) FROM Productos

Se podría sustituir por Código fuente

CREATE PROCEDURE num_clientes_productos @clientes int OUTPUT, @clientes intOUTPUT ASSELECT @clientes = (SELECT COUNT(*) FROM cl ients)

SELECT @productos = (SELECT COUNT(*) FROM Productos)RETURN (0)

Para ejecutar el anterior procedimiento, seguiremos los siguientes pasos







Declarar las variables que vamos a utilizar para llamar al procedimiento. La sintaxis paradeclarar una variable es utilizar la palabra reservada DECLARE, seguido del nombre de lavariable y el tipo

DECLARE @num_cl ientes in tDECLARE @num_Productos int

Ejecutar el procedimiento. La sintaxis es utilizar la palabra reservada EXEC, seguida delnombre del procedimiento, y los parámetros, separados por comas, especificando si sonde retorno

EXEC num_clientes_productos @num_clientes OUTPUT, @num_productos OUTPUT

Mostrar los resultados

SELECT clientes = @num_clientes, productos = @num_productos

Tras ejecutar las anteriores sentencias, obtendremos como resultado el siguiente listado







1.3 USO DE LA FUNCIÓN EXECUTE

Ejecuta una función definida por el usuario que devuelve valores escalares, unprocedimiento del sistema, un procedimiento almacenado definido por el usuario o unprocedimiento almacenado extendido

Admite también la ejecución de una cadena de caracteres. Ejemplo

DECLARE @TABLE VARCHAR(25

SET @TABLE='CLIENTES'

EXEC ('SELECT * FROM ' + @TABLE

1.4 MODIFICAR UN PROCEDIMIENTO ALMACENADO

Si queremos modificar un procedimiento almacenado, ejecutaremos la sentencia ALTERPROCEDURE, seguido del nombre del procedimiento. Por ejemplo, si queremos hacercambios el procedimiento almacenado, creado en el anterior ejemplo, escribiremos elCódigo fuente

ALTER PROCEDURE num_clientes_productos @clientes int OUTPUT, @productos intOUTPUT ASSELECT @clientes = (SELECT COUNT(*) FROM clientes where pais=’alemania’)

SELECT @productos = (SELECT COUNT(*) FROM productos where preciounidad>10)RETURN (0)

1.5 ELIMINAR UN PROCEDIMIENTO ALMACENADO

Si queremos borrar un procedimiento almacenado, ejecutaremos la sentencia DROPPROCEDURE, seguido del nombre del procedimiento. Por ejemplo, si queremos borrar elprocedimiento almacenado, creado en el anterior ejemplo, escribiremos el Código fuente

DROP PROCEDURE num_clientes_clientes

Ejemplos de Procedimientos Almacenados:







Utilizar EXECUTE con DEFAULT

El ejemplo siguiente crea un procedimiento almacenado con un valor predeterminado parasu parametro. Cuando se ejecuta el procedimiento,el valor predeterminado se insertancomo parámetro si no se pasa ningún valor en la llamada o si se especifica el valorpredeterminado.

CREATE PROCEDURE LISTARCLIENTES(@PAIS VARCHAR(25) ='ARGENTINA')ASSELECT IDCLIENTE,NOMBRECOMPAÑÍA,DIRECCIÓN,PAÍS FROM CLIENTESWHERE PAÍS=@PAIS

Observe las distintas formas en las que se puede utilizar la palabra clave DEFAULT

Sino se especifica el valor del parametro se asume entonces el valor por defecto es decirse tomara entonces el valor “ARGENTINA”

EXEC LISTARCLIENTES DEFAULT







1.6 PROCEDIMIENTOS ALMACENADOS DE SISTEMA

SQL Server nos ofrece una serie de procedimientos almacenados ya implementados, esdecir, listos para ejecutar, cada uno con su propio objetivo o fin. Por ejemplo, si deseamossaber los usuarios conectados a nuestro sistema, podemos elaborar una consulta SELECTsobre la tabla de sistema que contiene los usuarios conectados, o como otra alternativaejecutar el procedimiento almacenado sp_who.

sp_who

Si escribimos sp_who, obtendremos una lista con todos los usuarios conectados,

sp_tables

Si queremos obtener una lista con todas las tablas del sistema, disponemos de otroprocedimiento almacenado denominado sp_tables.

sp_columns

Si deseamos conocer todos los atributos de una tabla, deberemos ejecutar sp_columnsseguido del nombre de la tabla.

Por ejemplo, para listar los atributos de la tabla Categorias ejecutamos

sp_columns Categorias

y obtenemos el resultado de la Figura







UTILIZAR EXECUTE CON UN PROCEDIMIENTO ALMACENADO EXTENDIDO

El ejemplo siguiente utiliza el procedimiento almacenado extendido XP_CMDSHELL paraenumerar un directorio de todos los archivos con una extensión de nombre de archivo .exe.

XP_CMDSHELL

Ejecuta una cadena de comandos como comando del sistema operativo y devuelve elresultado como filas de texto.

USE MASTEREXECUTE XP_CMDSHELL 'DIR C: *.EXE'







1.7 UTILIZAR COMANDOS DE RED DE WINDOWS NT

Este ejemplo muestra el uso de xp_cmdshell en un procedimiento almacenado. Esteejemplo notifica a los usuarios (con net send) que se va a cerrar SQL Server, detiene elservidor (con net pause) y, después, apaga el servidor (con net stop).

CREATE PROCEDURE APAGADO10 ASEXEC xp_cmdshell net send /domain:SQL_USERS ''SQL Server shutt ing down

in 10 minutes. No more connections allowed.', no_outputEXEC xp_cmdshell 'net pause sqlserver'WAITFOR DELAY '00:05:00'EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down

in 5 minutes.', no_outputWAITFOR DELAY '00:04:00'EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutt ing down

in 1 minute. Log off now.', no_output

WAITFOR DELAY '00:01:00'EXEC xp_cmdshell 'net stop sqlserver', no_output

Existe una gran variedad de procedimientos almacenados, como por ejemplo para creardispositivos, para comprobar el espacio usado por una tabla, etc. que en este nivel aun noserá visto







2. TRANSACCIONES EN SQL SERVER:

TRANSACTION

Entre las habilidades de todo Sistema Gestor de Bases de Datos Relaciones tiene queestar la de permitir al programador crear transacciones. El SQL Server nos permite trabajarcon transacciones de manera sencilla y eficaz.

Una transacción es un conjunto de operaciones que van a ser tratadas como una únicaunidad.

La transacción más simple en SQL Server es una única sentencia SQL. Por ejemplo unasentencia como esta:

UPDATE PRODUCTOS SET PRECIOUNIDAD=20 WHERE NOMBREPRODUCTO=’QUESO LAIVE’

Esta es una transacción ‘autocommit’, una transacción autocompletada. Cuando enviamosesta sentencia al SQL Server se escribe en el fichero de transacciones lo que va a ocurrir ya continuación realiza los cambios necesarios en la base de datos. Si hay algún tipo deproblema al hacer esta operación el SQL Server puede leer en el fichero de transaccioneslo que se estaba haciendo y si es necesario puede devolver la base de datos al estado enel que se encontraba antes de recibir la sentencia.

Este tipo de transacciones no requieren de la intervención humana puesto que el sistemase encarga de todo. Sin embargo si hay que realizar varias operaciones y queremos quesean tratadas como una unidad tenemos que crear esas transacciones de maneraexplícita.

La sentencia que se utiliza para indicar el comienzo de una transacción es ‘BEGIN TRAN’.Si alguna de las operaciones de una transacción falla hay que deshacer la transacción ensu totalidad para volver al estado inicial en el que estaba la base de datos antes deempezar. Esto se consigue con la sentencia ‘ROLLBACK TRAN’.

Si todas las operaciones de una transacción se completan con éxito hay que marcar el finde una transacción para que la base de datos vuelva a estar en un estado consistente conla sentencia ‘COMMIT TRAN’.

Un ejemplo Trabajaremos con la base de datos SUPERMERCADOSNEPTUNO ennuestros ejemplos.Vamos a realizar una transacción que modifica el precio de dosproductos de la base de datos.

USE SUPERMERCADOSNEPTUNO







DECLARE @Error int--Declaramos una variable que utilizaremos para almacenar un posible código deerrorBEGIN TRAN--Iniciamos la transacciónUPDATE Productos SET PrecioUnidad=2.5 WHERE NombreProducto =’Queso de

cabra’--Ejecutamos la primera sentenciaSET @Error=@@ERROR--Si ocurre un error almacenamos su código en @Error--y saltamos al trozo de código que deshara la transacción. Si, eso de ahí es un--GOTO, el demonio de los programadores, pero no pasa nada por usarlo--cuando es necesarioIF (@Error<>0) GOTO TratarError--Si la primera sentencia se ejecuta con éxito, pasamos a la segundaUPDATE Productos SET PrecioUnidad=2.5 WHERE NombreProducto=’Queso decabra’SET @Error=@@ERROR

--Y si hay un error hacemos como antesIF (@Error<>0) GOTO TratarError--Si llegamos hasta aquí es que los dos UPDATE se han completado con--éxito y podemos “ guardar” la transacción en la base de datosCOMMIT TRANTratarError:--Si ha ocurrido algún error l legamos hasta aquíIf @@Error<>0 THENBEGINPRINT ‘Ha ecorrido un error. Abortamos la transacción’--Se lo comunicamos al usuario y deshacemos la transacción--todo volverá a estar como si nada hubiera ocurrido

ROLLBACK TRANEND

Como se puede ver para cada sentencia que se ejecuta miramos si se ha producido o noun error, y si detectamos un error ejecutamos el bloque de código que deshace latransacción.

NOTA: Hay una interpretación incorrecta en cuanto al funcionamiento de las transaccionesque esta bastante extendida. Mucha gente cree que si tenemos varias sentencias dentrode una transacción y una de ellas falla, la transacción se aborta en su totalidad. ¡Nada máslejos de la realidad! Si tenemos dos sentencias dentro de una transacción.

USE SUPERMERCADOABCBEGIN TRAN







UPDATE PRODUCTOSSET PRECIOUNIDAD=2.5 WHERE NOMBREPRODUCTO=’QUESO CABRA’

UPDATE PRODUCTOSSET PRECIOUNIDAD=2.5 WHERE NOMBREPRODUCTO=’QUESO CABRA’COMMIT TRAN

Estas dos sentencias se ejecutarán como una sola. Si por ejemplo en medio de latransacción (después del primer update y antes del segundo) hay un corte de electricidad,cuando el SQL Server se recupere se encontrará en medio de una transacción y, o bien latermina o bien la deshace, pero no se quedará a medias.El error está en pensar que si laejecución de la primera sentencia da un error se cancelará la transacción. El SQL Serversólo se preocupa de ejecutar las sentencias, no de averiguar si lo hacen correctamente o sila lógica de la transacción es correcta. Eso es cosa nuestra. Por eso en el ejemplo quetenemos más arriba para cada sentencia de nuestro conjunto averiguamos si se haproducido un error y si es así actuamos en consecuencia cancelando toda la operación.

CREATE PROCEDURE INSERTADETALLE@IDPEDIDO INT,@IDPRODUCTO INT,@CANT INT,@PRECIO MONEYASDECLARE @ERROR INTSET NOCOUNT ON

IF NOT EXISTS(SELECT * FROM PEDIDOS WHERE IDPEDIDO=@IDPEDIDO)PRINT 'NO EXISTE EL NUMERO DE PEDIDO PARA EL DETALLE DE PEDIDO ESPECIFICADO'

ELSEBEGINBEGIN TRANSACTION

INSERT INTO [DETALLES DE PEDIDOS](IDPEDIDO,IDPRODUCTO,PRECIOUNIDAD,CANTIDAD)VALUES(@IDPEDIDO,@IDPRODUCTO,@PRECIO,@CANT)PRINT 'SE HA INSERTADO EL DETALLE DE PEDIDO'SET @ERROR=@@ERRORIF (@ERROR<>0) GOTO TRATARERRORUPDATE PRODUCTOS SET UNIDADESENEXISTENCIA=UNIDADESENEXISTENCIA-@CANTPRINT 'SE HA DESCARGADO EL STOCK'

SET @ERROR=@@ERRORIF (@ERROR<>0) GOTO TRATARERROR

COMMIT TRANSACTIONTRATARERROR:IF (@@ERROR<>0)BEGIN

PRINT 'NO ES POSIBLE LA ACTUALIZACION'ROLLBACK TRANSACTION

ENDEND

GOINSERTADETALLE 20572,2,10,3







3. TRIGGERS y DESENCADENADORES

3.1 CREACION DE TRIGGERS

Los disparadores de procedimiento, más comúnmente conocidos como triggers, secomportan como un procedimiento almacenado, a diferencia que se ejecutan cuandoocurre un evento sobre alguna tabla.

Entendemos por evento, cualquier acción del tipo:

• INSERCIÓN• BORRADO• ACTUALIZACIÓN

La sintaxis de la sentencia de creación de triggers es la siguiente:

CREATE TRIGGER nombre ON tabla FOR accion AS codigo

Donde acción especifica el evento que debe ocurrir para que se dispare el trigger, y quepuede ser:

• UPDATE: actualización• INSERT: inserción• DELETE: borrado

Por ejemplo, si queremos crear un trigger llamado modificación_Cliente sobre la tablaCliente que muestre un mensaje cada vez que se actualiza una tupla de la tabla,deberemos escribir el Código fuente

CREATE TRIGGER modificacion_cliente ON Cliente FOR UPDATE ASprint 'Han actualizado la tabla Clientes'

Para comprobar el funcionamiento de este trigger, podemos actualizar cualquier fila de la

tabla de clientes, por ejemplo con la sentencia que aparece en el Código fuente

UPDATE clientes SET nombre = 'Antonio Moreno' WHERE idcliente = 'anton'

Con esto conseguimos dos cosas, actualizar el nombre del cliente cuyo código es elespecificado a Miguel, y obtener el mensaje que se muestra como ejecución del trigger deactualización







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 daría lugar a un bucleinfinito

2. Por esta razón, un trigger no puede ejecutar instrucciones DDL (lenguaje dedefinición de datos

3. No se pueden ejecutar sentencias como SELECT INTO o de creación dedispositivos dentro de un trigger

3.2 ELIMINACION DE TRIGGERS

Para borrar un trigger, deberemos ejecutar la sentencia DROP TRIGGER

Por ejemplo, si queremos borrar el trigger anteriormente creado, ejecutaremos el Códigofuente

DROP TRIGGER modificacion_cliente

3.3 MODIFICACION DE TRIGGERS

Para modificar un trigger, deberemos ejecutar la sentencia ALTER TRIGGER

Por ejemplo, si queremos modificar el trigger anteriormente creado, ejecutaremos elCódigo fuente

ALTER TRIGGER modificacion_cliente ON Cliente FOR UPDATE ASPrint 'los registros de la tabla Clientes han sido actualizados'Como ya se ha comentado, los triggers o desencadenadores son una especie deprocedimientos almacenados, que se ejecutan cuando ocurre una acción dentro de la basede datos. Así, si por ejemplo se ejecuta una inserción, una actualización, o un borrado deuna tabla, se ejecutarían las sentencias definidas para el trigger en concreto de esa tablaespecífica.







Sintaxis

CREATE TRIGGER nombreON tablaFOR [DELETE | INSERT | UPDATE

ASSentencias

Las palabras reservadas DELETE, INSERT y UPDATE corresponden a cada una de lasacciones para las cuales se puede definir un desencadenador dentro de la tablaespecificada

El bloque de sentencias permite prácticamente 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 ejecutapara cambiar o comprobar los datos que se van a insertar, actualizar o borrar.

3.4 LAS TABLAS DELETED E INSERTED

Dentro de la definición de un trigger, podemos hacer referencia a un par de tablas lógicas,cuya estructura es similar a la tabla donde se esta ejecutando el trigger; es decir, es unacopia de la tabla en la cual se van a insertar o borrar los datos, y que contiene,precisamente, los datos que van a ser añadidos o borrados.

La utilidad de estas dos tablas es la de realizar comprobaciones entre los datos antiguos ylos nuevos. Así, por ejemplo, si queremos recuperar los datos de la tabla que estamos

borrando, dentro del trigger, se deberá ejecutar el siguiente código

SELECT FROM DELET

3.5 TIPOS DE DESENCADENADORES

SQL-Server permite la definición de varios tipos de triggers, entre los cuales cabe destacarlos siguientes:

• DESENCADENADORES MÚLTIPLES Para una misma tabla, se pueden definir distintos triggers para la misma acción, esdecir, si definimos un trigger para insert, y resulta que dicha tabla ya tenía definidoun trigger para esa misma acción, se ejecutarán ambos triggers cuando ocurradicho evento sobre la tabla.

• DESENCADENADORES RECURSIVOS Se permite la recursividad entre las llamadas a los triggers, es decir, un triggerpuede 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, seprovoca la llamada de este último que, si a su vez vuelve a modificar otra tabla,puede provocar la ejecución de otro trigger, y así sucesivamente. Si se supera elnivel de anidamiento permitido, se cancelará la ejecución de los triggers.

3.6 RESOLUCIÓN DIFERIDA DE NOMBRES

La resolución diferida de nombres es una utilidad que permite escribir triggers que haganreferencia a tablas que no existen en el momento de la compilación. Por ejemplo, elsiguiente código hace referencia a una tabla ABC, que no existe en el momento de escribirel trigger.

CREATE TRIGGER TRIGGER1ON AUTHORSFOR INSERT, UPDATE, DELETE

ASSELECT A.NOMBRE, A.APELLIDOSFROM CLIENTES A INNER JOIN ABC ON A.IDCLIENTE = X.IDCLIENTE

3.7 HABILITACION / DESHABILITACION DE TRIGGERS

A) Para DESHABILITAR un trigger se hace de la siguiente manera

ALTER TABLE DETALLESDISABLE TRIGGER ABC

B) Para HABILITAR un trigger se hace de la siguiente manera

ALTER TABLE DETALLESENABLE TRIGGER ABC

3.8 LIMITACIONES DE LOS TRIGGERS

Aunque ya se han comentado algunas de las limitaciones a la hora de programar triggers,veamos en detalle las restricciones que implica la definición de triggers

• Un trigger sólo 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 acción del desencadenador puede utilizarse para definir más de un

trigger sobre la misma tabla







• La opción SET elegida dentro de la ejecución de un desencadenador, volverá a suestado previamente definido una vez concluya la ejecución del mismo

• Así mismo, no se permite la utilización de las sentencias del DDL dentro de ladefinición de un trigger En una vista no se puede utilizar un desencadenador







EJEMPLOS DE TRIGGERS

1. El siguiente código, muestra la forma de enviar un mensaje de correo electrónicoa una persona, cuando se añade una nueva oferta

CREATE TRIGGER ENVIAR_CORREOON OFERTAFOR INSERT

ASEXEC MASTER..XP_SENDMAIL 'PEPE','Tenemos una nueva oferta en nuestra basede datos'GO

2. En el ejemplo anterior, si adicionalmente queremos que se envíe el mensaje cadavez que se cambia algo en la tabla de ofertas, deberemos reescribir el trigger paralas acciones update y delete, como se muestra en el siguiente código

DROP TRIGGER ENVIAR_CORREOGOCREATE TRIGGER ENVIAR_CORREOON OFERTAFOR INSERT, UPDATE, DELETE

ASEXEC MASTER..XP_SENDMAIL 'PEPE','LA TABLA DE OFERTAS HA CAMBIADO'GO

CREATE TRIGGER TR_ACTUALIZASTOCKON [DETALLES DE PEDIDOS

AS

BEGINDECLARE @IDPROD INT DECLARE @CANTIDAD INT DECLARE @STOCK INT

SET @IDPROD=(SELECT IDPRODUCTO FROM INSERTED SET @CANTIDAD=(SELECT CANTIDAD FROM INSERTED SET @STOCK= (SELECT STOCK FROM PRODUCTOS WHERE IDPRODUCTO=@IDPROD

IF @STOCK>@CANTIDADBEGIN

UPDATE PRODUCTOS SET STOCK=STOCK-@CANTIDAD

WHERE IDPRODUCTO=@IDPRODPRINT 'SE DESCARGO EL STOCK'

ENDELSE

BEGINROLLBACKPRINT 'LA CANTIDAD DEBE SER MENOR AL STOCK'

ENDEND





3. Crear un trigger que no permi ta registrar un detalle de pedido con una cabecerade pedidos ( impedido que no exista en la tabla pedidos

CREATE TRIGGER VALIDADETALLE

ON DETALLESFOR INSERT AS BEGINDECLARE @IDPEDIDO INT SET @IDPEDIDO=ISNULL((SELECT IDPEDIDO

FROM PEDIDOSWHERE IDPEDIDO=(SELECT IDPEDIDO FROM INSERTED)),0

IF @IDPEDIDO=0BEGIN

PRINT 'NO SE PUEDE INSERTAR UN PEDIDO' ROLLBACK

ENDEND

ths decimo segunda sesion (1).pdf

Documents