EXPLICACION Y EJEMPLOS DE DISPARADORES BASES DE DATOS
by ALFJZ 0
update paises set continente='EUROPA' where CO_PAIS = 'ESP';
DISPARADORES:
CREATE OR REPLACE TRIGGER Insertempleado
AFTER INSERT OR UPDATE
ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
Insert into jobs(job_id, job_title, min_salary, max_salary) values('A', 'A', 0, 1);
ELSE
dbms_output.put_line(' No se ha podido realizar la accion '); END IF;
END Insertempleado;
CREATE OR REPLACE TRIGGER preciovp BEFORE INSERT OR UPDATE OF costo on productos
FOR EACH ROW
BEGIN
:NEW.preciopub := :new.costo * (1 + (:new.porcentajegan/100));
END preciovp ;
CREATE OR REPLACE TRIGGER TR_PRODUCTOS_01
AFTER INSERT ON PRODUCTOS
FOR EACH ROW
DECLARE
-- local variables
BEGIN
INSERT INTO PRECIOS_PRODUCTOS
(CO_PRODUCTO,PRECIO,FX_ACTUALIZACION)
VALUES
(:NEW.CO_PRODUCTO,100,SYSDATE);
END ;
AFTER INSERT ON PRODUCTOS
FOR EACH ROW
DECLARE
-- local variables
BEGIN
INSERT INTO PRECIOS_PRODUCTOS
(CO_PRODUCTO,PRECIO,FX_ACTUALIZACION)
VALUES
(:NEW.CO_PRODUCTO,100,SYSDATE);
END ;
create or replace trigger CONTROL_ESTADISTICAS
before insert
on PARTICIPANTES
FOR EACH ROW
begin
insert into CONTROL values(user,sysdate,:NEW.NOMBRE);
end CONTROL_ESTADISTICAS;
CREATE OR REPLACE TRIGGER INSERTAR
BEFORE INSERT ON PARTICIPANTES
FOR EACH ROW
BEGIN
if :new.codigo_categoria = 1100 then
insert into profesional values (:new.CEDULA,:new.NOMBRE,:new.APELLIDO,:new.cuidad_origen);
else if (:new.codigo_categoria = 2200) then
insert into juvenil values (:new.CEDULA,:new.NOMBRE,:new.APELLIDO,:new.cuidad_origen);
else if (:new.codigo_categoria = 3300) then
insert into INFANTIL values (:new.CEDULA,:new.NOMBRE,:new.APELLIDO,:new.cuidad_origen);
else if (:new.codigo_categoria = 4400) then
insert into aficionado values (:new.CEDULA,:new.NOMBRE,:new.APELLIDO,:new.cuidad_origen);
else if (:new.codigo_categoria =5500) then
insert into cancion_inedita values (:new.CEDULA,:new.NOMBRE,:new.APELLIDO,:new.cuidad_origen);
end if;
end if;
end if;
end if;
end if;
END;
CREATE OR REPLACE TRIGGER ELIMINADOS
BEFORE DELETE ON PARTICIPANTES
FOR EACH ROW
BEGIN
insert into eliminados values (:OLD.CEDULA,:OLD.NOMBRE,:OLD.APELLIDO,SYSDATE,USER);
delete from repertorio where cedula_participante=:old.cedula;
delete from profesional where cedula_participante = :old.cedula;
delete from juvenil where cedula_participante = :old.cedula;
delete from infantil where cedula_participante = :old.cedula;
delete from aficionado where cedula_participante = :old.cedula;
delete from cancion_inedita where cedula_participante = :old.cedula;
NULL;
END;
/
CREATE OR REPLACE TRIGGER ACTUALIZAR_CODIGO_CATEGORIAS
BEFORE UPDATE ON CATEGORIAS
FOR EACH ROW
BEGIN
update participantes set codigo_categoria = :new.codigo where codigo_categoria = :old.codigo;
NULL;
END;
CREATE TRIGGER trigger_fechanac
BEFORE INSERT OR UPDATE OF fechanac ON alumnos FOR EACH ROW
DECLARE
edad NUMBER;
edad_out_of_range EXCEPTION;
BEGIN
/* Se trae la Edad del alumno edad. */
SELECT trunc(months_between(sysdate,:new.fechanac)/12) INTO edad from alumnos;
/* If the employee's new salary is less than or greater than the job classification's limits, the exception is
raised. The exception message is returned and the pending INSERT or UPDATE statement that fired the trigger is rolled back. */
IF (edad < 15) THEN
RAISE edad_out_of_range;
END IF;
EXCEPTION
WHEN edad_out_of_range THEN
raise_application_error (-20300,'Edad out of range for' );
WHEN NO_DATA_FOUND THEN
raise_application_error(-20322,'Invalid Job Classification');
END;
CREATE TRIGGER BUpCUOTA
BEFORE UPDATE OF f_pago ON Cuota
FOR EACH ROW
WHEN (new.f_pago > old.f_venc)
BEGIN
raise_application_error(-20000, 'Cuota' || TO_CHAR(:old.num_cuota) || ' del prestamo ' || TO_CHAR(:old.num_prest) || ' Vencida. Por favor, dirigirse a la gerencia');
END;
/
CREATE OR REPLACE TRIGGER AUDITORIA_CUOTA
BEFORE INSERT ON CUOTA
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITORIA(AUD_USER,AUD_FECHA,AUD_TIPO,AUD_TERMINAL)
VALUES(USER,SYSDATE,'INSERT',USERENV('TERMINAL'));
END IF;
END;
Insert into cuota(num_prest,num_cuota,f_venc)values(100,2,to_date('30/09/2011','dd/mm/yyyy'));
