Proporcione un ejemplo del uso de activadores en la base de datos Oracle.
1. Permitir/restringir modificaciones de la tabla
2. Generar automáticamente columnas derivadas, como campos de incremento automático
3. p >
4. Proporcionar auditoría y registro
5. Evitar transacciones no válidas
6. Habilitar una lógica empresarial compleja
Comenzar
Antes de insertar o actualizar
Crear disparador biufer_employees_department_id
antes de insertar o actualizar
de departamental_id
en los empleados
haciendo referencia a antiguo como valor_antiguo
nuevo como valor_nuevo
para cada fila
cuando (nuevo_valor.department_idlt;gt;80)
comenzar
.commission_pct := 0;
fin
/
Componentes del disparador:
1. Nombre del activador
2. Declaración del activador
3. Restricciones del activador
4.
Crear activador biufer_employees_department_id
Regla de nomenclatura:
biufer (antes de insertar y actualizar cada fila)
nombre de la tabla de empleados
nombre de la columna department_id p>
2. Declaración de activación
Por ejemplo:
1:
en la tabla o ver declaraciones DML
DDL declaraciones
Apagado o inicio de base de datos, apagado de inicio, etc.
Antes de insertar o actualizar
Insertar o actualizar id_departamento
En la tabla de empleados
Referencia antigua como valor_antiguo
nuevo como nuevo_valor
Para cada fila
Descripción:
1 Al INSERTAR la tabla de empleados, si se especifica el ID del departamento
2. Cuando se actualiza la columna depart_id de la tabla de empleados
3. Activar restricciones
cuando (new_value. depart_idlt; gt; 80)
Sin restricciones. requerido. Este ejemplo muestra que si la columna id_departamento no es igual a 80, se ejecutará el disparador.
Entre ellos, new_value representa el valor después de operaciones posteriores.
4. La operación del disparador
es el tema del disparador
comenzar
: new_value.commission_pct:= 0; >
end;
El cuerpo principal es muy simple, que consiste en establecer la columna Commission_pct actualizada en 0
Activador:
insertar en empleados( empleado_id,
apellido, nombre, fecha_contratación, id_trabajo, correo electrónico, id_departamento, salario, comisión_pct)
valores (12345, 'Chen', 'Donny', sysdate, 12, 'donny @hotmail .com', 60, 10000, .25);
seleccione Commission_pct de empleados empleado donde_id=12345
El disparador cambia la entrada del usuario sin notificarlo.
Tipos de activadores:
1. Activadores de declaración
2. Activadores de fila
3. > p>
4. Activador de condición del sistema
5. Activador de evento de usuario
1. Activador de declaración
Está en una tabla o en determinadas situaciones. Un disparador que ejecuta una declaración específica o un grupo de declaraciones en una vista. Se puede asociar con INSERTAR, ACTUALIZAR,
ELIMINAR o una combinación. Pero no importa qué combinación se utilice, cada activador de declaración solo se puede activar una vez para la declaración especificada
. Por ejemplo, el activador de la declaración de actualización solo se llamará una vez, sin importar cuántas filas se actualicen.
Ejemplo:
Los usuarios que realizan operaciones DML en tablas deben someterse a una verificación de seguridad para determinar si tienen los permisos adecuados.
Crear tabla foo(un número);
Crear disparador biud_foo
Antes de insertar, actualizar o eliminar
En foo
Comenzar
Si el usuario no está en ('DONNY'), entonces
Raise_application_error(-20001, 'No tienes acceso para modificar esta tabla.'); /p>
End if;
End;
/
Incluso los usuarios de SYS y SYSTEM no pueden modificar la tabla foo
[Experimento]
Registrar la hora y la persona que modificó la tabla.
1. Crear tabla de prueba
crear tabla empleados_copia como selección *de hr. empleados
2. Crear tabla de registro
crear tabla empleados_log(
quién varchar2(30),
cuándo fecha
p>fecha de cuándo) p>
3. Cree un activador de declaración en la tabla de copia de empleados y complete la tabla de registro de empleados en el activador.
Crear o reemplazar el activador biud_employee_copy
Antes de insertar, actualizar o eliminar
En empleados_copia
Comenzar
Insertar en empleados_log(
Quién, cuándo)
Valores( usuario, sysdate
Fin;
/
<); p>4. Pruebeactualizar empleados_copiar salario= salario*1.1;
seleccionar *de empleo_log
5.
¿Qué INSERTAR/ACTUALIZAR/ELIMINAR activó el disparador?
Puede utilizar INSERTAR / ACTUALIZAR / ELIMINAR predicados condicionales en activadores para determinar:
iniciar
si se inserta entonces
- -- --
elsif actualizando y luego
-
-
Para determinar qué declaración activó el disparador, necesita Usar INSERTAR / ACTUALIZAR / BORRAR predicados condicionales.
elsif eliminando entonces
------
end if
end
if; actualizando('COL1') o actualizando('COL2 ') entonces
------
end if
[test]
1. Modificar tabla de registro
alterar tabla empleados_log
add (action varchar2(20)
2. .
Crear o reemplazar el disparador biud_employee_copy
Antes de insertar, actualizar o eliminar
En empleados_copia
Declaración
L_ acción empleados_log.actiontype;
Comenzar
Si inserta entonces
l_action:='Insert'
elsif actualiza entonces
l_action:='Actualizar';
elsif eliminando y luego
l_action:='Delete'
else
; rise_application_error(-20001, 'Nunca deberías recibir este error.');
Si estás insertando entonces
l_action:='Insert'; /p>
raise_application_error(-20001, 'Nunca deberías recibir este error.'). ');
Insertar en empleados_log(
Quién, acción, cuándo)
Valores( usuario, l_action, sysdate); Fin;
/
3. Prueba
insertar en empleados_copia( empleado_id, apellido, correo electrónico, fecha_contratación, trabajo_id)
valores( 12345, 'Chen', 'Donny@ hotmail', sysdate, 12);
seleccione *de empleados_log
actualice empleados_copia establezca salario=50000 donde empleado_id = 12345
;2. Disparador de fila
Es un disparador que se activa para cada fila afectada. Su definición es similar al disparador de declaración, pero con las siguientes dos excepciones:
1. El La declaración de definición contiene una cláusula PARA CADA FILA
2 En el disparador ANTES...PARA CADA FILA, el usuario puede hacer referencia al valor de la fila afectada.
Por ejemplo:
Definición:
Crear activador biufer_employees_department_id
antes de insertar o actualizar
de departament_id
en copia_empleados
haciendo referencia a antiguo como valor_antiguo
nuevo como valor_nuevo
para cada fila
cuando (valor_nuevo. depart_idlt;gt;80)
comienzo
: new_value.commission_pct:= 0;
fin
/
Cláusula de referencia:
El nombre predeterminado del valor antes de ejecutar la instrucción DML es: antiguo y el valor posterior es: nuevo.
La operación de inserción es solo: nuevo
La operación de eliminación es solo: antiguo
La operación de actualización es ambas
La cláusula de referencia solo Los cambios nuevos y antiguos pasan a llamarse valor_nuevo y valor_antiguo para evitar confusiones. Por ejemplo, cuando se opera en una tabla denominada
nueva.
Esto no es muy útil.
[Experimento]: Generación de números de secuencia autoincrementales para el ken maestro
drop table foo;
create table foo(id number, data varchar2(20)
crear secuencia foo_seq
crear o reemplazar el disparador bifer_foo_id_pk
antes de insertar en foo
para cada fila
comenzar
seleccione foo_seq.nextval en :new.id de dual
final
/
insertar en foo; (datos) valores('donny');
insertar en foo valores(5,'Chen');
seleccionar * de foo; En lugar de actualizar el disparador de la vista
Cree o reemplace la vista company_phone_book por
Seleccione first_name||', ' ||apellido nombre, correo electrónico, número de teléfono,
employee_id emp_id
De hr.employees
Intenta actualizar el correo electrónico y el nombre
actualizar la guía telefónica de hr.company
configurar name=' Chen1, Donny1'
donde emp_id=100
Crear o reemplazar el activador update_name_company_phone_book
EN LUGAR DE
Actualizar hr.company_phone_book
p>
Inicio
Actualizar hr.employees
Establecer empleado_id=:new.emp_id,
First_name=substr( :nuevo.nombre, instr( :nuevo.nombre,',') 2),
apellido= substr(:nuevo.nombre,1,instr(:nuevo.nombre,',')-1 ),
número_teléfono=: nuevo.número_teléfono,
correo electrónico=: nuevo.correo electrónico
donde empleado_id=: antiguo.emp_id
end;
4. Activador de eventos del sistema
Eventos del sistema: inicios de bases de datos, cierres, errores del servidor
crear disparador ad_startup
después inicio
en la base de datos
comenzar
--hacer algunas cosas
finalizar
p>
/
5. Activadores de eventos de usuario
Eventos de usuario: inicio de sesión de usuario, cierre de sesión, CREAR / ALTERAR / BORRAR / ANALIZAR / AUDITAR / CONCEDER / REVOCAR / p >
RENOMBRAR/TRUNCAR/Cerrar sesión
Ejemplo: registrar objetos eliminados
1. Tabla de registro
crear tabla drop_objects(
nombre_objeto varchar2(30),
tipo_objeto varchar2(30),
fecha drop_on);
2 Activador
crear o. reemplazar el disparador log_drop_trigger
antes de soltar donny.schema
comenzar
insertar en valores de drop_objects(
ora_dict_obj_name, - - Funciones relacionadas con disparadores
ora_dict_obj_type,
sysdate
end
/
3 .Test
Crear tabla drop_me (un número);
Crear vista drop_me_view como select *from drop_me
Eliminar vista drop_me_view
Eliminar tabla drop_me;
seleccione *from drop_objects
Deshabilitar y habilitar activadores
alter trigger lt; trigger_namegt;
alterar trigger_namegt; enable;
Transacción:
En los desencadenadores, no puede usar commit/rollback
porque las declaraciones ddl tienen confirmaciones implícitas, por lo que no está permitido usar
p>
Ver:
dba_triggers