Procedimiento almacenado:



    Al crear un procedimiento almacenado, las instrucciones que contiene se analizan para verificar si son correctas sintácticamente. Si se encuentra algún error, el procedimiento se compila, pero aparece un mensaje con advertencias que indica tal situación.

Un procedimiento almacenado se invoca llamándolo.

       En primer lugar se deben escribir y probar las instrucciones que se incluyen en el procedimiento almacenado, luego, si se obtiene el resultado esperado, se crea el procedimiento.

          Los procedimientos almacenados pueden hacer referencia a tablas, vistas, a funciones definidas por el usuario, a otros procedimientos almacenados.

       Para crear un procedimiento almacenado se emplea la instrucción "create procedure". La sintaxis básica es:

create or replace procedure NOMBREPROCEDIMIENTO
as
begin
INSTRUCCIONES
end;

       El bloque de instrucciones comienza luego de "begin" y acaba con "end".

     Si se utiliza "or replace", se reemplaza un procedimiento existente; si se omite y existe un procedimiento con ese nombre, Oracle mostrará un mensaje de error.

          Para diferenciar los procedimientos almacenados del sistema de los procedimientos almacenados creados por el usuario use un prefijo, por ejemplo "pa_nombreprocedimiento".

   La sintaxis para ejecutar un procedimiento almacenado es:

Execute NOMBREPROCEDIMIENTO;

       Los procedimientos almacenados se eliminan con "drop procedure".

drop procedure NOMBREPROCEDIMIENTO;

      Si el procedimiento que se quiere eliminar no existe, aparece un mensaje de error que indicara tal situación.
Se puede eliminar una tabla referenciada en un procedimiento almacenado, Oracle lo permite, pero luego, al ejecutar el procedimiento, aparecerá un mensaje de error porque la tabla referenciada no existe.

         Si al crear un procedimiento almacenado se coloca "create or replace procedure", el nuevo procedimiento reemplaza al anterior.

         Los procedimientos almacenados son objetos, así que para obtener información de ellos pueden consultarse los siguientes diccionarios.

 "user_objects": muestra todos los objetos de la base de datos seleccionada, incluidos los procedimientos. En la columna "object_type" aparece "procedure" si es un procedimiento almacenado.

    En el siguiente ejemplo se solicita todos los objetos que son procedimientos:

select *from user_objects where object_type='PROCEDURE';​

 "user_procedures": muestra todos los procedimientos almacenados de la base de datos actual.

         En el siguiente ejemplo se solicita información de todos los procedimientos que comienzan con "PA":

select * from user_procedures where object_name like 'PA_%';



Disparadores (Trigger):



      Si se intenta modificar (agregar, actualizar o eliminar) datos de una tabla asociada a un disparador, el disparador se ejecuta (se dispara) en forma automática.



        La diferencia con los procedimientos almacenados del sistema es que los triggers:

 No pueden ser invocados directamente; al intentar modificar los datos de una tabla asociada a un disparador, el disparador se ejecuta automáticamente.

 No reciben y retornan parámetros.
 Son apropiados para mantener la integridad de los datos, no para obtener resultados de consultas.

create or replace trigger NOMBREDISPARADOR
MOMENTO-- before, after o instead of
EVENTO-- insert, update o delete
of CAMPOS-- solo para update
on NOMBRETABLA
NIVEL--puede ser a nivel de sentencia (statement) o de fila (for each row)
when CONDICION--opcional
begin
CUERPO DEL DISPARADOR--sentencias
end NOMBREDISPARADOR;

     Los triggers se crean con la instrucción "create trigger" seguido del nombre del disparador. Si se agrega "or replace" al momento de crearlo y ya existe un trigger con el mismo nombre, tal disparador será borrado y vuelto a crear.

         "MOMENTO" indica donde se disparará el trigger en relación al evento, puede ser BEFORE (antes), AFTER (después) o INSTEAD OF (en lugar de). "before" significa que el disparador se activará antes que se ejecute la operación (insert, update o delete) sobre la tabla, que causó el disparo del mismo. "after" significa que el trigger se activará después que se ejecute la operación que causó el disparo. "instead of" sólo puede definirse sobre vistas, anula la sentencia disparadora, se ejecuta en lugar de tal sentencia (ni antes ni después).

       "EVENTO" especifica la operación (acción, tipo de modificación) que causa que el trigger se dispare (se active), puede ser "insert", "update" o "delete"; Debe colocarse una acción, puede ser más de una, en tal caso se separan con "or". Si "update" lleva una lista de atributos, el trigger sólo se ejecuta si se actualiza algún atributo de la lista.

     "on NOMBRETABLA" indica la tabla (o vista) asociada al disparador;

       "NIVEL" puede ser a nivel de sentencia o de fila. "for each row" indica que el trigger es a nivel de fila, es decir, se activa una vez por cada registro afectado por la operación sobre la tabla, donde una sola operación afecta a varios registros. Los triggers a nivel de sentencia, se activan una sola vez (antes o después de ejecutar la operación sobre la tabla). Si no se especifica, o se especifica "statement", es a nivel de sentencia.

        "CUERPO DEL DISPARADOR" son las acciones que se ejecutan al dispararse el trigger, las condiciones que determinan un intento de inserción, actualización o borrado provoca las acciones que el trigger realizará. El bloque se delimita con "begin... end".

      Entonces, un disparador es un bloque de código asociado a una tabla que se dispara automáticamente antes o después de una sentencia "insert", "update" o "delete" sobre la tabla.

    Se crean con la instrucción "create trigger"se especifica el momento en que se disparará, qué evento lo desencadenará (inserción, actualización o borrado), sobre qué tabla (o vista) y las instrucciones que se ejecutarán.

    Los disparadores pueden clasificarse según tres parámetros:

 el momento en que se dispara: si se ejecutan antes (before) o después (after) de la sentencia.
 el evento que los dispara: insert, update o delete, según se ejecute una de estas sentencias sobre la tabla.

 nivel: depende si se ejecuta para cada fila afectada en la sentencia (por cada fila) o bien una única vez por sentencia independientemente de la filas afectadas (nivel de sentencia).

