Ejercicios Triggers

UNIVERSIDAD FRANCISCO GAVIDIA
FACULTAD DE INGENIERIA Y ARQUITECTURA CICLO I – 2013 PRACTICA Asignatura: Administración de Base de Datos Profesor: Ing. Luis Enrique Reyes Horario: Sábado 6:20 – 9:40 AM Fecha desarrollo: 02 / 03 / 2013 Grupo: 01 Aula: E-01

PRACTICA: CREACIÓN DE TRIGGERS DE BASE DE DATOS

OBJETIVO:
El objetivo de la práctica es conocer el uso de TRIGGER de base de datos,para controlar el flujo y actualización de información en la base de datos. Para ello, será necesario acceder al servidor, ingresando a la base de datos NORTHWIND y utilizando la opción del menú denominado SQL, en la cual al seleccionarlo, le mostrará una ventana del editor SQL para que ingrese las instrucciones que se le indicarán y podrá verificar el resultado al presionar el botón “Continuar”. Siexiste algún error en la instrucción, se le mostrará una pantalla color amarillo en el cual le indicará el tipo de error existe, para lo cual deberá corregir el query respectivo y volver a ejecutarlo hasta obtener los resultados.

DESARROLLO
SINTAXIS CREACIÓN DE TRIGGER DE BASE DE DATOS CREATE TRIGGER nombre_TRIGGER momento_TRIGGER evento_TRIGGER ON nombre_tabla FOR EACH ROW sentencia_TRIGGERmomento_TRIGGER: Puede ser BEFORE (antes) o AFTER (después) evento_TRIGGER : Puede ser INSERT, UPDATE, o DELETE. sentencia_TRIGGER: Grupo de instrucciones SQL delimitadas por un BEGÍN y un END, por un SET Las columnas de la tabla asociada con el disparador pueden referenciarse empleando los alias OLD y NEW, para identificar los datos antes o después de una acción (delete, insert o update)EJERCICIO 1. Creación de una nueva tabla en la que será necesario registrar datos que proceden de otra tabla

DROP TABLE IF EXISTS t1; CREATE TABLE IF employeeId titleOfCourtesy subAlterno Jefe ) NOT EXISTS t1 ( int(11) varchar(25) varchar(32) varchar(32) NOT NULL default '0', default NULL, NOT NULL default '', default NULL

Esta tabla será utilizada en lo subsiguientes ejercicios, en el cualprobaremos los triggers para la manipulación de los datos.

EJERCICIO 2. Adicionar datos a la tabla t1 basados en la selección de otra tabla. Por medio de un select se pueden obtener datos de una tabla, el query puede ser complejo y estoy pueden ser incorporados en una tabla en lugar de mostrarlos en pantalla. INSERT INTO t1 SELECT a.employeeId, a.titleOfCourtesy, concat( a.lastName, ', ',a.firstName ) AS subAlterno, concat( j.lastName, ', ', j.firstName ) AS Jefe FROM employees a LEFT JOIN employees j ON a.reportsto = j.employeeid

EJERCICIO 3. Visualizar resultado de la nueva tabla nombrada t1 SELECT * FROM t1 ORDER BY 1 El resultado sería el mismo si ejecutamos el siguiente código: SELECT a.employeeId, a.titleOfCourtesy, concat( a.lastName, ', ', a.firstName ) AS subAlterno,concat( j.lastName, ', ', j.firstName ) AS Jefe FROM employees a LEFT JOIN employees j ON a.reportsto = j.employeeid

EJERCICIO 4. Otro método para crear los mismos resultados mostrados en los ejercicios anteriores. Se creará la tabla t2 que almacenará los subalternos y sus respectivos jefes CREATE TABLE t2 as SELECT a.employeeId, a.titleOfCourtesy,

concat( a.lastName, ', ', a.firstName ) ASsubAlterno, concat( j.lastName, ', ', j.firstName ) AS Jefe FROM employees a LEFT JOIN employees j ON a.reportsto = j.employeeid

EJERCICIO 5. Creación de una tabla t3, que sus datos dependerán del resultado de un query de mas de una tabla. Su objetivo es para migraciones posteriores. CREATE TABLE t3 as select o.orderid, p.ProductName, (od.unitprice * od.quantity) as Venta, concat(od.discount *100,'%') as Descto, (od.unitprice * od.quantity) * (1 - od.discount) as Neto from order_details od, orders o, products p where od.orderid = o.orderid and od.productId = p.productId

EJERCICIO 6. Creación de un trigger que al agregarlo en la tabla t1, automáticamente se agregue a la tabla t2. CREATE TRIGGER t1_insert AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 values ( NEW. employeeId, NEW....