29 de enero de 2015

26 de enero de 2015

Triggers en SQL

Ejemplo utilizando la table INSERTED

CREATE TRIGGER TR_CUENTAS
ON CUENTAS
AFTER UPDATE

AS

BEGIN
-- SET NOCOUNT ON impide que se generen mensajes de texto
-- con cada instrucción
SET NOCOUNT ON;
INSERT INTO HCO_SALDOS
(IDCUENTA, SALDO, FXSALDO)
SELECT IDCUENTA, SALDO, getdate()
FROM INSERTED
END

Ejemplo utilizando dos tablas, una de ellas es la tabla INSERTED

USE Blogger
    GO

    CREATE TRIGGER ActualizaVentasEmpleados
    ON pedidos FOR INSERT

    AS

    UPDATE  empleados SET ventas=ventas+inserted.importe
    FROM  empleados, inserted
    WHERE  numemp=inserted.rep;

    GO

Una consideración a tener en cuenta es que el trigger se ejecutará aunque la instruccion DML (UPDATE, INSERT o DELETE ) no haya afectado a ninguna fila. En este caso inserted y deleted devolverán un conjunto de datos vacío.

Podemos especificar a que columnas de la tabla debe afectar el trigger.

ALTER TRIGGER TR_CUENTAS
ON CUENTAS
AFTER UPDATE

AS
BEGIN
-- SET NOCOUNT ON impide que se generen mensajes de texto
-- con cada instrucción
SET NOCOUNT ON;

IF UPDATE(SALDO) -- Solo si se actualiza SALDO
BEGIN
INSERT INTO HCO_SALDOS
(IDCUENTA, SALDO, FXSALDO)
SELECT IDCUENTA, SALDO, getdate()
FROM INSERTED
END
END

 Los trigger están dentro de la transacción original (Insert, Delete o Update) por lo cual si dentro de nuestro trigger hacemos un RollBack Tran, no solo estaremos echando atrás nuestro trigger sino también toda la transacción; en otras palabras si en un trigger ponemos un RollBack Tran, la transacción de Insert, Delete o Update volverá toda hacia atrás.

ALTER TRIGGER TR_CUENTAS
ON CUENTAS
AFTER UPDATE

AS
BEGIN
-- SET NOCOUNT ON impide que se generen mensajes de texto
-- con cada instrucción
SET NOCOUNT ON;
INSERT INTO HCO_SALDOS
(IDCUENTA, SALDO, FXSALDO)
SELECT IDCUENTA, SALDO, getdate()
FROM INSERTED

ROLLBACK
END

En este caso obtendremos el siguiente mensaje de error:
La transacción terminó en el desencadenador. Se anuló el lote.

Ejemplo de After Insert

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]
FOR INSERT
AS
       declare @empid int;
       declare @empname varchar(100);
       declare @empsal decimal(10,2);
       declare @audit_action varchar(100);

       select @empid=i.Emp_ID from inserted i;
       select @empname=i.Emp_Name from inserted i;   
       select @empsal=i.Emp_Sal from inserted i;     
       set @audit_action='Inserted Record -- After Insert Trigger.';

       insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
       values(@empid,@empname,@empsal,@audit_action,getdate());

       PRINT 'AFTER INSERT trigger fired.'
GO


Ejemplo de After Update

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]
FOR UPDATE
AS
       declare @empid int;
       declare @empname varchar(100);
       declare @empsal decimal(10,2);
       declare @audit_action varchar(100);

       select @empid=i.Emp_ID from inserted i;
       select @empname=i.Emp_Name from inserted i;   
       select @empsal=i.Emp_Sal from inserted i;     
      
       if update(Emp_Name)
             set @audit_action='Updated Record -- After Update Trigger.';
       if update(Emp_Sal)
             set @audit_action='Updated Record -- After Update Trigger.';

       insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
       values(@empid,@empname,@empsal,@audit_action,getdate());

       PRINT 'AFTER UPDATE Trigger fired.'
