7. Vistas, Procedimientos y Trigger

7.  Vistas, Procedimientos y Triggers

VISTAS
Una vista es una consulta, que refleja el contenido de una o más tablas, desde la que se puede acceder a los datos como si fuera una tabla.

Dos son las principales razones por las que podemos crear vistas.


•     Seguridad, nos pueden interesar que los usuarios tengan acceso a una parte de la información que hay en una tabla, pero no a toda la tabla.
•     Comodidad, como hemos dicho el modelo relacional no es el más comodo para  visualizar  los datos, lo que nos puede llevar a tener que escribir complejas sentencias SQL, tener una vista nos simplifica esta tarea.

Las vistas no tienen una copia física de los datos, son consultas a los datos que hay en las  tablas, por lo que si actualizamos los datos de una vista, estamos actualizando realmente la  tabla, y si actualizamos la tabla estos cambios serán visibles desde la vista.

Nota: No siempre podremos actualizar los datos de una vista, dependerá de la complejidad de la misma (dependerá de si el conjunto de resultados tiene acceso a la clave principal de la tabla o no), y del gestor de base de datos. No todos los gestores de bases de datos permiten actualizar vistas, ORACLE, por ejemplo, no lo permite, mientras que SQL Server sí.

7.1 Creación de vistas.

Para    crear    una    vista    debemos    utilizar    la    sentencia    CREATE    VIEW, debiendo proporcionar un nombre a la vista y una sentencia SQL SELECT válida.

CREATE VIEW <nombre_vista>

AS

(<sentencia_select>);
 Ejemplo: Crear una vista sobre nuestra tabla alquileres, en la que se nos muestre el nombre y apellidos del cliente en lugar de su código.



CREATE VIEW    vAlquileres

AS

(

SELECT    nombre, apellidos,
matricula

FROM    tAlquileres, tClientes
WHERE    (    tAlquileres.codigo_cliente    =    tClientes.codigo    )

)



Si queremos, modificar la definición de nuestra vista podemos utilizar la sentencia ALTER VIEW, de forma muy parecida a como lo haciamos con las tablas. En este caso queremos añadir los campos fx_alquiler y fx_devolucion a la vista.

ALTER    VIEW    vAlquileres

AS

(
SELECT    nombre, apellidos,
matricula, fx_alquiler, fx_devolucion
FROM    tAlquileres, tClientes
WHERE    (    tAlquileres.codigo_cliente    =    tClientes.codigo    )
)

Por último podemos eliminar la vista a través de la sentencia DROP VIEW. Para eliminar la vista que hemos creado anteriormente se uitlizaría:

DROP VIEW vAlquileres;

Una vista se consulta como si fuera una tabla. Ejemplo:

USE NORTHWIND GO
--CREACION DE VISTA PROVEEDORES Y SUS PEDIDOS

CREATE VIEW VIEW_SUPPLIER_PRODUCS

AS

SELECT S.SUPPLIERID,S.COMPANYNAME,S.CONTACTNAME

,P.PRODUCTID,P.PRODUCTNAME, P.UNITPRICE FROM SUPPLIERS AS S INNER JOIN PRODUCTS AS P ON
S.SUPPLIERID=P.SUPPLIERID GO
--CREACION DE UNA VISTA CON INSTRUCCION GROUP

CREATE VIEW VIEW_SUBTOTALES(CODIGO_ORDEN,SUB_TOTAL)

AS

SELECT OD.ORDERID,SUM(CONVERT(MONEY,(OD.UNITPRICE* QUANTITY*(1-DISCOUNT)/100))*100)
FROM [ORDER DETAILS] OD GROUP BY OD.ORDERID
GO

--LLAMAR A LA CONSULTA ANTERIOR SELECT * FROM VIEW_SUBTOTALES
--VER VISTAS

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='VIEW'
--BORRAR UNA VISTA

DROP VIEW VIEW_SUBTOTALES

--ALTERAR UNA VISTA

ALTER VIEW  VIEW_SUPPLIER_PRODUCS AS

7.2 PROCEDIMIENTOS

Que son procedimientos Almacenados: Es un conjunto de código puro del lenguaje que utilizamos dentro de las bases de datos como por ejemplo: create table, insert into, Select from. Que se almacena físicamente en la base de datos y de la misma forma se exportan cuando creamos una copia de seguridad de nuestra base de datos.

Como funcionan: Los procedimientos almacenados se basan en una estructura definida por SQL SERVER.
Iniciamos con:

•     CREATE PROCEDURE nombre_del_procedimiento @variables tipo(longitud) }
•     AS
•     BEGIN
•     Todo el codigo puro de base de datos
•     END
Como probamos: Esto es lo más fácil de realizar solo necesitamos lo siguiente: Escribimos EXEC nombre_del_procedimiento ' variables',12,' variables'

Nota: Cabe destacar que no necesariamente se deben definir variables en el caso de un Select * From solo pondremos EXEC y nombre del procedimiento

Vamos a realizar un ejemplo

•     Creemos una base de datos con el nombre que decidamos
•     Creemos la siguiente tabla.

 Ahora definimos el procedimiento almacenado para insertar un nuevo registro(Recordemos que todo lo que está dentro del Begin puede ser cualquier sentencia de código puro de SQL SERVER: Insert into, Delete From, Update set, etc) .

 •     Ejecutamos el cogido y ahora probamos con los siguientes datos:

Codigo: 001 ; Nombre: Patricia ; Apellido: Colimba ; Edad: 21; Universidad: UTN

•     Ejecutamos el procedimiento almacenado con los datos anteriores tomando en consideración que: los campos tipo int se envían sin comilla el resto dentro de comilla.
•     Para ejecutar el procedimiento almacenado escribimos EXEC + el nombre del procedimiento + las variables si fuera el caso y clic en ejecutar

 •     Ahora revisamos en nuestra tabla y tenemos lo siguiente:

•     Como podemos ver funciona sin ningún problema
Nota: Todas las sentencias de SQL SERVER se pueden enviar dentro de un procedimiento almacenado, inclusive inner joins, busquedas &like entre otras.


7.3 TRIGGERS
Un    trigger(    o    desencadenador)    es    una    clase    especial    de    procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento en el servidor de bases de datos.


La estructura de un trigger es:



Create Trigger pr_usuarios_Trigger1

On dbo.pr_usuarios
For /* Insert, Update, Delete */ As



Los triggers pueden incluir cualquier número y clase de instrucción de Transact- SQL.
Desde el Trigger podremos obtener los datos de la fila que se ha modificado o añadido utilizando inserted o deleted:


Select * from deleted



Limitaciones de los triggers.

- Solo se pueden aplicar a una tabla específica, es decir, un trigger no sirve para dos o más tablas
- El trigger se crea en la base de datos que de trabajo pero desde un trigger puedes hacer referencia a otras bases de datos.
- Un Trigger devuelve resultados al programa que lo desencadena de la misma forma que un Stored Procedure aunque no es lo mas idoneo, para impedir que una instrucción de asignación devuelva un resultado se puede utilizar la sentencia SET NOCOUNT al principio del Trigger.
- Las siguientes instrucciones no se pueden utilizar en los triggers :



ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE
DROP DATABASE LOAD DATABASE


LOAD LOG RECONFIGURE

RESTORE DATABASE RESTORE LOG



SQL Server proporciona los siguientes tipos de triggers:



Trigger DML, se ejecutan cuando un usuario intenta modificar datos mediante un evento  de  lenguaje  de  manipulación  de  datos  (DML).  Los  eventos  DML  son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.
Trigger DDL, se ejecutan en respuesta a una variedad de eventos de lenguaje de definición    de   datos   (DDL).    Estos    eventos    corresponden  principalmente    a instrucciones  CREATE,  ALTER  y  DROP  de  Transact-SQL,  y  a  determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL. Trigger DML, se ejecutan cuando un usuario intenta modificar datos mediante un evento  de  lenguaje  de  manipulación  de  datos  (DML).  Los  eventos  DML  son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.


Ejemplo 1:

Como ejemplo crearemos un Trigger que avise al webmaster con un mail cuando un usuario se  da de alta en nuestro web, para crear el trigger solo se han de seguir los pasos arriba indicados, como muestran las imágenes y el código es el siguiente, haciendo Copy & Paste funciona.


El funcionamiento del trigger es muy sencillo, declaramos dos variables, una para el mensaje que se enviará en el mail y otra para obtener el ID del registro recién insertado  y  luego  este  ID  lo  concatenamos  al  mensaje  para  enviárselo  al webmaster.


Alter Trigger Trigger_Aviso_al_Webmaster

On dbo.pr_usuarios

For Insert

As



– Declaramos las variables del mensaje y del ID del nuevo usuario

Declare @Mensaje varchar(200)

Declare @ID numeric



– Obtenemos el id del usuario recien insertado

Select @ID = (Select IDUsuario From Inserted )

Select @Mensaje = „Nuevo Usuarios en el web : „ + Convert(varchar(10), @ID)



Exec master.dbo.xp_sendmail

@recipients = „webmaster@dominio.com‟,

@subject = „Nuevo usuario‟,

@message = @Mensaje



EJEMPLO 2:

Para indicar los triggers en esta base hacemos lo siguiente:

Podemos hacer dentro de nuestra base o creamos una Nueva Consulta, donde llamamos a nuestra base:
use Biblioteca

y comenzamos realizando nuestros triggers asi:

--Trigger que inserta una fila historico cuando se borra un prestamo

/*4.  Siempre  que  se  borra  una  fila  de  la  tabla  PRESTAMO,  se  inserta  en HISTORICO la fila con los datos correspondientes al prestamo que se borra*/ create trigger tr_delete_prestamo2
on PRESTAMO

after delete as declare

@a char(3),

@b smallint,

@c datetime,

@d smallint,

@e int,

@f datetime select
@a=DELETED.isbn,

@b=DELETED.copiaNro,

@c=DELETED.fechaPrestamo,

@d=DELETED.tituloNro,

@e=DELETED.usuarioNro,

@f=DELETED.fechaDebeDevolver from DELETED
insert into HISTORICO values(@a,@b,@c,@d,@e,@f,null,null,null)

Para ver el resultado tenemos que insertar a la tabla histórico lo siguiente:

insert into HISTORICO values(@a,@b,@c,@d,@e,@f,null,null,null) Luego llamamos a la tabla histórico y prestamo
select * from historico select * from prestamo
Ejecutamos el triggers.