EJERCICIOS DE BASE DE DATOS

GUIA DE EJERCICIOS SOBRE DISPARADORES EN POSTGRESQL 9.1 Ejercicio 1 La tabla item no tiene memoria de los cambios que han sufrido sus tuplas, si alguien viene y cambia el precio de venta de un artículo, no hay forma de obtener el precio anterior. Si alguien borra un artículo, tampoco sabremos que artículo ha sido borrado ni por quien. a) Crear un trigger que almacene los cambios de precio_venta en la tabla item. Solución a) 1. createdb -p 5432 -h 127.0.0.1 -U laboratorio prueba 2. psql -p 5432 -h 127.0.0.1 -U laboratorio prueba 3. create table item (item_id serial int4 not null, nombre varchar(150) not null, tipo varchar(100) not null, cantidad int4 not null, precio_compra float8 not null, precio_venta float8 not null, primary key (item_id)); 4. create table item_actualizado (item_id int4 not null, nombre varchar(150) not null, precio_compra float8 not null, precio_venta float8 not null, autor varchar (100) not null, fecha_cambio date not null, nomb_comando char(20), foreign key (item_id) references item(item_id)); 5. create language 'plpgsql'; 6. CREATE FUNCTION item_actualizado() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP='UPDATE') THEN IF (OLD.precio_venta != NEW.precio_venta) THEN INSERT INTO item_actualizado VALUES (OLD.item_id, OLD.nombre, OLD.precio_venta, NEW.precio_venta, current_user, current_date, TG_OP); END IF; END IF; RETURN NULL; END; $$ language 'plpgsql'; 7. CREATE TRIGGER actualizar_item AFTER UPDATE ON item FOR EACH ROW EXECUTE PROCEDURE item_actualizado(); 8. insert into item (nombre, tipo, cantidad, precio_compra, precio_venta) values ('monitor', 'vga','5','100', '150'); 9. insert into item (nombre, tipo, cantidad, precio_compra, precio_venta) values ('mouse', 'optico','3','30', '35'); 10. select * from item; UPDATE item SET precio_venta='40' WHERE item_id='2'; select * from item_actualizado; UPDATE item SET precio_venta='45' WHERE item_id='2'; select * from item_actualizado; b) Crear un trigger que almacene los cambios de stock en la tabla item. Ejercicio 2: A la tabla Item_actualizado añadir un atributo hora_cambio que almacene la hora de lamodificación. Ejercicio 3: Tenemos la tabla Empleado, con los campos id, nombres, apellidos y fecha. Vamos a crear un disparador para que cada vez que se inserte un registro en la tabla, se coloque automáticamente la fecha actual en el campo Fecha. Tabla: Empleado CI:varchar(10) Nombres: varchar(25) Apellidos: varchar(25) Fecha: date Ejercicio 4. Elaborar un trigger que registre a todos los empleados dados de baja por la empresa XY, con el objetivo de poder auditar los cambios realizados en el departamento de personal. Ejercicio 5: Tenemos la tabla Obreros, con los campos ci varchar(10) Primary key, Nombres Apell, varchar(40), dir varchar(60) not null y salario float8. Vamos a crear un trigger que verifique el valor del campo salario de los obreros, el cual debe ser siempre igual al salario mínimo (1,200 Bs.F). Con este trigger nos aseguramos de que cada vez que se actualice un registro, el valor de salario siempre sea igual a 1200,00 Bs.F. Ejercicio 6: En la dirección de informática del ISP, se requiere crear un trigger que verifique de que cada vez que se actualice el salario de un empleado, el valor del mismo sea siempre positivo, si fuera negativo que coloque automáticamente 0 como valor. La base de datos se llama: ISP Tenemos la tabla Personal e insertar los siguientes datos: CI: varchar(10) NombApell: varchar(40) not null Direccion: varchar(60) Sueldo: float8 CI: 5236456 NombApell: Mirta Martínez Dirección: Valencia Sueldo: 2045