Oracle

Triggers en PL/SQL

Por : Jhons_1101
2019-01-11
Tags :
Un activador de base de datos es un subprograma almacenado asociado con una tabla, vista o evento de base de datos.
El disparador se puede llamar una vez, cuando ocurre algún evento, o muchas veces. una vez por cada fila afectada por una instrucción INSERT, UPDATE o DELETE. Se puede llamar al activador después del evento, para grabarlo o realizar alguna acción de seguimiento.
O bien, se puede llamar al activador antes del evento para evitar operaciones erróneas o corregir nuevos datos para que cumpla con las reglas comerciales.
Los activadores de la base de datos (Triggers) se usan para registrar eventos de manera transparente. Supongamos que desea realizar un seguimiento de todas las inserciones en una tabla, incluso si el proceso de registro falla.
Debido a que este proceso es autónomo, el desencadenante puede confirmar cambios en la tabla de respaldo, ya sea que los cambios de apliquen o no en la tabla principal.
Es muy usada para registrar bitácoras de tablas principales, esto para conocer la historia de los registros de estas tablas.
compartir en facebook compartir en Google compartir en Twitter compartir en Blogger compartir como código embebido compartir la url
Ejemplo:
-- El activador es a nivel de tabla 'tbl_empleado', se activa cada vez que se actualizan los salarios en la tabla 'tbl_empleado'
Para cada actualización, el activador escribe un registro en la tabla tbl_auditar_salario.


CREATE TABLE emp_audit (
    id_cliente      NUMBER(6), 
    fec_actualiza   DATE, 
    new_salario     NUMBER(8,2), 
    old_salario     NUMBER(8,2)
);

CREATE OR REPLACE TRIGGER tbl_auditar_salario
   AFTER UPDATE OF salario ON tbl_empleado 
   FOR EACH ROW
BEGIN
    -- Miremos los prefijos OLD y NEW, estos automáticamente los puedo usar para
    -- tomar el valor del campo. Ejemplo: new.salario
    -- donde salario es un campo de la tabla tbl_empleado TBL_EMPLEADO.SALARIO
    INSERT INTO emp_audit VALUES( 
        :old.id_cliente, 
        SYSDATE, 
        :new.salario, 
        :old.salario
    );
END;


Cuando trabajamos con trigger a nivel de fila, Oracle provee de dos tablas temporales a las cuales se puede acceder, que contienen los antiguos y nuevos valores de los campos del registro afectado por la sentencia que disparó el trigger.
El nuevo valor es ":new" y el viejo valor es ":old". Para referirnos a ellos debemos especificar su campo separado por un punto ":new.CAMPO" y ":old.CAMPO".