GO


Ejemplo de After Delete

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]
AFTER DELETE
AS
       declare @empid int;
       declare @empname varchar(100);
       declare @empsal decimal(10,2);
       declare @audit_action varchar(100);

       select @empid=d.Emp_ID from deleted d; 
       select @empname=d.Emp_Name from deleted d;    
       select @empsal=d.Emp_Sal from deleted d;
       set @audit_action='Deleted -- After Delete Trigger.';

       insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
       values(@empid,@empname,@empsal,@audit_action,getdate());

       PRINT 'AFTER DELETE TRIGGER fired.'
GO


Ejemplo Instead Of

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]
INSTEAD OF DELETE
AS
       declare @emp_id int;
       declare @emp_name varchar(100);
       declare @emp_sal int;
      
       select @emp_id=d.Emp_ID from deleted d;
       select @emp_name=d.Emp_Name from deleted d;
       select @emp_sal=d.Emp_Sal from deleted d;

       BEGIN
             if(@emp_sal>1200)
             begin
                    RAISERROR('Cannot delete where salary > 1200',16,1);
                    ROLLBACK;
             end
             else
             begin
                    delete from Employee_Test where Emp_ID=@emp_id;
                    COMMIT;
             insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
                    values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
                    PRINT 'Record Deleted -- Instead Of Delete Trigger.'
             end
       END
GO


Otro ejemplo de trigger Instead Of

create TRIGGER actualizar
    on usuarios
    Instead Of update
    AS
        IF update (descripcion)
        BEGIN
    SET NOCOUNT ON;
            RAISERROR ('Imposible actualizar los registros',16, 1)
    ROLLBACK TRAN
        END
 


Trigger DDL
Los trigger DDL se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL.
La siguiente instrucción impide que se ejecuten sentencias DROP TABLE y ALTER TABLE en la base de datos.
CREATE TRIGGER TR_SEGURIDAD
ON DATABASE FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
RAISERROR ('No está permitido borrar ni modificar tablas !' , 16, 1)
ROLLBACK TRANSACTION
END

Otro ejemplo
CREATE TRIGGER SEGURIDAD
    ON DATABASE FOR Create_table,DROP_TABLE, ALTER_TABLE
    AS
    BEGIN
   RAISERROR ('Imposible crear, borrar ni modificar tablas,Faltan permisos' , 16, 1)
    ROLLBACK TRANSACTION
    INSERT Auditoria VALUES ('','',GetDate())
    END

Deshabilitar Triggers

En ocasiones puede ser útil inhabilitar temporalmente un desencadenador sin que por ello suponga eliminarlo, para estos casos podemos utilizar la sentencia DISABLE TRIGGER.

Ejemplo:

DISABLE TRIGGER  ActualizaVentasEmpleado ON pedidos;

Deshabilita todos los desencadenadores asociados a la tabla pedidos.
DISABLE TRIGGER ALL ON pedidos;
   
Deshabilita todos los desencadenadores definidos en la base de datos actual.
DISABLE TRIGGER ALL ON DATABASE; 

Deshabilita todos los desencadenadores definidos en el servidor.
DISABLE TRIGGER ALL ON ALL SERVER;


C# - Oracle: Bloques Anonimos

Los bloques PL/SQL son unidades de código que se envían al servidor para que sean procesadas conjuntamente, este tipo de subprogramas son menos conocidos que los procedimientos almacenados, funciones y triggers, pero son enormemente útiles.

La estructura de un bloque dentro de un procedimiento:

PROCEDURE nombreProcedimiento
IS
[DECLARE]
BEGIN
  -- instrucciones
[EXCEPTION]
END;

La estructura de un bloque dentro de una función:

FUNCTION nombreFuncion
RETURN tipodedato
IS
[DECLARE]
BEGIN
  -- instrucciones
  RETURN valor;
[EXCEPTION]
END;

