Clarion y SQL (VI)

[Mauricio]

Supongamos que tenemos que hacer un sistema de control de personal. En un nivel muy básico se me ocurren que necesitaremos, en principio, 2 tablas: una de empleados y una de categorías para cada empleado. Algo como esto:

Empleados1.PNG

La relación entre Categorías y Empleados es 1-M y también tenemos una recursiva en Empleados ya que un jefe puede tener varios empleados a su cargo. Al crear esta relación en el diagrama veremos esto:

Empleados2.PNG

Si prestan atención a las reglas de actualización y eliminación podrán ver que está seleccionado Sin Acción y no solo eso, sino que no se puede cambiar. En principio estaría bien, dado que el ID de un empleado no cambiará y entonces no sería necesario actualizar y por otro lado no sería lógico que si eliminamos un empleado con subordinados estos también sean borrados. Pero, siempre hay un pero, trabajamos en una empresa muy HDP y nuestro sistema debe contemplar que si despedimos un jefe, todos sus subordinados se van a dormir debajo de un puente con él. Bueno, este tipo de relaciones recursivas o cíclicas, MS SQL no las permite y no nos queda otra que buscar alternativas.

La más sencilla sería claudicar y dejarle a Clarion que se encargue de estos casos pero nuevamente nos estaríamos atando a él y por otro lado si queremos hacer algo por "afuera" tendríamos que asegurarnos de no dejar ningún cabo suelto (releyendo esto no sé si estoy escribiendo sobre programación o planeando un asesinato).

Dado que queremos dejarle todo el trabajo sucio a SQL la otra alternativa es escribir un par de triggers que se encarguen de todo. En realidad solo necesitaríamos el de borrado porque, como dije antes, el ID del empleado no cambiará pero me estaría contradiciendo con lo de hacer arreglos por afuera. Aún cuando el ID el usuario no lo ve siempre aparece alguien que te dice "ahh, no, empezó a contar desde 0 y yo quería que fuese desde 1.000.000, ¿no lo podés cambiar?".

Empecemos con el Trigger de actualización, que es el que pongo a continuación:

Código: Seleccionar todo

CREATE TRIGGER [dbo].[TR_Empleados_update]
ON [dbo].[Empleados]
AFTER UPDATE
AS
BEGIN

 IF UPDATE(EMP_ID)

   BEGIN

     UPDATE F
     SET F.EMP_PADRE=A.NuevoId
     FROM [dbo].[EMPLEADOS] F INNER JOIN (
     SELECT I.EMP_ID NuevoId,D.EMP_ID ViejoId
     FROM INSERTED I 
     INNER JOIN DELETED D ON I.EMP_ID=D.EMP_ID ) A
     ON A.ViejoId=F.EMP_PADRE
 
   END

END 

A primera vista parece complicado pero desmenuzándolo creo que lo entenderemos. Primero hay que tener en cuenta que en una actualización SQL maneja 2 tablas internas llamadas INSERTED (con el nuevo registro insertado) y DELETED (con el anterior) y que nosotros podemos acceder a ellas.

Código: Seleccionar todo

CREATE TRIGGER [dbo].[TR_Empleados_update]
ON [dbo].[Empleados]
AFTER UPDATE

es el nombre del Trigger, en la tabla Empleados y lo importante es que se ejecutará DESPUÉS (After) la actualización. Hay otro tipo de trigger que es INSTEAD OF (en lugar de) que se dispara ante una actualización.

Código: Seleccionar todo

 IF UPDATE(EMP_ID)

el trigger solo se ejecutará si el campo EMP_ID ha sido actualizado.

Código: Seleccionar todo

     UPDATE F
     SET F.EMP_PADRE=A.NuevoId
     FROM [dbo].[EMPLEADOS] F INNER JOIN (
     SELECT I.EMP_ID NuevoId,D.EMP_ID ViejoId
     FROM INSERTED I 
     INNER JOIN DELETED D ON I.EMP_ID=D.EMP_ID ) A
     ON A.ViejoId=F.EMP_PADRE

acá actualizamos la tabla F, que no es otra cosa que la misma tabla Empleados, seteando en el campo EMP_PADRE el nuevo valor de EMP_ID. Para eso fue necesario hacer un JOIN entre las tablas INSERTED y DELETED para obtener los valores nuevos y viejos.

Para el borrado, el trigger es el siguiente:

Código: Seleccionar todo

ALTER TRIGGER [dbo].[TR_Empleados_Delete]
ON [dbo].[Empleados]
AFTER DELETE
AS
BEGIN 
  DELETE F FROM [dbo].[EMPLEADOS] F
  INNER JOIN DELETED ON F.EMP_PADRE = DELETED.EMP_ID
END

Nuevamente lo que interesa es lo que está dentro del BEGIN y que no es más ni menos que borrar los registros que tienen como padre al empleado borrado.

Para que todo esto funcione es necesario borrar del diagrama la relación recursiva en la tabla empleados, quedando así:

Empleados3.PNG

[Aníbal Alarcón]

Muy bueno Mauricio.
Sería interesante tambien q' trates el manejo de transacciones en otro post.
Gracias