El acceso a estos campos depende del evento del disparador.


  • En un trigger disparado por un "insert", se puede acceder al campo ":new" unicamente, el campo ":old" contiene "null".
  • En una inserción se puede emplear ":new" para escribir nuevos valores en las columnas de la tabla.
  • En un trigger que se dispara con "update", se puede acceder a ambos campos. En una actualizacion, se pueden comparar los valores de ":new" y ":old".
  • En un trigger de borrado, unicamente se puede acceder al campo "old", ya que el campo ":new" no existe luego que el registro es eliminado, el campo ":new" contiene "null" y no puede ser modificado.
  • Extraido de tutorialesprogramacionya.com



    Triggers after y before


    Oracle provee una manera de crear un activador ANTES DE INSERTAR en la tabla principal y también justo despues de ello. (insertar, actualizar o eliminar).

  • Triggers BEFORE INSERT

  • Un disparador ANTES DE INSERTAR significa que Oracle disparará este disparador antes de que se ejecute la operación INSERT.
        
        -- Sintaxis
        -----------------------------------------------
        CREATE [ OR REPLACE ] TRIGGER trigger_name
        BEFORE INSERT
        ON table_name
        [ FOR EACH ROW ]
    
        DECLARE
        -- variable declarations
    
        BEGIN
        -- trigger code
    
        EXCEPTION
        WHEN ...
        -- exception handling
        END;
        
        
    Parámetros o Argumentos
    OR REPLACE
    Opcional. Si se especifica, le permite volver a crear el activador si ya existe, de modo que puede cambiar la definición del activador sin emitir una declaración DROP TRIGGER .
    trigger_name
    El nombre del disparador a crear.
    BEFORE INSERT
    Indica que el disparador se disparará antes de que se ejecute la operación INSERTAR.
    table_name
    El nombre de la tabla en la que se crea el activador.


    Las restricciones
  • No puede crear un activador BEFORE de una vista.
  • Puede actualizar los :NEW valores.
  • No puede actualizar los valores :OLD.

  •     
        -- Ejemplo de trigger INSERT BEFORE
        CREATE OR REPLACE TRIGGER orders_before_insert
        BEFORE INSERT
        ON tbl_orders
        FOR EACH ROW
        DECLARE
        v_username varchar2(10);
        BEGIN
    
        -- buscamos el usuario del sistema que está tratando de INSERTAR en la tabla
        SELECT user INTO v_username FROM dual;
        :new.create_date := sysdate;
        :new.created_by  := v_username;
        END;
        
        

  • Triggers BEFORE UPDATE

  • Un disparador ANTES DE ACTUALIZAR significa que Oracle disparará este disparador antes de que se ejecute la operación ACTUALIZAR.
        
        -- Sintaxis
        -----------------------------------------------
        CREATE [ OR REPLACE ] TRIGGER trigger_name
        BEFORE UPDATE
        ON table_name
        [ FOR EACH ROW ]
    
        DECLARE
        -- variable declarations
        BEGIN
        -- trigger code
        EXCEPTION
        WHEN ...
        -- exception handling
        END;
        
        
    Parámetros o Argumentos
    OR REPLACE
    Opcional. Si se especifica, le permite volver a crear el activador si ya existe, de modo que puede cambiar la definición del activador sin emitir una declaración DROP TRIGGER .
    trigger_name
    El nombre del disparador a crear.
    BEFORE UPDATE
    Indica que el disparador se disparará antes de que se ejecute la operación ACTUALIZACIÓN.
    table_name
    El nombre de la tabla en la que se crea el activador.


    Las restricciones
  • No puede crear un activador BEFORE de una vista.
  • Puede actualizar los :NEW valores.
  • No puede actualizar los valores :OLD.

  •     
        -- Ejemplo de trigger UPDATE BEFORE
        CREATE OR REPLACE TRIGGER orders_before_update
        BEFORE UPDATE
        ON orders
        FOR EACH ROW
        DECLARE
        v_username varchar2(10);
        BEGIN
    
        SELECT user INTO v_username
        FROM dual;
        :new.updated_date  := sysdate;
        :new.updated_by    := v_username;
        END;
        
        


  • Triggers BEFORE DELETE

  • Un disparador ANTES DE ELIMINAR significa que Oracle disparará este disparador antes de que se ejecute la operación DELETE.
        
        -- Sintaxis
        -----------------------------------------------
        CREATE [ OR REPLACE ] TRIGGER trigger_name
        BEFORE DELETE
        ON table_name
        [ FOR EACH ROW ]
        DECLARE
        -- variable declarations
        BEGIN
        -- trigger code
        EXCEPTION
        WHEN ...
        -- exception handling
        END;
        
        
    Parámetros o Argumentos
    OR REPLACE
    Opcional. Si se especifica, le permite volver a crear el activador si ya existe, de modo que puede cambiar la definición del activador sin emitir una declaración DROP TRIGGER .
    trigger_name
    El nombre del disparador a crear.
    BEFORE DELETE
    Indica que el disparador se disparará antes de que se ejecute la operación DELETE.
    table_name
    El nombre de la tabla en la que se crea el activador.


    Las restricciones
  • No puede crear un activador BEFORE de una vista.
  • Puede actualizar los :NEW valores.
  • No puede actualizar los valores :OLD.

  •     
        -- Ejemplo de trigger DELETE BEFORE
        CREATE OR REPLACE TRIGGER orders_before_delete
        BEFORE DELETE
        ON orders
        FOR EACH ROW
        DECLARE
        v_username varchar2(10);
        BEGIN
    
        -- Encuentra el nombre de usuario de la persona que realiza el DLETE en la tabla
        SELECT user INTO v_username
        FROM dual;
    
        -- Inserta el registro en la tabla de auditoría
        INSERT INTO orders_audit
        (
            order_id,
            quantity,
            cost_per_item,
            total_cost,
            delete_date,
            deleted_by
        )
        VALUES
        ( 
            :old.order_id,
            :old.quantity,
            :old.cost_per_item,
            :old.total_cost,
            sysdate,
            v_username
        );
        END;
        
        




  • Triggers AFTER INSERT

  • Un disparador AFTER INSERT significa que Oracle disparará este disparador después de que se ejecute la operación INSERT.
        
        -- Sintaxis
        -----------------------------------------------
        CREATE [ OR REPLACE ] TRIGGER trigger_name
        AFTER INSERT
        ON table_name
        [ FOR EACH ROW ]
        DECLARE
        -- variable declarations
        BEGIN
        -- trigger code
        EXCEPTION
        WHEN ...
        -- exception handling
        END;
        
        
    Parámetros o Argumentos
    OR REPLACE
    Opcional. Si se especifica, le permite volver a crear el activador si ya existe, de modo que puede cambiar la definición del activador sin emitir una declaración DROP TRIGGER .
    trigger_name
    El nombre del disparador a crear.
    AFTER INSERT
    Indica que el disparador se disparará después de que se ejecute la operación INSERTAR.
    table_name
    El nombre de la tabla en la que se crea el activador.


    Las restricciones
  • No puede crear un activador AFTER de una vista.
  • No puede actualizar los :NEW valores.
  • No puede actualizar los valores :OLD.

  •     
        -- Ejemplo de trigger AFTER INSERT
        CREATE OR REPLACE TRIGGER tbl_auditar_salario
        AFTER INSERT OF salario ON tbl_empleado 
        FOR EACH ROW
        BEGIN
            INSERT INTO emp_audit VALUES( 
                :old.id_cliente, 
                SYSDATE, 
                :new.salario, 
                :old.salario
            );
        END;
        
        


  • Triggers AFTER UPDATE

  • Un Activador DESPUÉS DE ACTUALIZACIÓN significa que Oracle activará este activador después de que se ejecute la operación ACTUALIZACIÓN.
        
        -- Sintaxis
        -----------------------------------------------
        CREATE [ OR REPLACE ] TRIGGER trigger_name
        AFTER UPDATE
        ON table_name
        [ FOR EACH ROW ]
        DECLARE
        -- variable declarations
        BEGIN
        -- trigger code
        EXCEPTION
        WHEN ...
        -- exception handling
        END;
        
        
    Parámetros o Argumentos
    OR REPLACE
    Opcional. Si se especifica, le permite volver a crear el activador si ya existe, de modo que puede cambiar la definición del activador sin emitir una declaración DROP TRIGGER .
    trigger_name
    El nombre del disparador a crear.
    AFTER UPDATE
    Indica que el disparador se activará después de que se ejecute la operación ACTUALIZACIÓN.
    table_name
    El nombre de la tabla en la que se crea el activador.


    Las restricciones
  • No puede crear un activador AFTER de una vista.
  • No puede actualizar los :NEW valores.
  • No puede actualizar los valores :OLD.

  •     
        -- Ejemplo de trigger AFTER UPDATE
        CREATE OR REPLACE TRIGGER orders_after_update
        AFTER UPDATE
        ON orders
        FOR EACH ROW
        DECLARE
        v_username varchar2(10);
        BEGIN
    
        -- Find username of person performing UPDATE into table
        SELECT user INTO v_username
        FROM dual;
    
        -- Insert record into audit table
        INSERT INTO orders_audit
        (
            order_id,
            quantity_before,
            quantity_after,
            username
        )
        VALUES
        (
            :new.order_id,
            :old.quantity,
            :new.quantity,
            v_username
        );
        END;
        
        


  • Triggers AFTER DELETE

  • Un Activador AFTER DELETE significa que Oracle activará este disparador después de que se ejecute la operación DELETE.
        
        -- Sintaxis
        -----------------------------------------------
        CREATE [ OR REPLACE ] TRIGGER trigger_name
        AFTER DELETE
        ON table_name
        [ FOR EACH ROW ]
        DECLARE
        -- variable declarations
        BEGIN
        -- trigger code
        EXCEPTION
        WHEN ...
        -- exception handling
        END;
        
        
    Parámetros o Argumentos
    OR REPLACE
    Opcional. Si se especifica, le permite volver a crear el activador si ya existe, de modo que puede cambiar la definición del activador sin emitir una declaración DROP TRIGGER .
    trigger_name
    El nombre del disparador a crear.
    AFTER DELETE
    Indica que el disparador se disparará después de que se ejecute la operación DELETE.
    table_name
    El nombre de la tabla en la que se crea el activador.


    Las restricciones
  • No puede crear un activador AFTER de una vista.
  • No puede actualizar los :NEW valores.
  • No puede actualizar los valores :OLD.

  •     
        -- Ejemplo de trigger AFTER DELETE
        CREATE OR REPLACE TRIGGER tbl_auditar_salario
        AFTER DELETE OF salario ON tbl_empleado 
        FOR EACH ROW
        BEGIN
            INSERT INTO emp_audit VALUES( 
                :old.id_cliente, 
                SYSDATE, 
                :new.salario, 
                :old.salario
            );
        END;
        
        

    La parte ejecutable de un activador puede contener instrucciones de procedimiento así como instrucciones de manipulación de datos SQL.
    Además de los desencadenantes (trigger) a nivel de tabla, desencadenadores (trigger) a nivel de vistas y desencadenadores (trigger) de eventos.


    Datos importantes.
  • El valor de ":new" puede modificarse en un trigger before, es decir, se puede acceder a los nuevos valores antes que se ingresen en la tabla y cambiar los valores asignando a ":new.CAMPO" otro valor.
  • El valor de ":new" NO puede modificarse en un trigger after, esto es porque el trigger se activa luego que los valores de "new" se almacenaron en la tabla.
  • El campo ":old" nunca se modifica, sólo puede leerse.
  • Pueden usarse en una clásula "when".
  • En el cuerpo el trigger, los campos "old" y "new" deben estar precedidos por ":" (dos puntos), pero si está en "when" no.








  • Si este contenido te fue útil, no olvides compartirlo en redes sociales, Considéralo. Puede ser la manera de agradecer!


    Agrega tu comentario...

    Puedes utilizar etiquetas <pre></pre>, <p></p>, <div></div>, + (Nombre usuario, para responderle a alguien)

    W8aYBvn





    Este post no tiene comentarios, sé el primero en hacerlo
    Esta entrada no cuenta con imágenes adjuntas

    Unete al grupo de whatsApp +57 316 392 6456

    Sigue el grupo en facebook

    Siguenos.....