El siguiente es un ejemplo simple de bloque anónimo

DECLARE
  v_apellido empleados.apellido%TYPE;

BEGIN
  SELECT apellido
    INTO v_apellido
    FROM empleados
   WHERE id_empleado = 138;

  DBMS_OUTPUT.put_line(v_apellido);
END;

Queremos mostrar los datos de un empleado cuyo DNI sea 23564390, del cual se tiene que  mostrar nombre y el apellido, además que utiliza excepciones

DECLARE
v_nom   empleados.nombre%type;
v_ape    empleados.apellido%type;

BEGIN
Select nombre,apellido
into v_nombre,v_ape
from Empleados
where dni=23564390;

Dbms_output.put_line('Nombre: '||v_nombre|| 'Apellido: '||v_apellido );
                      
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Dbms_output.put_line('No  existe empleado con ese DNI');

   WHEN OTHERS THEN
   Dbms_output.put_line('otros errores');

 END;


Ejemplo 2

En este ejemplo se utiliza la tabla DUAL

SET SERVEROUTPUT ON;

DECLARE
  A VARCHAR(10) := '';

BEGIN
  SELECT TO_CHAR(SYSDATE) INTO A FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('LA FECHA ACTUAL ES : ' || A);

EXCEPTION
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('HOLA');
END;


Ejemplo 3

En este ejemplo se utiliza & para que aparezca un popup que pide el valor al usuario

DECLARE
V_DEPTO  EMP.DEPTNO%TYPE;
V_ENAME  EMP.ENAME%TYPE;

BEGIN
SELECT ENAME INTO V_ENAME
FROM EMP
WHERE SAL=(SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO=&DEPTNO);

DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;

Ejemplo 4

Las transacciones son un conjuntos de operaciones en la base de datos, que se tratan como una unidad. Se realizan todas o se descartan todas. Para confirmar las transacciones pendientes se utiliza la sentencia COMMIT y para deshacer las transacciones pendientes se realizaría con ROLLBACK.

Ejemplo de manejo de transacciones:

DECLARE
    vValor NUMBER := 100;
    vOrigen VARCHAR2(1) :='A';
    vDestino VARCHAR2(1) := 'B';

BEGIN
    UPDATE tabla SET valor = vValor
     WHERE origen = vOrigen;

    INSERT INTO tabla2 (Origen, Destino, valor, FECHA)
                VALUES (vOrigen, vDestino, vValor, SYSDATE);

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error en la transaccion:'||SQLERRM);
        dbms_output.put_line('Se deshacen las modificaciones);
        ROLLBACK;
END;


Ejemplo 5

En este ejemplo se utiliza & para pedir el dato al usuario

DECLARE
                d_producto  producto.id_producto%type

BEGIN
                SELECT   id_producto
                               INTO  d_producto
                               FROM  producto
                               WHERE  id_producto = &id_inventario;
                DELETE  FROM   inventario
                               WHERE  id_inventario = d_producto;
         COMMIT ;

EXCEPTION
                WHEN OTHERS THEN
                               ROLLBACK ;
                               INSERT INTO exception_table (message) VALUES (‘ocurrió un error ’) ;
                               COMMIT ;
END ;

Ejemplo 6

En este ejemplo se utiliza un cursor

DECLARE
CURSOR CUR4 IS

SELECT ENAME, DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
V_N VARCHAR2(20);
V_D VARCHAR2(20);

BEGIN
OPEN CUR4;
LOOP
FETCH CUR4 INTO V_N,V_D;
EXIT WHEN CUR4%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_N||'*'||'ESTA EN ELDEPNO'||V_D);
END LOOP
CLOSE;
END;

Ejemplo 7

SQL%ROWCOUNT es un atributo especial de cursor que retorna el número de tuplas modificadas por la última sentencia DML ejecutada en nuestra sesión.

• Modificar todos los empleados en el departamento 10 con un 20% de incremento salarial.

