Un Trigger en PLSQL es un tipo especial de procedimiento almacenado asociado con una tabla de la base de datos Oracle y que se "dispara" (is triggered) cada vez que se ejecuta sobre dicha tabla una sentencia INSERT, una sentencia UPDATE o una sentencia DELETE, es decir, es un procedimiento que permite realizar distintas operaciones cuando un registro de una tabla Oracle es actualizado o borrado, o cuando se añade un registro nuevo a dicha tabla.
Hay muchas circunstancias en las que el uso de un trigger PL/SQL puede ser extremadamente útil, por ejemplo:
- Cuando los datos de una tabla son generados desde otro tipo de procedimientos y se necesita controlar los valores que toman algunos campos determinados de la tabla en cuestión.
- Para duplicar los contenidos de una tabla automáticamente y en tiempo real.
- Para implementar complejas restricciones sobre los valores que pueden tomar los campos de una tabla Oracle, es decir, cuando los CONSTRAINTS que se pueden definir sobre una tabla son insuficientes.
- Para controlar las modificaciones de los valores de los campos de una tabla (auditorías).
- Para incrementar automáticamente los valores de un campo.
- Para realizar actualizaciones de una tabla en cascada.
- Para modificar campos o registros de una tabla que un usuario no puede modificar directamente.
Los triggers PL/SQL constituyen una potente herramienta para mantener la integridad de la base de datos, ya que pueden llevar a cabo cualquier acción que sea necesaria para el mantenimiento de dicha integridad.
Los triggers PLSQL pueden llamar a otros procedimientos y disparar otros triggers, pero no admiten parámetros y no pueden ser invocados desde otros procedimientos PLSQL.
Los triggers están almacenados en la tabla catálogo del sistema como parte de las propiedades de una tabla.
Tipos de triggers PLSQL
Los triggers PLSQL pueden actuar antes o después de que se realice una inserción, un borrado o una actualización de un registro de la tabla Oracle involucrada.
Se pueden definir triggers PL/SQL diferentes para cada tipo de evento (INSERT, UPDATE, DELETE) pero lo más lógico es crear un único trigger para todos los eventos y usar una sentencia IF para distinguir que acción realizar dependiendo del evento.
Por otro lado los triggers pueden ser a nivel de registro (row) o a nivel de sentencia (statement).
- A nivel de registro o fila el trigger PL/SQL se ejecuta cada vez que un registro es actualizado, insertado o borrado.
- A nivel de sentencia, el trigger se ejecuta una vez que la sentencia PL/SQL INSERT, UPDATE o INSERT se completa. Obviamente en este caso el trigger sólo puede ser ejecutado después (AFTER) de que se ejecute dicha sentencia.
Ventajas de los triggers PL/SQL
Estandarización: Un solo procedimiento controla las acciones que pueden realizar el resto de procedimientos sobre una tabla Oracle específica.
Eficiencia: Cuando un trigger es analizado por el optimizador Oracle, el plan de ejecución se almacena en la memoria virtual del servidor, con lo cual, si se vuelve a disparar el trigger PLSQL, este se ejecuta instantáneamente ya que existe una copia del plan de ejecución en la memoria.
Seguridad: Aumentan la seguridad porque permiten restringir el acceso de los usuarios a los datos de las tablas, al no poder éstos modificar o acceder a los mismos directamente. Las acciones que realiza un trigger normalmente se limitan en base a los permisos que tiene el usuario que creo la tabla involucrada y no en base a los permisos que tiene el usuario que disparó el trigger.
Sintaxis de los triggers en PL/SQL
CREATE OR REPLACE TRIGGER nombre-del-trigger
[FOLLOWS nombre-otro-trigger]
[BEFORE/AFTER]
[INSERT/DELETE/UPDATE/UPDATE OF lista-columnas]
ON nombre-tabla
[REFERENCING [OLD AS nombre-antiguo][NEW AS nombre-nuevo]]
[FOR EACH ROW/FOR EACH STATEMENT]
[WHEN {condiciones}]
{Bloque estándar de sentencias PL/SQL... BEGIN, EXCEPTION}
Aclaraciones:
La diferencia entre UPDATE y UPDATE OF lista-columnas es que, en el segundo caso, el trigger PL/SQL se ejecuta sólo cuando se modifica alguna de las columnas de la lista y, en el primer caso, el trigger se ejecuta en caso de que cualquiera de las columnas de la tabla sea modificada.
La cláusula FOLLOWS se utiliza para determinar el orden en que se deben ejecutar diferentes triggers del mismo tipo (INSERT, DELETE o UPDATE) que se disparan al mismo tiempo sobre una misma tabla. Así el trigger que está siendo creado se ejecutará después del trigger indicado en la cláusula FOLLOWS (sólo disponible para la versión 11g de la base de datos Oracle).
La cláusula REFERENCING permite referirnos con el nombre que nosotros indiquemos (por defecto los nombres son old y new) al valor antiguo (old) de un registro que ha sido borrado o actualizado o al valor nuevo (new) de un registro que ha sido actualizado o insertado. Yo, sinceramente, no le veo una utilidad evidente a esta cláusula debido a que los valores por defecto son suficientemente explicativos.
La cláusula WHEN determina que el trigger PLSQL se disparé sólo para los registros que cumplen la condición de la cláusula. Esta cláusula sólo se puede usar en triggers a nivel de registro.
Ejemplo:
CREATE TRIGGER tr1_empleados
BEFORE INSERT OR UPDATE OF salario
ON empleados
FOR EACH ROW
WHEN (new.salario > 5000);
BEGIN
UPDATE empleados
SET salario = 5000
WHERE empleado_id = :new.empleado_id;
END;
En este ejemplo, si insertamos o actualizamos el registro de un empleado de manera que su salario sea superior a 5000 euros, el trigger PL/SQL actualizará dicho salario al valor de 5000 euros independientemente del salario que nosotros hayamos insertado o introducido al modificar el registro.
Artículos relacionados: Problemas con los triggers en PLSQL.
47 comentarios:
Gracias Krdnas81, ten por seguro que seguiré actualizando este blog, aunque, como se dice por ahí, "sin prisa pero sin pausa".
Muchas gracias por la informacion, es de gra utilidad... de verdad muy agradecido y espero que sigas poniendo articulos tan interesantes.
Gracias.
Hola! me gustaría saber en que casos es mas efectivo o útil un trigger sobre sentencia que uno sobre columna?
Hola Alonso,
Si el resultado de la ejecución del trigger es el mismo, resulta más efectivo el trigger sobre sentencia (FOR EACH STATEMENT).
Por cierto, aprovecho para decir que en caso de que no se especifique si el trigger debe dispararse a nivel de registro (FOR EACH ROW, es decir, cada vez que un registro se ve afectado por el trigger), o si debe dispararse al final de la ejecución de la sentencia SQL (FOR EACH STATEMENT), entonces el interprete SQL toma como valor por defecto FOR EACH STATEMENT.
Hola es excelente que publiques informacion tan util, solo una pregunta, estoy trabajando con forms (Developer) y hago algo parecido al ejemplo que colocaste, solo que me da un error en las variables :NEW.variable, si sabes como puedo solucionar esto agradezco tu ayuda ;)
Hola laBeba, mi experiencia con Oracle Developers es muy limitada, pero los triggers de los formularios no tienen mucho que ver con los triggers de la base de datos.
saludos ;P me ha sido util :)
Los triggers son muy utiles cuando queremos guardar los log's de las transacciones. Pero cómo se puede hacer para guardar el usuario que realiza la transacción?. Cuando guardo el usuario me guarda siempres el mismo, ADMIN. Si sabes como puedo obtener el usuario, te lo agradezco
Hola Patov,
Normalmente debería funcionarte el utilizar la pseudo columna de la base de datos USER (SELECT user FROM dual).
Pero si la aplicación utiliza un esquema de autentificación específico, es posible que tengas que utilizar desde PL/SQL la función V('APP_USER').
Hola Pepelu:
Gracias por poner ejemplos en tu Blog, no se si podeis ayudarme en algo en que me he atorado, resulta que debo realizar algo asi como auditoria de tablas, mi problema es el siguiente:
- Que se dispare un trigger cuando actualice campos en una tabla X.
- Que la funcion principal del trigger inserte en una tabla Y lo siguiente: IDRegistro modificado en la tabla X, junto con Nombre_Campo_Modificado y Valor_Nuevo_de_dicho_Campo.
Se podrá, soy novata en esto del SQL, gracias Pepelu.
Hola Eudorax,
Se puede hacer lo que pides. Echa una ojeada a esta página:
http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11020
En dicha página incluso han construido un procedure para crear el trigger de auditoría pasando como parámetro el nombre de la tabla a auditar.
Magnifico Pepelu!!!
Muchisimas gracias el ejemplo está muy completo y justo a lo que necesito, y estamos en contacto por si tambien puedo aportar en algo.
Saludos a todos.
MUY BUEN BLOG. MUCHAS GRACIAS!
Hola!, te escibo desde Arg. y queria plantearte una duda que me surge con la utilizacion de triggers de tabla (for each statement).
Supongamos que declaramos un trigger fot each statement, al utilizar :new (u :old) ¿ a que tupla hara referencia ?, sabiendo que se pudieron modificar varias tuplas de la tabla.
Espero tu rta.
Si quieres me agregas a mi msn:
nicolasmiraccorucker@hotmail.com
Saludos.
Gorozito, cada vez que se actualiza un registro (o tupla) el trigger salta. En un UPDATE masivo, saltará para cada registro que sea actualizado, por lo tanto :new y :old hacen referencia al registro que se está actualizando en ese momento.
Hola Pepelu.
Soy nuevo con lo de los triggers e intento hacer uno, de manera que cuado se haga un UPDATE,INSERT o DELETE sobre un registro de la TablaX de la Instancia A, se actualice en la TablaZ de la Instancia B.
Hasta donde se, para realizar la conexión entre instancias se usa un DBLINK pero no se como.
Espero me puedas orientar de como hacerlo.
De antemano muchas gracias por tu atención y respuesta.
Saludos!!!
Hola Moshi,
Curiosamente la última entrada que he escrito en este blog tiene que ver con lo que me preguntas:
Acceso a bases de datos remotas mediante DBLink.
Échale una ojeada que seguro que tus dudas quedan resueltas.
Hola, no se mucho sobre triggers, y quisiera saber si en un triggers se puede cancelar la accion es decir que dada una condicion no inserte (si es BEFORE INSERT)
Hola Victoria, en un trigger PLSQL "BEFORE INSERT", puedes generar un error utilizando la función RAISE_APPLICATION_ERROR y se cancelaría la inserción. Pero claro, se haría un ROLLBACK de todos los cambios desde el último COMMIT.
Hola Papelu, la consulta es la siguiente y se debe a desconocimiento casi total del tema: necesitaría hacer un trigger por un insert (este se produce sólo si hubo un update de alguno de los campos) pero debería grabar en otra tabla sólo los campos que han sido modificados, se entiende? Espero tu respuesta.
Muchas gracias y Saludos!!
Hola Agustina, lo que pides se puede hacer con un trigguer pero no es un tema que pueda contestar en un comentario de forma rápida.
Echa un vistazo a esta página:
http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11020
Hola, me gustaria ejecutar un procedimiento desde un front-end y dejarlo desatendido, el procedimiento no devuelve ningun parametro, pero realiza una serie de acciones sobre diferentes tablas y tarda un rato. He visto que puedo utilizar el paquete de DBMS_SCHEDULER, pero necesita ponerle una repeticion. ¿Que puedo utilizar?
Muy util, he aprendido un poco mas sobre triggers en tu post!
saludos!
Maite,
Te aconsejo que eches un vistazo a este artículo del blog: El paquete PLSQL DBMS_SCHEDULER.
Un saludo,
Pepelu.
Hola, soy nueva en esto de los triggers, actualmente en mi empresa se esta ejecutando un trigger de la siguiente forma: create or replace TRIGGER "BILLING"."D_BILLING_COPY_TRG" AFTER INSERT ON MEDIATION.D_BILLING, este trigger a su vez hace una insercción a la tabla INSERT INTO MEDIATION.SETTLEMENT y este hace que se ejecute el siguiente trigger create or replace TRIGGER MEDIATION.TRG_SETTLEMENT
BEFORE INSERT
ON MEDIATION.SETTLEMENT, anteriormente este último trigger no existía y este a afectadoconciderablemente el performace, el segundo trigger tiene consultas sencillas e indexadas que no deben de afectar notablemente, algo que se hizo es que ambos triggers se ejecutaran al mismo tiempo es decir create or replace
TRIGGER "BILLING"."D_BILLING_COPY_TRG" AFTER INSERT ON MEDIATION.D_BILLING, como funciona en este caso en realidad los dos trigger se ejecutan al mismo tiempo o en forma secuencial? la insercción del siguiente registro se realiza hasta que los dos triggers se acaben de ejecutar?, me podrías pasar alguna liga donde pueda obter más información sobre trigger y optimizacion?, gracias de antemano.
Saludos
Hola Julieta,
La ejecución de los triggers es secuencial, primero saltaría el D_BILLING_COPY_TRG que haría saltar el TRG_SETTLEMENT, aunque obviamente el primer trigger no terminará de ejecutarse hasta que acabe el TRG_SETTLEMENT.
Si tienes problemas de rendimiento que antes no existían, yo revisaría el trigger nuevo. Hay veces que consultas sencillas dan problemas de rendimiento si el optimizador genera un plan de ejecución inadecuado.
Si crees que te puedo ayudar en algo puedes contactar conmigo por email (pepelu[arroba]gmail[punto]com).
Hola Papelu, muy bueno tu blog, estoy tratando de entender los triggers y poder realizarlos, vi el que comentaste http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11020 lo hice pero al ejecutar el procedimiento, me da este error:
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS
ORA-06512: at "SYS.DPR_AUDITTABLE", line 83
ORA-06512: at line 1
Y no se como solucionarlo. Muchas gracias Papelu!!!
Saludos a todos.
Nonstop, eso es simplemente un problema de permisos.
CREATE OR REPLACE TRIGGER consolidado
AFTER INSERT or UPTADE or DELETE on estudiantes
--creamos cursor
CURSOR mi_estudiante IS
select especialidad,count(creditos) total_creditos,sum(creditos) suma_creditos AS
from est estudiantes
group by especialidad;
begin
for xi IN mi_estudiante loop
update especialidad
set totalestudiantes := xi.total_creditos,
totalcreditos := xi.suma_creditos
WHERE especialidad = xi.especilidad
if sql%notfound then
INSERT INTO especialidad (especialidad,totalcreditos,totalestudiiante)
VALUES(xi.especialidad,xi.total_creditos,xi.suma_creditos);
END IF;
END loop;
end consolidado;
/
DISPARO INVALIDO ALGUNA SUGERENCIA
actualiza la tabla especialidad cuando haya un insert o alter o delete
Van, así a simple vista me parece que la definición del trigger es correcta, pero también te comento que los triggers no se deben utilizar para realizar el tipo de operación que estás haciendo con este trigger.
El motivo es que es muy probable que se produzcan problemas de rendimiento cuando alguien realice una operación sobre la tabla estudiante, ya que cualquier cambio sobre dicha tabla producirá una actualización global de la tabla especialidad.
Pienso que es mucho mejor definir una vista o una vista materializada en la que se realicen los cálculos que haces en el trigger, excluyendo las columnas totalestudiantes y totalcreditos de la tabla especialidad.
Hola oye tengo una duda acerca con esto y lo que pasa es que si yo tengo un trigger bien compilado y lo tengo al momento de insertar un registro, entonces cuando inserta el registro quiero que se duplique hacia otra tabla que esta en dblink pero si al momento de insertar no responde el dblink, el registro de la tabla local si se inserta verdad, osea que aunque falle el trigger, el registro si se inserta??
Hola Julius,
Una pregunta interesante. Basicamente dependerá de como manejes las excepciones dentro del trigger.
Si no utilizas excepciones y se produce un error al ejecutarse el trigger, no se realizará el INSERT en ninguna de las tablas, ni en la local, ni a la que se accede vía DBLINK.
Pero si añades esta excepción al final del trigger:
EXCEPTION
WHEN OTHERS THEN null;
O sea, no hacer nada si se produce un error dentro del trigger.
Entonces, si la conexión DBLINK falla, el INSERT si que se realizaría en la tabla local.
Hola la verdad tu blog esta excelente te felicito , estaba checando el ejemplo del TRIGGER y me marcaba error en esta linea-
WHEN (:new.salario > 5000);
lo que hice fue quitar los ":" que estan junto al new y jalo asi
WHEN (new.salario > 5000); , alomejor estoy equivocado o no se , pero solo como comentario saludos desde méxico.
diculpa también quite el ";" del final de la sentencia jeje así quedo.
WHEN (new.salary > 5000)
Hola disculpa que te moleste tengo una consulta estoy tratando de hacer un trigger para actualizar tendria que actualizar 3 tablas que estan relaciones, tengo que actualizar un stock segun lo modificado en una tabla de detallefactura, pero quiero guardar un valor antes de la actualizacion para actualizar correctamente el stock como lo haria porfavor es urgente please
Hola Mía, yo no utilizaría un trigger para hacer lo que me cuentas. Simplemente incluiría todas las actualizaciones en el procedimiento PL/SQL correspondientes.
Hola, buenas tardes, estoy con un inconveniente:
Tengo un trigger before insert anda update (sentencia).
El trigger verifica un hororia, y si si se encuentra en el horario permitido me permite hacer un insert or update. El problema es hace el INSERT y DEPUES SE DISPARA EL TRIGGER, por favor si alguien me ayuda con este ejercicio. Gracias.
******TRIGGER*************
CREATE OR REPLACE TRIGGER INSERT_UPDATE_SALES_ORDER
before INSERT OR UPDATE ON SALES_ORDER
BEGIN
if to_char(sysdate,'hh24') not between '09' and '17' then
if inserting then
RAISE_APPLICATION_ERROR(-20011,'FUERA DE HORARIO LABIRAL INSERT');
elsif updating then
RAISE_APPLICATION_ERROR(-20011,'FUERA DE HORARIO LABIRAL UPDATE');
end if;
end if;
exception
when others then
dbms_output.put_line(sqlerrm);
END;
******EJECUSION *****
set serveroutput on
insert into sales_order
values (204,'01/01/01',102,sysdate,null);
gracias, espero su respuesta!
Hola Pablo,
El trigger PL/SQL que has escrito funcionará correctamente cuando elimines del código la sección que maneja excepciones. Es decir, cuando elimines estas líneas de código:
exception
when others then
dbms_output.put_line(sqlerrm);
Con tu código lo que ocurre es que cuando entra en juego la función RAISE_APPLICATION_ERROR el trigger PLSQL pasa directamente a la sección EXCEPTION y como ésta no genera ningún error, el trigger no hace nada y los registros se insertan (INSERT) o modifican (UPDATE) sin ningún problema.
mi pregunta va por este tema, se esta migrando informacion de una tabla que se encuentra en un esquema(A) a otro esquema(B) que tambien se encuentra la tabla, al migrar del esquem(A) se tienen que borrar la informacion y asarlo al otro esquem(B). y por ende al borrar saltan los triggers, como puedo hacer para al momento de migrar la informacion los triggers no se vean afectados o no se disparen. me dijeron que se puede utilizar varibles globales por sesion. espero tu respuesta.
Hola Edwin,
Puedes deshabilitar todos los triggers asociados con una tabla utilizando el siguiente comando SQL:
ALTER TABLE [esquema(A).nombre_tabla] DISABLE ALL TRIGGERS;
Y al terminar la migración habilitarlos de nuevo:
ALTER TABLE [esquema(A).nombre_tabla] ENABLE ALL TRIGGERS;
No es recomendable realizar la deshabilitacion de los triggers ya que pueda odurrir que cualquier usuario este ingresando en el momento de la migracion y los triggers se encuentre disable.
aun que puedas planificar la migracionsiempre existe unmargen de error. gracias por tu respuesta
Hola Edwin, lo mejor y más recomendable en las migraciones de datos es hacerlas sin usuarios interactuando con el sistema.
Hola quiero que por favor me ayuden a resolver un script que no he podido terminar, soy nuevo en esto, tengo un dato de una tabla pero necesito que me arroje datos en dos campos, me estan pidiendo el ext_num_id pero tengo que ponerlo cuando es el padre y cuando es el hijo, lo trate de hacer con select anidado pero no me deja me vota el error ora 01427 por favor ayudenme a resolverlo.
select (select a.ext_num_id from num_history a, num_rel_history b
where B.PADRE_NUM_ID = SUBSTR(A.EXT_NUM_ID, 6,13)and b.relation = '2') as num_hijo,
(select a.ext_num_id from num_history a, num_rel_history b
where B.PADRE_NUM_ID = SUBSTR(A.EXT_NUM_ID, 6,13)and b.relation = '1') as num_padre,
A.INC_TYPE TIPO_INCIDENTE,
A.STATIC ESTADO,
A.STOP DETENCION,
A.CREATE FECHA_CREACION,
D.L_TEXT DISTRITO,
C.ASIG_TS ASIGNACION_RECURSO,
C.ASIG_NUM_TS MOVILIZACION_RECURSOS,
C.LLEGA_TS LLEGADA_RECURSOS,
A.CIERRA_TS CIERRE_INCIDENTE
FROM NUM_HISTORY A,
NUM_REL_HISTORY B,
INC_RES_HISTORY C,
TOTAL_DB_1 D
WHERE B.PADRE_NUM_ID = A.NUM_ID(+) AND
B.PADRE_NUM_ID = C.NUM_ID(+) AND
B.PADRE_NUM_ID = D.NUM_ID(+) AND
D.LOG_TYPO = 'ADDSRCH' AND
ORDER BY B.HIJO_NUM_ID, A.PADRET_NUM_ID;
Hola Claudia, la consulta que me haces tiene toda la pinta de ser un ejercicio de una clase de programación PLSQL, lo mejor en estos casos es que intentéis resolver el problema por vosotros mismo y después preguntéis al profesor o a algún compañero.
Tu consulta resulta evidente que no está bien construida porque cuando se usan SELECT anidados, los SELECT que están dentro del primer SELECT sólo deben devolver un registro y para mi resulta bastante claro que estos dos SELECT:
SELECT a.ext_num_id
FROM num_history a, num_rel_history b
WHERE b.padre_num_id = SUBSTR(a.ext_num_id,6,13)
AND b.relation = '2
SELECT a.ext_num_id
FROM num_history a, num_rel_history b
WHERE b.padre_num_id = SUBSTR(a.ext_num_id,6,13)
AND b.relation = '1'
Deben devolver múltiples registros.
En SELECT anidados, los segundos SELECT suelen incluir en el WHERE alguna condición utilizando algún campo de las tablas del primer SELECT. Por lo que creo que debes rediseñar tus SELECT anidados de una manera que será similar a la siguiente:
SELECT b2.ext_num_id
FROM num_rel_history b2
WHERE b2.padre_num_id = SUBSTR(a.ext_num_id,6,13)
AND b2.relation = '2
SELECT b2.ext_num_id
FROM num_rel_history b2
WHERE b2.padre_num_id = SUBSTR(a.ext_num_id,6,13)
AND b2.relation = '1'
Siendo la tabla "a" la del FROM del primer SELECT (NUM_HISTORY).
hola muy buenas noches a todos mi tema es como puedo realizar un un trigger en pl/sql de tabla digamos cliente que me permita registrar en una tabla auditoria_cliente todos los clientes que son eliminados..... por favor quien me pueda ayudar se lo agradeceria mucho para mi es muy urgente
si alguien puede decirme como quedaria construido seria de gran ayuda se me hace un poco dificil se los agradezco de ante mano y este tema esta full bueno
Hola a todos. A alguien le ha sucedido que de repente los triggers se desaparecen?
Gracias.
Publicar un comentario