Laboratorio 13
Presentado por: Jhon Barcasnegras A la profesora: Claudia Casas
Análisis y desarrollo de sistemas de información Servicio nacional de aprendizaje 2015
Laboratorio 13 1. Procedimientos almacenados para inserción de registros Un procedimiento almacenado es similar a una función pero no retorna un valor. Reciben sui nombre porque las instrucciones quedan almacenadas en el servidor. Su función es facilitar la ejecución de una tarea en la base de datos ya que ahorra ejecutar toda la sintaxis habitualmente necesaria para ello. Vamos a crear procedimientos almacenados para inserción de datos. Para ello utilizaremos la base de datos laboratorio.sql que desarrollamos en el laboratorio 12. Para esto hemos aprovechado la base de datos creada en MySQL y además la hemos creado nuevamente en Oracle. Los primeros ejercicios serán ejecutados en MySQL y aplican para las tablas profesor, curso, estudiante y estudiantexcurso. 1.1 Tabla profesor La tabla profesor tiene esta estructura: mysql> describe profesor; +---------------+---------------+-------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------+------+-----+---------+-------+ | doc_profesor | varchar(20) | NO | PRI | NULL | | | nom_profesor | varchar(30) | NO | | NULL | | | ape_profesor | varchar(30) | NO | | NULL | | | cate_prof | int(11) | YES | | NULL | | | sal_prof | int(11) | YES | | NULL | | +----------------+---------------+-------+-----+---------+-------+ Tiene almacenados los siguientes datos: mysql> select * from profesor; +-----------------+------------------+----------------+-----------+----------+ | doc_profesor | nom_profesor | ape_profesor | cate_prof | sal_prof | +-----------------+------------------+----------------+-----------+----------+ | 1.098.765.789 | Alejandra | Torres | 4 | 1100000 | | 13.826.789 | Maritza | Angarita | 1 | 550000 | | 63.502.720 | Martha | Rojas | 2 | 690000 | | 91.216.904 | Carlos | Pérez | 3 | 950000 | +-----------------+-----------------+----------------+------------+----------+ Primero creamos un delimitador, este puede ser cualquier símbolo aunque los más habituales son // y $$. Dentro se establecen los parámetros y el procedimiento a ejecutar. Para mayor comodidad, en esta ocasión no he utilizado la terminal de MySQL sino la aplicación gráfica MySQL Workbench ya que me permite editar una línea si cometo
algún error y luego ejecutar todo el bloque de instrucciones.
**Procedimiento almacenado para la tabla ins_profesor (nota: dice tabla profesor porque no tengo captura de la corrección. Cambié el nombre para que no se llamara igual que la tabla). Ahora hay que ponerlo a prueba llamando al método e introduciendo información válida de acuerdo a los parámetros:
Verificamos que se hayan insertado los datos correctamente: mysql> select * from profesor; +-----------------+------------------+--------------+-------------+----------+ | doc_profesor | nom_profesor | ape_profesor | cate_prof | sal_prof | +-----------------+------------------+----------------+-----------+----------+ | 1.098.765.789 | Alejandra | Torres | 4 | 1100000 | | 13.826.789 | Maritza | Angarita | 1 | 550000 | | 63.502.720 | Martha | Rojas | 2 | 690000 | | 73542983 | Rafael | Conde | 3 | 950000 | | 91.216.904 | Carlos | Pérez | 3 | 950000 | +-----------------+------------------+----------------+-----------+----------+
1.2 Tabla curso
mysql> select * from curso; +------------+-------------------------------------+-------------+-----------+ | cod_curso | nom_curs | horas_cur | valor_cur | +------------+--------------------------------------+-------------+-----------+ | 149842 | Fundamentos de bases de datos | 40 | 500000 | | 172943 | Procedmientos almacenados | 20 | 500000 | | 250067 | Fundamentos de SQL | 20 | 700000 | | 289011 | Manejo de MySQL | 45 | 550000 | | 345671 | Fundamentos de Oracle | 60 | 3000000 | +------------+--------------------------------------+------------+-----------+
1.3 Tabla estudiantes
mysql> select* from estudiante; +-----------------+--------------+-----------------+----------+ | doc_est | nom_est | ape_est | edad_est | +-----------------+--------------+-----------------+----------+ | 1.098.098.097 | Jonatan | Ardila | 17 | | 1.098.765.678 | Carlos | Martinez | 19 | | 63.502.720 | María | Perez | 23 | | 72240535 | Jhon | Barcasnegras | 37 | | 91.245.678 | Carlos Jose | Lopez | 25 | +-----------------+--------------+------------------+----------+
1.4 Tabla estudiantexcurso
mysql> select cod_curso, doc_est, nom_est, fec_ini_estcur -> from curso join estudiante join estudiantexcurso -> on doc_est = doc_est_estcur and cod_curso = cod_curso_estcur; +------------+-----------------+-----------+-----------------+ | cod_curso | doc_est | nom_est | fec_ini_estcur | +------------+-----------------+-----------+-----------------+ | 289011 | 1.098.765.678 | Carlos | 2011-01-02 | | 250067 | 63.502.720 | María | 2011-01-03 | | 289011 | 1.098.098.097 | Jonatan | 2011-01-02 | | 345671 | 63.502.720 | María | 2011-01-04 | | 172943 | 72240535 | Jhon | 2014-02-06 | +------------+------------------+----------+------------------+
1.5 Tabla artículo: Las tablas cliente, artículo y pedido la realizaremos usando la interfaz web de Oracle.
Ejecutamos en terminal el procedure: SQL> execute ins_articulo('laboratorio 13', 'Jhon Barcasnegras', 'AlphaomegaRama', 22000); PL/SQL procedure successfully completed.
1.6 Tabla cliente
La ejecución del procedimiento desde una IDE es algo distinta, no se puede usar execute:
1.7 Tabla pedido
1.8 Tabla compañía
mysql> select *from compañia; +----------------+-------------------------+---------------+-----------------------------+ | comnit | comnombre | comañofun | comreplegal | +----------------+-------------------------+---------------+-----------------------------+ | 800890890-2 | Seguros Atlantida | 1998 | Carlos López | | 899999999-1 | Aseguradora Rojas | 1991 | Luis Fernando Rojas | | 899999999-5 | Seguros del Estadio | 2001 | Maria Margarita Pérez | | 899999999-7 | Seguros del caribe | 2005 | Luis Arrieta | +----------------+-------------------------+---------------+-----------------------------+ 1.9 Tabla tiposautomotores
mysql> select *from tiposautomotores; +------.----+----------------+ | autotipo | autnombre | +-----------+----------------+ | 1 | Automóviles | | 2 | Camperos | | 3 | Camiones | | 4 | Autobuses | +----------+-----------------+ 4 rows in set (0.00 sec) 1.10 Tabla automotores
mysql> select *from automotores; +-----------+-----------------+----------+------------+------------------+---------------+-------------------+ | autoplaca | automarca | autotipo | automodelo | autonumpasajeros | autocilindraje|autonumchasis | +-----------+-----------------+----------+------------+------------------+---------------+-------------------+ | DKZ820 | Reanult stepway | 1| 2008 | 5| 1600 | wywzzz157kk009d45 | | FDT650 | Mercedes | 4| 2010 | 45 | 12300 | wywzzz127kk009f95 | | FLL420 | Chevrolet corsa | 1| 2003 | 5| 1400 | wywzzz167kk009d25 | | KJQ920 | Kia sportage | 2| 2009 | 7| 2000 | wywzzz157kk009d25 | +-----------+-----------------+----------+------------+------------------+---------------+-------------------+
1.11 Tabla aseguramientos
Llamamos al procedimiento: call ins_asegura('2013-08-12', '2014-08-12', 60000000, 'Vigente', 'FDT650', 1200000); comprobamos que se inserten los datos correctamente. mysql> select *from aseguramientos; +-----------+----------------+--------------------+-------------------+-----------+---------+----------+ | asecodigo | asefechainicio | asefechaexpiracion | asevalorasegurado | aseestado | aseplaca | asecosto | +-----------+----------------+--------------------+-------------------+----------+-------------------+----------+ | 1 | 2012-09-30 | 2013-09-30 | 30000000 | Vigente | FLL420 | 500000 | | 2 | 2012-09-27 | 2013-09-27 | 35000000 | Vigente | DKZ820 | 600000 | | 3 | 2011-09-28 | 2012-09-28 | 50000000 | Vencido | KJQ920
| 800000 | | 4 | 2013-08-12 | 2014-08-12 | 60000000 | Vigente | FDT650 | 1200000 | +-----------+----------------+--------------------+-------------------+----------+------------------+----------+ 1.12 Tabla incidentes
call ins_incidente('2013-09-15', 'FDT650', 'Barranquilla', 1, 0, 2); mysql> select *from incidentes; +------------+---------------+-----------+--------------+-----------------+--------------------+--------------------------+ | incicodigo | incifecha | inciplaca | incilugar | incicantheridos | incicantfatalidades | incicanautosinvolucrados | +------------+---------------+-----------+--------------+-----------------+---------------------
+--------------------------+ | 1 | 2012-09-30 | DKZ820 | Bucaramanga | 2| | 2 | 2012-09-27 | FLL420 | Girón | 1| | 3 | 2011-09-28 | FLL420 | Bucaramanga | 2| | 4 | 2013-09-15 | FDT650 | Barranquilla | 2|
0|
0|
1|
0|
1|
0|
1|
0|
2 Procedimientos almacenados para procesos en Oracle 2.1 Proceso ordenar salarios: Este ejercicio solicita presentar los salarios de los profesores organizados por categorías. Oracle no me permite crear un procedimiento que simplemente lleve el select en su interior, sino que al investigar veo que debo crear un cursor. El código del cursor en el procedimiento sería así:
Ejecutamos el procedimiento y obtenemos el resultado en pantalla:
2.2 Proceso cursos: Mostrar los cursos cuyo valor sea mayor a $500000:
2.3 Proceso clientes: Muestra los datos de los clientes que realizaron pedidos el día 25 de febrero del 2012.
2.4 Mostrar todos los pedidos: create or replace procedure "LISTA_PEDIDOS" is cursor curs is select tit_art, can_art_artped, val_ven_art_artped from articuloxpedido join articulo on id_art = id_art_artped; begin for resultado in curs loop dbms_output.put_line ('Artículo: '|| resultado.tit_art ||' Cantidad: '|| resultado.can_art_artped ||' Valor: '|| resultado.val_ven_art_artped); end loop; end; Muestra el resultado: Artículo: Artículo: Artículo: Artículo: Artículo: Artículo:
Facebook y Twitter para adultos Cantidad: 10 Valor: 55000 Creación de un portal con PHP y MySQL Cantidad: 12 Valor: 45000 Creación de un portal con PHP y MySQL Cantidad: 5 Valor: 40000 Administración de sistemas operativos Cantidad: 12 Valor: 55000 Redes Cisco Cantidad: 20 Valor: 65000 Redes Cisco Cantidad: 5 Valor: 65000
Statement processed.
2.5 Mostrar vehículos: Mostrar vehículos cuya póliza vence el 30 de septiembre de 2013 (El ejercicio decía octubre pero no hay datos con esa fecha). create or replace procedure "vsept2013" is cursor curs is select autoplaca, asefechaexpiracion, automarca from automotores join aseguramientos on autoplaca = aseplaca and asefechaexpiracion = '09-30-2013'; begin for resultado in curs loop dbms_output.put_line ('Placa: '|| resultado.autoplaca ||' Expiración: '|| resultado.asefechaexpiracion ||' Marca: '|| resultado.automarca);
end loop; end; Muestra el resultado: Placa: FLL420 Expiración: 09/30/2013 Marca: Chevrolet corsa
Statement processed. 3 Procedimientos almacenados para procesos con MySQL En MySQL los procedimientos con select no son distintos de los insert y requieren mucho menos trabajo que con Oracle. 3.1 Proceso mostrar cursos Vamos a mostrar todos los cursos ordenados por valor. USE `laboratoriosql`; DROP procedure IF EXISTS `mostrar_cursos`; DELIMITER $$ USE `laboratoriosql`$$ CREATE PROCEDURE mostrar_cursos () BEGIN select *from curso order by valor_cur; END$$ DELIMITER ; Llamamos el procedimiento y obtenemos los datos que queremos. Mysql> call mostrar_cursos; +-----------+-------------------------------------+-----------+-----------+ | cod_curso | nom_curs
| horas_cur | valor_cur |
+-----------+--------------------------------------+-----------+-----------+ |
149842 | Fundamentos de bases de datos |
40 |
500000 |
|
172943 | Procedimientos almacenados
|
20 |
500000 |
|
289011 | Manejo de MySQL
|
45 |
550000 |
|
250067 | Fundamentos de SQL
|
20 |
700000 |
|
345671 | Fundamentos de Oracle
|
60 | 3000000 |
3.2 Mostrar pedidos USE `laboratoriosql`; DROP procedure IF EXISTS `mostrar_pedidos`; DELIMITER $$ USE `laboratoriosql`$$ CREATE PROCEDURE mostrar_pedidos () BEGIN select id_pedido, id_art, tit_art, can_art_artped, val_ven_art_artped from pedido join articulo join articuloxpedido on id_pedido = id_ped_artped and id_art_artped = id_art; END$$ DELIMITER ; Llamamos el proceso y obtenemos los valores: mysql> call mostrar_pedidos; +-----------+--------+----------------------------------------+---------------+--------------------+ | id_pedido | id_art | tit_art
| can_art_artped | val_ven_art_artped |
+-----------+--------+----------------------------------------+---------------+--------------------+ | 1|
3 | Creación de un portal con php y mysql |
5|
40000 |
| 1|
4 | Administración de sistemas operativos |
12 |
55000 |
| 2|
1 | Redes cisco
|
5|
65000 |
| 3|
2 | Facebook y twitter para adultos
|
10 |
55000 |
| 3|
3 | Creación de un portal con php y mysql |
12 |
45000 |
| 4|
1 | Redes cisco
20 |
65000 |
|
+-----------+--------+----------------------------------------+---------------+--------------------+
3.3 Empresas fundadas entre 1991 y 1998 USE `laboratoriosql`; DROP procedure IF EXISTS `mostrar_empresas`; DELIMITER $$ USE `laboratoriosql`$$ CREATE PROCEDURE mostrar_empresas() BEGIN select *from compañia where comañofun >= 1991 and comañofun <= 1998; END$$ DELIMITER ; Llamamos el procedimiento: mysql> call mostrar_empresas; +----------------+----------------------+---------------+------------------------+ | comnit
| comnombre
| comañofun | comreplegal
|
+----------------+-----------------------+---------------+------------------------+ | 800890890-2 | Seguros Atlantida |
1998 | Carlos López
|
| 899999999-1 | Aseguradora Rojas |
1991 | Luis Fernando Rojas |
+----------------+------------------------+--------------+--------------------------+ 3.4 Clientes ordenados por apellidos: USE `laboratoriosql`; DROP procedure IF EXISTS `clientesxapellido`; DELIMITER $$ USE `laboratoriosql`$$ CREATE PROCEDURE clientesxapellido () BEGIN select *from cliente order by ape_cli desc; END$$ DELIMITER ;
Llamamos el procedure:
3.5 Incidentes con un herido USE `laboratoriosql`; DROP procedure IF EXISTS `incidentes1herido`; DELIMITER $$ USE `laboratoriosql`$$ CREATE PROCEDURE incidentes1herido () BEGIN select incifecha, inciplaca, asefechainicio, aseestado, asevalorasegurado from incidentes join aseguramientos on inciplaca = aseplaca and incicantheridos = 1; END$$ DELIMITER ; Llamamos el proceso:
3.6 Incidentes del vehículo FLL420 USE `laboratoriosql`; DROP procedure IF EXISTS `incifll420`; DELIMITER $$ USE `laboratoriosql`$$ CREATE PROCEDURE incifll420 () BEGIN select incifecha, inciplaca, asefechainicio, asefechaexpiracion, aseestado, asevalorasegurado from incidentes join aseguramientos
on inciplaca = aseplaca and inciplaca = 'FLL420'; END$$ DELIMITER ; Llamamos el procedimiento:
4. Funciones 4.1 Contar estudiantes mayores de 22 USE `laboratoriosql`; DROP function IF EXISTS `contar22`; DELIMITER $$ USE `laboratoriosql`$$ CREATE FUNCTION contar22 () RETURNS INTEGER BEGIN DECLARE cantidad int; select count(*) into cantidad from estudiante where edad_est > 22; RETURN cantidad; END$$ DELIMITER ; Llamamos la función y visualizamos el resultado: mysql> select contar22(); +------------+ | contar22() | +------------+ | 3 | +------------+
4.2 Mostrar el nombre del más estudiante más joven Primero he creado una función que devuelve como valor la edad del alumno más joven: USE `laboratoriosql`; DROP function IF EXISTS `masJoven`; DELIMITER $$ USE `laboratoriosql`$$ CREATE FUNCTION masJoven () RETURNS int BEGIN DECLARE masJoven int; SELECT edad_est into masJoven from estudiante order by edad_est asc limit 1; RETURN masJoven; END;$$ DELIMITER ; Y luego la he combinado con un select, así: mysql> select nom_est, edad_est from estudiante where edad_est = masJoven(); +---------+------------+ | nom_est | edad_est | +---------+------------+ | Jonatan | 17 | +---------+------------+ 4.3 Valor promedio de los cursos con más de 40 horas USE `laboratoriosql`; DROP function IF EXISTS `promediocursos`; DELIMITER $$ USE `laboratoriosql`$$ CREATE FUNCTION promediocursos () RETURNS INTEGER BEGIN declare var_promedio int; select avg(valor_cur) into var_promedio from curso where horas_cur > 40; RETURN var_promedio; END$$
DELIMITER ; Probamos la función:
4.4 Sueldo promedio de profesores categoría 1 USE `laboratoriosql`; DROP function IF EXISTS `promediosueldo`; DELIMITER $$ USE `laboratoriosql`$$ CREATE FUNCTION promediosueldo () RETURNS INTEGER BEGIN DECLARE avgsueldo int; SELECT AVG(sal_prof) INTO avgsueldo FROM profesor WHERE cate_prof = 1; RETURN avgsueldo; END$$ DELIMITER ; Probamos la función con select: mysql> select promediosueldo(); +---------------------+ | promediosueldo() | +---------------------+ | 550000 | +---------------------+ 1 row in set (0.00 sec)
4.5 Nombre del profesor con el menor sueldo USE `laboratoriosql`; DROP function IF EXISTS `menorSueldo`; DELIMITER $$ USE `laboratoriosql`$$ CREATE FUNCTION menorSueldo () RETURNS VARCHAR(30) BEGIN DECLARE nombre VARCHAR(30); SELECT nom_profesor INTO nombre FROM profesor ORDER BY sal_prof ASC LIMIT 1; RETURN nombre; END$$ DELIMITER ; mysql> select menorsueldo(); +-----------------+ | menorsueldo() | +-----------------+ | Maritza | +-----------------+ 1 row in set (0.00 sec)
5 Funciones con Oracle 5.1 Nombre, fecha y valor del pedido más costoso Para resolver esta función ya que hay que devolver varios valores en una variable, utilice una concatenación. Para conseguir el valor más alto realice una subconsulta. CREATE OR REPLACE FUNCTION fn_valPed RETURN VARCHAR2 IS var_datos VARCHAR2(500); BEGIN SELECT 'Nombre: '|| nom_cli ||' Fecha: '|| fec_ped || ' Valor: '|| val_ped INTO var_datos FROM cliente JOIN pedido ON id_cli = id_cli_ped and val_ped = (SELECT MAX(val_ped) from pedido); RETURN var_datos; END; Ejecutamos la función y obtenemos el valor deseado: select fn_valped from dual;
5.2 Devolver la cantidad de artículos por cada editorial El ejercicio me pide realizarlo con una función pero en los ejercicios anteriores realicé inserciones de datos en la tabla artículos y ahora mi tabla tiene este aspecto:
Para retornar las distintas editoriales con las cantidades de artículos correspondientes tuve que usar no una función sino un procedimiento con un cursor:
create or replace procedure "PA_EDITORIALES" is cursor curs is select edi_art ||' '||count(*) edi_art from articulo group by edi_art; begin for valores in curs loop dbms_output.put_line (valores.edi_art); end loop; end; Probamos el funcionamiento ejecutándolo en la interfaz web: begin pa_editoriales; end; Y nos regresa los valores que buscamos: Alphaomega-Rama 4 Alphaomega-rama 1 Oveja negra 1 Statement processed. 5.3 Datos de la póliza más costosa Esta función es similar a la que solicitaba el valor del pedido más costoso: create or replace FUNCTION fn_valpoliza RETURN VARCHAR2 IS var_datos VARCHAR2(500); BEGIN SELECT 'Código: '|| asecod ||' Fecha expiración: '|| asefechaexpiracion || ' Valor asegurado: '|| asevalorasegurado INTO var_datos FROM aseguramientos WHERE asevalorasegurado = (SELECT MAX(asevalorasegurado) from aseguramie ntos); RETURN var_datos; END;
5.4 Incidentes con el mínimo de autos involucrados CREATE OR REPLACE FUNCTION fn_incidentes RETURN VARCHAR2 IS var_datos VARCHAR2(500); BEGIN SELECT 'Código incidente: '|| incicod ||' Fecha: '|| incifecha || ' Lugar: '|| incilugar || ' Placa: '|| inciplaca|| ' Valor póliza: '|| asevalorasegurado || ' Vehículos implicados: '|| incicanautosinvolucrados INTO var_datos FROM incidentes JOIN aseguramientos ON inciplaca = aseplaca and incicanautosinvolucrados = (SELECT MIN(incicanautosinvolucrados) from incidentes); RETURN var_datos; END; Probamos la función y obtenemos:
5.5 Datos de la póliza de mayor valor create or replace FUNCTION fn_autopolizamasval RETURN VARCHAR2 IS var_datos VARCHAR2(700); BEGIN SELECT 'Placa: '|| autoplaca ||' Marca: '|| automarca || ' Tipo: '|| autotipo ||' Modelo: '|| automodelo ||' Inicio póliza:' || asefechainicio|| 'Expiración: '|| asefechaexpiracion ||' Estado: '|| aseestado || ' Valor: '|| asevalorasegurado INTO var_datos FROM aseguramientos JOIN automotores ON aseplaca = autoplaca AND asevalorasegurado = (SELECT MAX(asevalorasegurado) from aseguramientos); RETURN var_datos; END;
6. Disparadores o Triggers Los disparadores son funciones útiles en auditoría que se programan para ejecutarse automáticamente cuando se efectúa el tipo de acción que los dispara. Esta acción puede ser Update, insert y delete. Pueden ejecutarse antes (Before) o después (After). 6.1 Disparador para profesor en MySQL 6.1.1 Actualizar Creamos una tabla para auditoria llamada “auditoria_profesor” la cual incluirá los viejos y nuevos valores de las columnas afectadas, y el usuario del sistema que realizó los cambios. Para esto último se utiliza la función current_user() que ya viene definida en MySQL, la cual se colocará como valor por defecto para la columna audi_usuario:
Una forma de crear el disparador es ir a la tabla profesor en mysql Workbench y editar la opción triggers:
La aplicación genera el código adecuado: USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.profesor_BEFORE_UPDATE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`profesor_BEFORE_UPDATE` BEFORE UPDATE ON `profesor` FOR EACH ROW BEGIN insert into auditoria_profesor(audi_nombreAnterior, audi_apellidoAnterior, audi_categoriaAnterior, audi_salarioAnterior, audi_nombreNuevo, audi_apellidoNuevo, audi_categoriaNuevo, audi_salarioNuevo, audi_fechaModificacion, audi_usuario, audi_docProfesor, audi_accion) values (old.nom_profesor, old.ape_profesor, old.cate_prof, old.sal_prof, new.nom_profesor, new.ape_profesor, new.cate_prof, new.sal_prof, now(), current_user(), new.doc_profesor, 'Actualización'); END$$ DELIMITER ; Voy a editar la categoría y salario del profesor Rafael Conde quien tiene categoría 3 y pasará a 4 con el mismo sueldo que el otro profesor que ostenta esa categoría. update profesor set cate_prof = 4 where doc_profesor = '73542983'; update profesor set sal_prof = 1100000 where doc_profesor = '73542983';
Y ahora miramos cómo quedó la tabla profesor y la tabla auditoria_profesor (La resolución no es la mejor pero tuve que editar la captura para poder mostrar todos lo contenidos):
Para que current_user() no me de siempre root, he creado un usuario nuevo: CREATE USER 'jhonBarc'@'localhost' IDENTIFIED BY 'jhon12345'; le he dado privilegios de acceso: GRANT ALL PRIVILEGES ON * . * TO 'jhonBarc'@'localhost'; Y luego refrescamos los privilegios: Ahora voy a usar ese nuevo usuario para los ejercicios. Cerrando mi conexión y regresando con mi nuevo usuario y contraseña. Ahora la función current_user() devuelve el valor jhonbarc@localhost. 6.1.2 Eliminando registros Vamos a borrar un registro en la tabla profesor con el nuevo usuario. Pero primero vamos a crear un trigger para ese evento: USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.profesor_AFTER_DELETE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`profesor_AFTER_DELETE` AFTER DELETE ON `profesor` FOR EACH ROW BEGIN INSERT INTO auditoria_profesor(audi_nombreAnterior, audi_apellidoAnterior, audi_categoriaAnterior, audi_salarioAnterior, audi_fechaModificacion, audi_usuario, audi_docProfesor, audi_accion) VALUES(old.nom_profesor, old.ape_profesor, old.cate_prof, old.sal_prof, now(), current_user(), old.doc_profesor, 'Registro eliminado'); END$$ DELIMITER ;
Eliminamos el registro: delete from profesor where doc_profesor = 'Rafael'; Y ahora miramos que ha ocurrido con nuestras tablas profesor y auditoría profesor:
Se ha borrado el registro del profesor Rafael Conde y ahora sí aparece que el evento lo realizó el usuario jhonBarc tal como se ve en la siguiente imagen:
6.2 Disparador para la tabla curso en MySQL 6.2.1 Actualizar Curso CREATE TABLE `laboratoriosql`.`auditoria_curso` ( `idaudi` INT NOT NULL AUTO_INCREMENT, `audi_nombreAnterior` VARCHAR(100) NULL, `audi_horasAnterior` INT NULL, `audi_valorAnterior` INT NULL, `audi_nombreNuevo` VARCHAR(100) NULL, `audi_horasNuevo` INT NULL, `audi_valorNuevo` INT NULL, `audi_fechaModificacion` DATE NULL, `audi_usuario` VARCHAR(45) NULL DEFAULT 'current_user()', `audi_codCurso` INT NULL, `audi_accion` VARCHAR(45) NULL,
PRIMARY KEY (`idaudi`));
USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.curso_BEFORE_UPDATE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`curso_BEFORE_UPDATE` BEFORE UPDATE ON `curso` FOR EACH ROW BEGIN insert into auditoria_curso(audi_nombreAnterior, audi_horasAnterior, audi_valorAnterior, audi_nombreNuevo, audi_horasNuevo, audi_valorNuevo, audi_fechaModificacion, audi_usuario, audi_codCurso, audi_accion) values( old.nom_curs, old.horas_cur, old.valor_cur, new.nom_curs, new.horas_cur, new.valor_cur, now(), current_user(), new.cod_curso, 'Actualización'); END$$ DELIMITER ; Probamos el disparador: update curso set nom_curs= 'Procedimientos almacenados MySQL' where cod_curso = 172943;
6.2.2 Eliminar registros curso USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.curso_BEFORE_UPDATE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`curso_BEFORE_UPDATE` BEFORE UPDATE ON `curso` FOR EACH ROW BEGIN insert into auditoria_curso(audi_nombreAnterior, audi_horasAnterior, audi_valorAnterior, audi_nombreNuevo, audi_horasNuevo, audi_valorNuevo,
audi_fechaModificacion, audi_usuario, audi_codCurso, audi_accion) values( old.nom_curs, old.horas_cur, old.valor_cur, new.nom_curs, new.horas_cur, new.valor_cur, now(), current_user(), new.cod_curso, 'Actualización'); END$$ DELIMITER ; USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.curso_AFTER_DELETE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`curso_AFTER_DELETE` AFTER DELETE ON `curso` FOR EACH ROW BEGIN insert into auditoria_curso(audi_nombreAnterior, audi_horasAnterior, audi_valorAnterior, audi_fechaModificacion, audi_usuario, audi_codCurso, audi_accion) values(old.nom_curs, old.horas_cur, old.valor_cur, now(), current_user(), old.cod_curso, 'Registro eliminado'); END$$ DELIMITER ; 6.3 Disparadores para estudiante Creamos la tabla para la auditoría: CREATE TABLE `laboratoriosql`.`auditoria_estudiantes` ( `idaudi` INT NOT NULL AUTO_INCREMENT, `audi_nomAnterior` VARCHAR(30) NULL, `audi_apeAnterior` VARCHAR(30) NULL, `audi_edadAnterior` INT NULL, `audi_nomNuevo` VARCHAR(30) NULL, `audi_apeNuevo` VARCHAR(30) NULL, `audi_edadNuevo` INT NULL, `audi_fechaModificacion` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, `audi_usuario` VARCHAR(45) NULL DEFAULT 'CURRENT_USER()', `audi_docest` VARCHAR(20) NULL, `audi_accion` VARCHAR(45) NULL, PRIMARY KEY (`idaudi`)); 6.3.1 Disparador para actualizaciones de registros USE `laboratoriosql`; DELIMITER $$
DROP TRIGGER IF EXISTS laboratoriosql.estudiante_BEFORE_UPDATE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`estudiante_BEFORE_UPDATE` BEFORE UPDATE ON `estudiante` FOR EACH ROW BEGIN insert into auditoria_estudiantes(audi_nomAnterior, audi_apeAnterior, audi_edadAnterior, audi_nomNuevo, audi_apeNuevo, audi_edadNuevo, audi_fechaModificacion, audi_usuario, audi_docest, audi_accion) values(old.nom_est, old.ape_est, old.edad_est, new.nom_est, new.ape_est, new.edad_est, now(), current_user(), old.doc_est, 'Actualización'); END$$ DELIMITER ; 6.3.2 Disparador para eliminación de registros USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.estudiante_AFTER_DELETE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`estudiante_AFTER_DELETE` AFTER DELETE ON `estudiante` FOR EACH ROW BEGIN insert into auditoria_estudiante(audi_nomAnterior, audi_apeAnterior, audi_edadAnterior, audi_fechaModificacion, audi_usuario, audi_docest, audi_accion) values(old.nom_est, old.ape_est, old.edad_est, now(), current_user(), old.doc_est, 'Registro eliminado'); END$$ DELIMITER ; 6.4 Disparadores para automotores 6.4.1 Disparador de actualización USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.automotores_BEFORE_UPDATE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`automotores_BEFORE_UPDATE` BEFORE UPDATE ON `automotores` FOR EACH ROW BEGIN
insert into auditoria_automotores(audi_marcaAnterior, audi_tipoAnterior, audi_modeloAnterior, audi_numAnterior, audi_cilinAnterior, audi_chasisAnterior, audi_marcaNuevo, audi_tipoNuevo, audi_modeloNuevo, audi_numNuevo, audi_cilinNuevo, audi_chasisNuevo, audi_fechaModificacion, audi_usuario, audi_placa, audi_accion) values(old.automarca, old.autotipo, old.automodelo, old.autonumpasajeros, old.autocilindraje, old.autonumchasis, new.automarca, new.autotipo, new.automodelo, new.autonumpasajeros, new.autocilindraje, new.autonumchasis, now(), current_user(), old.autoplaca, 'Actualización'); END$$ DELIMITER ; 6.4.2 Disparador eliminación registros DELIMITER ; USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.automotores_AFTER_DELETE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`automotores_AFTER_DELETE` AFTER DELETE ON `automotores` FOR EACH ROW BEGIN insert into auditoria_automotores(audi_marcaAnterior, audi_tipoAnterior, audi_modeloAnterior, audi_numAnterior, audi_cilinAnterior, audi_chasisAnterior, audi_fechaModificacion, audi_usuario, audi_placa, audi_accion) values(old.automarca, old.autotipo, old.automodelo, old.autonumpasajeros, old.autocilindraje, old.autonumchasis, now(), current_user(), old.autoplaca, 'Registro eliminado'); END$$ DELIMITER ; 6.5 Disparadores para aseguramientos en MySQL CREATE TABLE `laboratoriosql`.`auditoria_aseguramientos` ( `audi_aseIniAnterior` DATE NOT NULL, `audi_aseFinAnterior` DATE NULL, `audi_valorAseAnterior` INT NULL, `audi_estadoAnterior` VARCHAR(11) NULL, `audi_placaAnterior` VARCHAR(6) NULL, `audi_costoAnterior` INT NULL, `audi_aseIniNuevo` DATE NOT NULL, `audi_aseFinNuevo` DATE NULL, `audi_valorNuevo` INT NULL,
`audi_estadoNuevo` VARCHAR(11) NULL, `audi_placaNuevo` VARCHAR(6) NULL, `audi_costoNuevo` INT NULL, `audi_fechaModificacion` DATETIME NULL, `audi_usuario` VARCHAR(45) NULL DEFAULT 'current_user()', `audi_asecodigo` INT NULL, `audi_accion` VARCHAR(45) NULL, PRIMARY KEY (`audi_aseIniAnterior`)); 6.5.1 Trigger para actualización USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.aseguramientos_BEFORE_UPDATE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`aseguramientos_BEFORE_UPDATE` BEFORE UPDATE ON `aseguramientos` FOR EACH ROW BEGIN insert into auditoria_aseguramientos(audi_aseIniAnterior, audi_aseFinAnterior, audi_valorAseAnterior, audi_estadoAnterior, audi_placaAnterior, audi_costoAnterior, audi_aseIniNuevo, audi_aseFinNuevo, audi_valorNuevo, audi_estadoNuevo, audi_placaNuevo, audi_costoNuevo, audi_fechaModificacion, audi_usuario, audi_asecodigo, audi_accion) values(old.asefechainicio, old.asefechaexpiracion, old.asevalorasegurado, old.aseestado, old.aseplaca, old.asecosto, new.asefechainicio, new.asefechaexpiracion, new.asevalorasegurado, new.aseestado, new.aseplaca, new.asecosto, now(), current_user(), old.asecodigo, 'Actualización'); END$$ DELIMITER ; 6.5.2 Trigger para eliminación USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.aseguramientos_AFTER_DELETE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`aseguramientos_AFTER_DELETE` AFTER DELETE ON `aseguramientos` FOR EACH ROW BEGIN insert into auditoria_aseguramientos(audi_aseIniAnterior, audi_aseFinAnterior, audi_valorAseAnterior, audi_estadoAnterior, audi_placaAnterior,
audi_costoAnterior, audi_fechaModificacion, audi_usuario, audi_asecodigo, audi_accion) values(old.asefechainicio, old.asefechaexpiracion, old.asevalorasegurado, old.aseestado, old.aseplaca, old.asecosto, now(), current_user(), old.asecodigo, 'Eliminación registro'); END$$ DELIMITER ; 6.6 Disparadores para la tabla incidentes CREATE TABLE `laboratoriosql`.`auditoria_incidentes` ( `idaudi` INT NOT NULL AUTO_INCREMENT, `audi_fechaAnterior` DATE NOT NULL, `audi_placaAnterior` VARCHAR(6) NOT NULL, `audi_lugarAnterior` VARCHAR(40) NOT NULL, `audi_heridosAnterior` INT(11) NULL, `audi_fatalidadesAnterior` INT(11) NULL DEFAULT NULL, `audi_involucradosAnterior` INT(11) NULL DEFAULT NULL, `audi_fechaNuevo` DATE NOT NULL, `audi_placaNuevo` VARCHAR(6) NOT NULL, `audi_lugarNuevo` VARCHAR(40) NOT NULL, `audi_heridosNuevo` INT(11) NULL, `audi_fatalidadesNuevo` INT(11) NULL DEFAULT NULL, `audi_involucradosNuevo` INT(11) NULL DEFAULT NULL, `audi_fechaModificacion` DATETIME NULL, `audi_usuario` VARCHAR(45) NULL, `audi_incicodigo` INT NULL, `audi_accion` VARCHAR(45) NULL, PRIMARY KEY (`idaudi`)); 6.6.1 Actualización USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.incidentes_BEFORE_UPDATE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`incidentes_BEFORE_UPDATE` BEFORE UPDATE ON `incidentes` FOR EACH ROW BEGIN insert into auditoria_incidentes(audi_fechaAnterior, audi_placaAnterior, audi_lugarAnterior, audi_heridosAnterior, audi_fatalidadesAnterior, audi_involucradosAnterior, audi_fechaNuevo, audi_placaNuevo, audi_lugarNuevo, audi_heridosNuevo, audi_fatalidadesNuevo, audi_involucradosNuevo, audi_fechaModificacion, audi_usuario, audi_incicodigo, audi_accion)
values(old.incifecha, old.inciplaca, old.incilugar, old.incicantheridos, old.incicantfatalidades, old.incicanautosinvolucrados, new.incifecha, new.inciplaca, new.incilugar, new.incicantheridos, new.incicantfatalidades, new.incicanautosinvolucrados, now(), current_user(), incicodigo, 'Actualización'); END$$ DELIMITER ; 6.6.2 Eliminación de registros USE `laboratoriosql`; DELIMITER $$ DROP TRIGGER IF EXISTS laboratoriosql.incidentes_AFTER_DELETE$$ USE `laboratoriosql`$$ CREATE DEFINER = CURRENT_USER TRIGGER `laboratoriosql`.`incidentes_AFTER_DELETE` AFTER DELETE ON `incidentes` FOR EACH ROW BEGIN insert into auditoria_incidentes(audi_fechaAnterior, audi_placaAnterior, audi_lugarAnterior, audi_heridosAnterior, audi_fatalidadesAnterior, audi_involucradosAnterior, audi_fechaModificacion, audi_usuario, audi_incicodigo, audi_accion) values(old.incifecha, old.inciplaca, old.incilugar, old.incicantheridos, old.incicantfatalidades, old.incicanautosinvolucrados, now(), current_user(), incicodigo, 'Registro eliminado'); END$$ DELIMITER ;
6.7 Disparador de actualización con Oracle (Tabla artículo) Primero creamos la tabla auditoria_articulos:
6.7.1 Actualización create or replace trigger "ACTUALIZAR_ARTICULO" BEFORE update on "ARTICULO" for each row begin insert into auditoria_articulo(TITARTANTERIOR, AUTARTANTERIOR, EDIARTANTERIOR, PRECARTANTERIOR, TITARTNUEVO, AUTARTNUEVO, EDIARTNUEVO, PRECARTNUEVO, FECHAMODIFICACION, USUARIO, IDART, ACCION) values(:old.tit_art, :old.aut_art, :old.edi_art, :old.prec_art, :new.tit_art, :new.aut_art, :new.edi_art, :new.prec_art, sysdate, user, :old.id_art, 'Actualización'); end; Al editar un registro de la tabla artículo se crea el registro correspondiente en la tabla de auditoría.
6.7.2 Eliminar registros create or replace trigger "ELIMINAR_ARTICULO" AFTER delete on "ARTICULO" for each row begin insert into auditoria_articulo(TITARTANTERIOR, AUTARTANTERIOR, EDIARTANTERIOR, PRECARTANTERIOR, FECHAMODIFICACION, USUARIO, IDART, ACCION) values(:old.tit_art, :old.aut_art, :old.edi_art, :old.prec_art, sysdate, user, :old.id_art, 'Eliminación de registro'); end; 6.8 Disparadores para la tabla cliente CREATE TABLE "AUDITORIA_CLIENTE" ( "NOMCLIANT" VARCHAR2(30), "APECLIANT" VARCHAR2(30), "DIRCLIANT" VARCHAR2(100), "DEPCLIANT" VARCHAR2(20), "MESANT" VARCHAR2(10), "NOMCLINUE" VARCHAR2(30), "APECLINUE" VARCHAR2(30), "DIRCLINUE" VARCHAR2(100), "DEPCLINUE" VARCHAR2(20), "MESNUE" VARCHAR2(10), "FECHAMOD" DATE, "USUARIO" VARCHAR2(45), "IDCLI" VARCHAR2(20), "ACCION" VARCHAR2(45) ) ;
6.8.1 Actualizar create or replace trigger "ACTUALIZAR_CLIENTE" BEFORE update on "CLIENTE" for each row begin insert into auditoria_cliente(NOMCLIANT, APECLIANT, DIRCLIANT, DEPCLIANT, MESANT, NOMCLINUE, APECLINUE, DIRCLINUE, DEPCLINUE, MESNUE,FECHAMOD, USUARIO, IDCLI, ACCION) values(:old.NOM_CLI, :old.APE_CLI, :old.DIR_CLI, :old.DEP_CLI, :old.MES_CUM_CLI, :new.NOM_CLI, :new.APE_CLI, :new.DIR_CLI, :new.DEP_CLI, :new.MES_CUM_CLI, sysdate, user, :old.ID_CLI, 'Actualizacion'); end;
6.8.2 Eliminar registros create or replace trigger "ELIMINAR_CLIENTE" AFTER delete on "CLIENTE" for each row begin insert into auditoria_cliente(NOMCLIANT, APECLIANT, DIRCLIANT, DEPCLIANT, MESANT,FECHAMOD, USUARIO, IDCLI, ACCION) values(:old.NOM_CLI, :old.APE_CLI, :old.DIR_CLI, :old.DEP_CLI, :old.MES_CUM_CLI, sysdate, user, :old.ID_CLI, 'Actualizacion'); end; 6.9 Disparadores para la tabla pedido CREATE TABLE "AUDITORIA_PEDIDO" ( "IDCLIPEDA" VARCHAR2(20), "FECPEDA" DATE, "VALPEDA" NUMBER, "IDCLIPEDN" VARCHAR2(20), "FECPEDN" DATE, "VALPEDN" NUMBER, "FECMOD" DATE, "USUARIO" VARCHAR2(45), "IDPED" NUMBER, "ACCION" VARCHAR2(45) ); 6.9.1 Actualizar create or replace trigger "ACTUALIZAR_PEDIDO" BEFORE update on "PEDIDO" for each row begin INSERT INTO AUDITORIA_PEDIDO(IDCLIPEDA, FECPEDA, VALPEDA, IDCLIPEDN, FECPEDN, VALPEDN, FECMOD, USUARIO, IDPED, ACCION) VALUES(:OLD.ID_CLI_PED, :OLD.FEC_PED, :OLD.VAL_PED,:NEW.ID_CLI_PED, :NEW.FEC_PED, :NEW.VAL_PED, SYSDATE, USER,:OLD.ID_PED, 'Actualización'); end;
6.9.2 Eliminar registros create or replace trigger "ELIMINAR_PEDIDO" AFTER delete on "PEDIDO" for each row begin INSERT INTO AUDITORIA_PEDIDO(IDCLIPEDA, FECPEDA, VALPEDA, FECMOD, USUARIO, IDPED, ACCION) VALUES(:OLD.ID_CLI_PED, :OLD.FEC_PED, :OLD.VAL_PED, SYSDATE, USER,:OLD.ID_PED, 'ELiminación registro'); end;
Referencias Las bases de datos fueron creadas con las herramientas MySQL Workbench y la interfaz web de Oracle. Se utilizó información de las páginas: www.oracleya.com www.mysqlconclase.com www.stackoverflow.com www.desarrolloweb.com www.elbauldelprogramador.com www.techonthenet.com El resto de la información pertenece a los objetos de aprendizaje del Sena.
