Qu son losTriggers?
Tabla
Esquema
Vista Base de Datos
Triggers de Base de Datos
Slo se almacena el cdigo fuente Pueden afectar N filas
Se deben especificar las condiciones
No admite parmetros
y las acciones que realizar
Su nombre debe ser nico en el Cuando falla, se realiza rollback de
mismo esquema sus sentencias
Triggers de Base de Datos
Se pueden escribir triggers que se activen cada vez que una de las
siguientes operaciones se produce en la base de datos:
SENTENCIAS SENTENCIAS
DML DDL
OPERACIN DE LA
BASE DE DATOS
Creando Trigger asociado a Sentencias
DML
La definicin de un trigger tiene 3 partes:
Comando
Restriccin
Accin
Sintaxis:
CREATE [OR REPLACE] TRIGGER nombre_trigger
tiempo
evento1 [OR evento2 OR evento3 ]
ON nombre_object
[[REFERENCING OLD AS old | NEW AS new ]
FOR EACH ROW
[WHEN (condicin)]]
cuerpo_trigger
Cundo Ejecutar un Trigger DML
SENTENCIAS SENTENCIAS
DML DML
INSTEAD OF
X
SENTENCIAS
DML
Tipos de Triggers DML
Trigger a nivel de Sentencia Trigger a nivel de Fila
Utilice la clusula FOR EACH ROW al
Es el valor por defecto cuando se crea
crear un disparador.
Se ejecuta una vez para el evento que lo Se ejecuta una vez para cada fila afectada
desencaden por el evento que lo desencaden
Se dispara una vez, incluso si no hay filas No se dispara si el evento
afectadas desencadenante no afecta a ninguna fila
Creando Trigger a Nivel de Sentencia
Ejemplo:
Aplicacin 2
INSERT INTO EMPLOYEES...;
Tabla EMPLOYEES
Trigger TRG_SEGURIDAD_EMP
1
CREATE OR REPLACE TRIGGER TRG_SEGURIDAD_EMP
BEFORE INSERT ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20500,
'Se debe insertar en tabla EMPLOYEES slo durante horas de trabajo.');
END IF;
END;
Creando Trigger a Nivel de Sentencia
Ejemplo:
Aplicacin
INSERT INTO employees (employee_id, last_name, first_name, email, hire_date,
job_id, salary, department_id)
VALUES (300, 'Smith', 'Rob', 'RSMITH', SYSDATE, 'IT_PROG', 4500, 60);
Uso de Predicados Condicionales
- INSERTING
- DELETING
- UPDATING
Ejemplo:
CREATE OR REPLACE TRIGGER TRG_VALIDA_DML_EMP
BEFORE INSERT OR UPDATE OR DELETE ON employees BEGIN
IF TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '08' AND '18' THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR(-20502, 'Se debe eliminar desde tabla EMPLOYEES slo
durante horas de trabajo.');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR(-20500, 'Se debe insertar en tabla EMPLOYEES slo
durante horas de trabajo.');
ELSIF UPDATING('SALARY') THEN
RAISE_APPLICATION_ERROR(-20503, 'Se debe actualizar salario slo durante horas
de trabajo.');
END IF;
END IF;
END;
Uso de OLD y NEW en un Trigger
Se pueden utilizar slo en trigger a nivel de filas
Se utilizan para referenciar el valor de una columna antes y despus
del cambio de dato
Deben ir precedidos por : (dos puntos)
Si son referenciados en la condicin de restriccin WHEN no deben ir
precedidos por :
:NEW no se puede modificar en un Trigger AFTER
:OLD slo se puede leer
Uso de OLD y NEW en un Trigger
Valores de OLD y NEW en cada operacin DML que gatilla el trigger:
OPERACIN VALOR OLD VALOR NEW
INSERT NULL Valor Insertado o que se va a insertar
Valor despus del UPDATE (nuevo
Valor antes de UPDATE (valor
UPDATE valor con el cual se va a actualizar la
que existe en la tabla)
tabla)
Value antes de DELETE (valor
DELETE NULL
que existe en la tabla)
Creando Trigger a Nivel de Fila
Ejemplo:
CREATE SEQUENCE SEQ_RESTRINGE_SAL;
CREATE TABLE VALIDA_SALARIOS
(sec_error NUMBER(5) CONSTRAINT PK_VALIDA_SALARIOS PRIMARY KEY,
id_empleado NUMBER(5) NOT NULL,
desc_error VARCHAR2(50) NOT NULL);
CREATE OR REPLACE TRIGGER TRG_RESTRINGIR_SALARIO
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.job_id NOT IN ('AD_PRES', 'AD_VP')
AND :[Link] > 15000 THEN
INSERT INTO valida_salarios
VALUES(seq_restringe_sal.NEXTVAL, :NEW.employee_id, 'Empleado no puede ganar
ms de $15,000.');
END IF;
END;
Creando Trigger a Nivel de Fila
Ejemplo
CREATE TABLE audit_emp
(user_name varchar2(30),
old_employee_id number(5),
new_employee_id number(5),
old_last_name varchar2(30),
1 new_last_name varchar2(30),
old_title varchar2(10),
new_title varchar2(10),
old_salary number(8,2),
new_salary number(8,2));
CREATE OR REPLACE TRIGGER TRG_AUDIT_EMP
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
2 BEGIN
INSERT INTO audit_emp(user_name, old_employee_id, new_employee_id,
old_last_name, new_last_name, old_title, new_title, old_salary, new_salary)
VALUES (USER, :OLD.employee_id, :NEW.employee_id, :OLD.last_name, :NEW.last_name,
:OLD.job_id, :NEW.job_id, :[Link], :[Link]);
END;
Creando Trigger a Nivel de Fila
3 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES(999, 'Ruth', 'Soto','RSOTO', sysdate, 'AD_ASST', 4000);
UPDATE employees
4 SET salary = 7000, last_name = 'Smith'
WHERE employee_id = 999;
DELETE FROM employees
5 WHERE employee_id = 999;
Creando Trigger a Nivel de Fila
Ejemplo
CREATE TABLE comisiones
1 (id_empleado number(5),
comision number(5));
CREATE OR REPLACE TRIGGER TRG_COMISION
BEFORE INSERT OR UPDATE OF SALARY OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO comisiones
2 VALUES(:NEW.employee_id, :[Link] * .25);
ELSIF UPDATING THEN
UPDATE comisiones
SET comision = :[Link] *.25
WHERE id_empleado = :NEW.employee_id;
ELSE
DELETE FROM comisiones
WHERE id_empleado = :OLD.employee_id;
END IF;
END;
Creando Trigger a Nivel de Fila
3 INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES(7777, 'Ruth', 'Soto','RSOTO', sysdate, 'AD_ASST', 4000);
UPDATE employees
4 SET salary = 15000
WHERE employee_id = 7777;
DELETE FROM employees
5 WHERE employee_id = 7777;
Restringiendo un Trigger a Nivel de Fila
Para restringir un trigger a aquellas filas que satisfacen una cierta
condicin se debe utilizar la clusula WHEN.
Ejemplo:
CREATE OR REPLACE TRIGGER TRG_derive_commission_pct
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.job_id = 'SA_REP')
BEGIN
IF INSERTING THEN
:NEW.commission_pct := 0;
ELSE
:NEW.commission_pct := :OLD.commission_pct+0.05;
END IF;
END;
CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE OF salary, job_id
Tablas Mutantes
ON employees
FOR EACH ROW
WHEN (NEW.job_id <> 'AD_PRES')
DECLARE
minsalary [Link]%TYPE;
1 maxsalary [Link]%TYPE;
BEGIN
SELECT MIN(salary), MAX(salary)
INTO minsalary, maxsalary
FROM employees
WHERE job_id = :NEW.job_id;
IF :[Link] < minsalary OR
:[Link] > maxsalary THEN
RAISE_APPLICATION_ERROR(-20505, 'Salario fuera de rango para el trabajo');
END IF;
END;
UPDATE employees
2 SET salary = 3400
WHERE last_name = 'Stiles';
3
INSERT INTO emp_details
Trigger INSTEAD OF
VALUES (9001,'ABBOTT',3000, 10, 'Administration');
INSERT tabla
NEW_EMPS
En lugar de insertar en
Vista EMP_DETAILS
3
UPDATE tabla
NEW_DEPTS
Vista EMP_DETAILS
Trigger INSTEAD OF
Ejemplo:
CREATE OR REPLACE TRIGGER TRG_V_DETALLE_EMPS
INSTEAD OF INSERT OR UPDATE OR DELETE ON v_detalle_emps
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO datos_emps
VALUES (:NEW.employee_id, :NEW.last_name,:[Link],:NEW.department_id);
UPDATE salarios_por_depto
SET dept_sal = dept_sal + :[Link]
WHERE department_id = :NEW.department_id;
ELSIF DELETING THEN
DELETE FROM datos_emps
WHERE employee_id = :OLD.employee_id;
UPDATE salarios_por_depto
SET dept_sal = dept_sal - :[Link]
WHERE department_id = :OLD.department_id;
ELSIF UPDATING ('salary') THEN
UPDATE datos_emps
SET salary = :[Link]
WHERE employee_id = :OLD.employee_id;
UPDATE salarios_por_depto
SET dept_sal = dept_sal + (:[Link] - :[Link])
WHERE department_id = :OLD.department_id;
END IF;
END;
Estado de un Trigger
ACTIVADO DESACTIVADO
En Oracle Database 11g, se puede crear un trigger deshabilitado y
luego activarlo cuando se sabe que va a ser compilado con xito.
CREATE OR REPLACE TRIGGER trg_disable
BEFORE INSERT ON employees FOR EACH ROW
DISABLE
BEGIN
:[Link] := seq_empleados.NEXTVAL;
...
END;
Uso de Sentencia CALL en Triggers
Sintaxis:
CREATE [OR REPLACE] TRIGGER nombre_trigger
tiempo
evento1 [OR evento2 OR evento3 ]
ON nombre_tabla
[REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW]
[WHEN condicin ]
CALL nombre_procedimiento
Ejemplo:
CREATE OR REPLACE PROCEDURE sp_check_salario
(v_salario IN [Link]%TYPE)
IS
BEGIN
1 IF v_salario > 20000 THEN
DBMS_OUTPUT.PUT_LINE('No corresponde ese salario
para el empleado');
2
END IF;
CREATE OR REPLACE TRIGGER trg_check_salary
END sp_check_salario;
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.job_id <> 'AD_PRES')
CALL sp_check_salario(:[Link])
Manejando Trigger con Sentencias ALTER y
DROP
Desactivar o volver a activar un trigger de base de datos:
ALTER TRIGGER nombre_trigger DISABLE | ENABLE;
Deshabilitar o volver a activar todos los triggers para una tabla:
ALTER TABLE nombre_tabla DISABLE | ENABLE ALL TRIGGERS;
Volver a compilar un trigger:
ALTER TRIGGER nombre_trigger COMPILE;
Elimina un trigger desde la base de datos:
DROP TRIGGER nombre_trigger;
Creando Trigger asociado a Sentencias
DDL
Sintaxis:
CREATE [OR REPLACE] TRIGGER nombre_trigger
tiempo
[evento_ddl1 [OR evento_ddl2 OR ...]]
ON {DATABASE|SCHEMA}
cuerpo_trigger
Ejemplo:
CREATE OR REPLACE TRIGGER trg_drop_bd
BEFORE DROP ON DATABASE
DECLARE oper system.ddl_log.operation%TYPE;
BEGIN
INSERT INTO system.ddl_log
SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, NULL,
USER, SYSDATE FROM dual;
END bds_trigger;
Creando Trigger asociado a Eventos
Sintaxis:
CREATE [OR REPLACE] TRIGGER nombre_trigger
BEFORE | AFTER
[evento1_basededatos1 [OR evento1_basededatos1 OR ...]]
ON {DATABASE | SCHEMA}
cuerpo_tigger
Ejemplo:
CREATE OR REPLACE TRIGGER trg_logon
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO log_trig_tabla(user_id,log_date,action)
VALUES (USER, SYSDATE, 'Logging on');
END;
Obteniendo Informacin de Triggers desde el
Diccionario de Datos
USER_TRIGGERS: informacin USER_OBJECTS: informacin de
detallada de los triggers creados por todos los objetos que pertenecen al
el usuario. usuario.
USER_ERRORS: informacin sobre
errores de compilacin de los triggers
creados por el usuario.