Consideraciones generales:
      o Las siguientes instrucciones no están permitidas en un desencadenador: create database, alter database, drop database, load database, restore database, load log, reconfigure, restore log, disk init, disk resize.

      o Se pueden crear varios triggers para cada evento, es decir, para cada tipo de modificación (inserción, actualización o borrado) para una misma tabla. Por ejemplo, se puede crear un "insert trigger" para una tabla que ya tiene otro "insert trigger".

Disparador (información)
    Los triggers son objetos, así que para obtener información de ellos pueden consultarse los siguientes diccionarios:

 "user_objects": muestra todos los objetos de la base de datos seleccionada, incluidos los triggers. En la columna "object_type" aparece "trigger" si es un disparador. En el siguiente ejemplo se solicitan todos los objetos que son disparadores:

select *from user_objects where object_type='TRIGGER';

 "user_triggers": muestra todos los triggers de la base de datos actual. Muestra el nombre del desencadenador (trigger_name), si es before o after y si es a nivel de sentencia o por fila (trigger_type), el evento que lo desencadena (triggering_event), a qué objeto está asociado, si tabla o vista (base_object_type), el nombre de la tabla al que está asociado (table_name), los campos, si hay referencias, el estado, la descripción, el cuerpo (trigger_body), etc. En el siguiente ejemplo se solicita información de todos los disparadores que comienzan con "TR":

select trigger_name, triggering_event from user_triggers where trigger_name like 'TR%';

 "user_source": se puede visualizar el código fuente almacenado en un disparador. En el siguiente ejemplo se solicita el código fuente del objeto "TR_insertar_libros":

select *from user_source where name='TR_INSERTAR_LIBROS';

Disparador de inserción a nivel de sentencia
       Dijimos que un disparador está asociado a una tabla y a una operación específica (inserción, actualización o borrado).

       A continuación se verá la creación de un disparador para el evento de inserción: "insert triger".

     La siguiente es la sintaxis para crear un trigger de inserción que se dispare cada vez que se ejecute una sentencia "insert" sobre la tabla especificada, es decir, cada vez que se intenten ingresar datos en la tabla:

create or replace trigger NOMBREDISPARADOR
MOMENTO insert
on NOMBRETABLA
begin
CUERPO DEL TRIGGER;
end NOMBREDISPARADOR;

        Disparador de insercion a nivel de fila (insert trigger for each row)

         Vimos la creación de un disparador para el evento de inserción a nivel de sentencia, es decir, se dispara una vez por cada sentencia "insert" sobre la tabla asociada.

       En caso que una sola sentencia "insert" ingrese varios registros en la tabla asociada, el trigger se disparará una sola vez; si se quire que se active una vez por cada registro afectado, se debe indicar con "for each row".

         La siguiente es la sintaxis para crear un trigger de inserción a nivel de fila, se dispare una vez por cada fila ingresada sobre la tabla especificada:

create or replace trigger NOMBREDISPARADOR
MOMENTO insert
on NOMBRETABLA
for each row
begin
CUERPO DEL TRIGGER;
end NOMBREDISPARADOR;

        Disparador de borrado (nivel de sentencia y de fila)
Dijimos que un disparador está asociado a una tabla y a una operación específica (inserción, actualización o borrado).

        La siguiente es la sintaxis para crear un trigger de eliminación que se dispare cada vez que se ejecute una sentencia "delete" sobre la tabla especificada, es decir, cada vez que se eliminen registros de la tabla:

create or replace trigger NOMBREDISPARADOR
MOMENTO delete
on NOMBRETABLA
NIVEL-- statement o for each row
begin
CUERPO DEL TRIGGER;
end NOMBREDISPARADOR;

  
          Luego de la instrucción "create trigger" o "create or replace trigger" se coloca el nombre del disparador. 

       "MOMENTO" indica donde se disparará el trigger en relación al evento, puede ser BEFORE (antes) o AFTER (después). Se especifica el evento que causa que el trigger se dispare, en este caso "delete", ya que el trigger se activará cada vez que se ejecute la sentencia "delete" sobre la tabla especificada luego de "on".

         En "NIVEL" se especifica si será un trigger a nivel de sentencia (se dispara una sola vez por cada sentencia "delete", aunque la sentencia elimine varios registros) o a nivel de fila (se dispara tantas veces como filas se eliminan en la sentencia "delete").

        Finalmente se coloca el cuerpo del tigger dentro del bloque "begin.. end".

        Se crea un desencadenador a nivel de fila que se dispara cada vez que se ejecuta un "delete" sobre la tabla "libros", en el cuerpo del trigger se especifican las acciones, en este caso, por cada fila eliminada de la tabla "libros", se ingresa un registro en "control" con el nombre del usuario que realizó la eliminación y la fecha.

Disparador de actualización - lista de campos (update trigger)
 

          El trigger de actualización (a nivel de sentencia o de  fila) permite incluir una lista de campos. Si se incluye el nombre de un campo (o varios) luego de "update", el trigger se disparará donde alguno de esos campos (incluidos en la lista) sea actualizado. Si se omite la lista de campos, el trigger se dispara al momento en que cualquier campo de la tabla asociada sea modificado, es decir, por defecto toma todos los campos de la tabla.

        La lista de campos solamente puede especificarse en disparadores de actualización, nunca en disparadores de inserción o borrado.

create or replace trigger NOMBREDISPARADOR
MOMENTO update of CAMPOS
on TABLA
NIVEL--statement o for each row
begin
CUERPODEL DISPARADOR;
end NOMBREDISPARADOR;

       "CAMPOS" son los campos de la tabla asociada que activarán el trigger si son modificados. Pueden incluirse más de uno, en tal caso, se separan con comas.

Disparador de actualizacion a nivel de sentencia (update trigger)
Dijimos que un disparador está asociado a una tabla y a una operación específica (inserción, actualización o borrado).

     La siguiente es la sintaxis para crear un trigger de  actualización a nivel de sentencia, que se dispare cada vez que se ejecute una sentencia "update" sobre la tabla especificada, es decir, cada vez que se intenten modificar datos en la tabla:

create or replace trigger NOMBREDISPARADOR
MOMENTO update
on NOMBRETABLA
statement
begin
CUERPO DEL TRIGGER;
end NOMBREDISPARADOR;

       Luego de la instrucción "create trigger" se coloca el nombre del disparador. Si se agrega "or replace" al momento de crearlo y ya existe un trigger con el mismo nombre, tal disparador será borrado y vuelto a crear.

      "MOMENTO" indica si se disparará el trigger en relación al evento, puede ser BEFORE (antes) o AFTER (después). Se especifica el evento que causa que el trigger se dispare, en este caso "update", ya que el trigger se activará cada vez que se ejecute la sentencia "update" sobre la tabla especificada luego de "on" "statement" significa que es un trigger a nivel de sentencia, es decir, se dispara una sola vez por cada sentencia "update", aunque la sentencia modifique varios registros; como en cualquier trigger, es el valor por defecto si no se especifica.

         Finalmente se coloca el cuerpo del tigger dentro del bloque "begin.. end".

Disparador de múltiples eventos
Un trigger puede definirse sobre más de un evento; en tal caso se separan con "or".
create or replace trigger NOMBREDISPARADOR
MOMENTO-- before, after o instead of
of CAMPO--si alguno de los eventos es update
EVENTOS-- insert, update y/o delete
on NOMBRETABLA
NIVEL--puede ser a nivel de sentencia (statement) o de fila (for each row)
begin
CUERPO DEL DISPARADOR--sentencias
end NOMBREDISPARADOR;

​

        Si el trigger se define para más de un evento desencadenante, en el cuerpo del mismo se puede emplear un condicional para controlar cuál operación disparó el trigger. Esto permite ejecutar bloques de código según la clase de acción que disparó el desencadenador.

         Para identificar el tipo de operación que disparó el trigger se emplea "inserting", "updating" y "deleting".
Disparador (eliminar)

      Si se elimina una tabla, se eliminan todos los triggers establecidos sobre ella. Para eliminar un trigger se emplea la siguiente sentencia:

drop trigger NOMBRETRIGGER;