DECLARE
  v_id_departamento empleados.id_departamento%TYPE := 10;

BEGIN
  UPDATE empleados
    SET salario = salario * 1.2
   WHERE id_departamento = v_id_departamento;

  DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;

Insertar un nuevo empleado en la tabla.

BEGIN
  INSERT INTO empleados (id_empleado
                       , apellido
                       , id_departamento
                       , salario)
       VALUES (100, 'Feuerstein' , 10, 200000);

  DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;


Ejemplo 8

Bloques anidados

DECLARE
   l_mensaje1  VARCHAR2(100) := 'Hola';

BEGIN
   DECLARE
      l_mensaje2  VARCHAR2(5);
   BEGIN
      l_mensaje2 := ' Mundo!';
      DBMS_OUTPUT.put_line(l_mensaje1 || l_mensaje2);
  
EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
   END;
   DBMS_OUTPUT.put_line(l_mensaje1);
END;


Ejemplo 9

Son elementos, para poder gestionar los valores devueltos por una consulta a la base de datos.
Se pueden distinguir dos tipos:

Cursores implícitos: Se utilizan, cuando una consulta devuelve un registro
Cursores explícitos: Son controlados por el programador y suelen ser más eficientes

Ejemplo cursor implícito:

declare
    vprovincia VARCHAR2(50);

begin
    SELECT DESCRIPCION INTO vprovincia
      FROM PROVINCIAS
     WHERE CO_PROVINCIA = '47';

    dbms_output.put_line('La provincia es: ' || vprovincia);
end;


Ejemplo cursor explícito:

DECLARE
    CURSOR cprovincias IS
        SELECT CO_PROVINCIA, DESCRIPCION
          FROM PROVINCIAS;

    CO_PROVINCIA VARCHAR2(3);
    descripcion VARCHAR2(50);

BEGIN
    OPEN cprovincias;
    FETCH cprovincias INTO CO_PROVINCIA,descripcion;
    WHILE cprovincias %found LOOP
        dbms_output.put_line(descripcion);
        FETCH cprovincias INTO CO_PROVINCIA,descripcion;
    END LOOP;
    CLOSE cprovincias;
END;

Crear un bloque que calcula el importe de la comisión de un empleado determinado, basándose en el salario del empleado.
a. Utilice el comando DEFINE para proporcionar la identificación de empleado.
DEFINE p_empno = 100
b. Si el sueldo del empleado es menor de $ 5,000, mostrar la cantidad de la bonificación para el empleado el 10% del sueldo.
c. Si el sueldo del empleado es de entre $ 5,000 y $ 10,000, aparecen con la cantidad de bonificación para el empleado el 15% del sueldo.
d. Si el salario del trabajador supera los $ 10.000, mostrar la cantidad de la bonificación para el empleado como 20% del sueldo.
e. Si el sueldo del empleado es NULL, la pantalla del importe de la bonificación para el empleado como 0.
f. Pruebe el bloque PL / SQL para cada caso con los casos de prueba siguiente, y comprobar cada bono de cantidad.

set serveroutput on
define p_empno =100;
declare
sal number;
cursor salarios is
select salary
from employees;
salario employees.salary%type:='&p_empno';
begin
open salarios;
fetch salarios into salario;
if
salario < 5000 then
sal:=(salario * 0.1);
elsif salario < 5000 and salario >=1000 then
sal:= salario * 0.15;
elsif salario > 1000 then
sal:= salario * 0.20;
elsif salario is null then
sal:= 0;
end if;
dbms_output.put_line (salario|| '      ' ||sal);
close salarios;
end;


Crear una tabla EMP que es una réplica de la tabla EMPLEADOS. Agregar una nueva columna, ESTRELLAS, de VARCHAR2 tipo de datos y la
duración del 50 al EMP tabla para almacenar asterisco (*).

create table EMP as
 select * from emp;
commit;
alter table emp
add  (ESTRELLAS varchar2(50));

desc emp;