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.
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
• 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.
