none
Guardar cambios (update) de una tabla en otra tabla en SQL Server 2012 RRS feed

  • Pregunta

  • Buenos días, verán tengo una tabla de Empleados en la cuál se registran los datos del alta del empleado, pero como sabemos, en todo lugar hay bajas y hay reingresos todo el tiempo; entonces lo que estoy buscando es que cuando mi personal de de baja a un empleado marcándolo como inactivo e ingresando la fecha de baja se genere un registro; pero del mismo modo que cuando ese mismo empleado vuelva a laborar con nosotros y le den su reingreso marcándolo como activo, agregándole su nueva fecha de ingreso y obviamente limpiando la fecha de baja se genere un nuevo registro; esto obviamente en otra tabla que tengo creada con el nombre EmpleadoLog en donde tenga la información para consultarla.

    Les comento lo que tengo y lo que he intentado:

    Tengo mi tabla de Empleados:

    create table EMPLEADOS(
    CLAVE varchar(20),
    NOMBRE varchar(200),
    DPTO varchar(20),
    PUESTO varchar(20),
    FECHA_INGRESO datetime,
    FECHA_BAJA datetime,
    ACTIVO VARCHAR(1)
    constraint PK_secciones primary key(CLAVE)
    );

    Creé una tabla de EmpleadosLog de la siguiente forma:

    CREATE TABLE EMPLEADOSLOG(
    ID int identity,
    CLAVE varchar(20),
    DPTO varchar(20) NULL,
    PUESTO varchar(20) NULL,
    FECHA_ANTIGUA datetime NULL,
    FECHA_BAJA datetime NULL,
    FECHA_REINGRESO datetime NULL,
    ACTIVO VARCHAR(1) NULL,
    );

    En donde FECHA_ANTIGUA sería la primer FECHA_INGRESO que tuvo el Empleado cuando se dio de alta por primera vez.

    Ahora, lo que intenté fue con un Trigger de la siguiente forma:

    go
    create trigger EMPLEADOS_UPDATE on EMPLEADOS
    after update
    as
    insert into EMPLEADOSLOG (CLAVE, DPTO, PUESTO,FECHA_BAJA,FECHA_REINGRESO,ACTIVO) 
    select CLAVE, DPTO,PUESTO,FECHA_BAJA,GETDATE(),ACTIVO
    from deleted
    go

    Pero en cualquier edición me guarda cambios y no es lo que busco, intenté con otro trigger de la siguiente forma:

    go
    create trigger RHEMPLEADO_UPDATE on RHEMPLEADO
    after update
    as
    insert into EMPLEADOSLOG 
    	(CLAVE,
    	DPTO,
    	PUESTO,
    	FECHA_ANTIGUA,
    	FECHA_BAJA,
    	FECHA_REINGRESO,
    	ACTIVO) 
    select 
    	CLAVE,
    	DPTO,
    	PUESTO,
    	FECHA_INGRESO,
    	FECHA_BAJA,
    	FECHA_REINGRESO=
    		CASE
    			WHEN ACTIVO='I' AND (FECHA_INGRESO=FECHA_INGRESO) THEN NULL
    			WHEN ACTIVO='A' AND (FECHA_BAJA=NULL) THEN GETDATE()
    		END,
    	ACTIVO
    from deleted
    go

    Verán, tengo el siguiente registro:

    CLAVE	NOMBRE			DPTO	PUESTO	FECHA_INGRESO		FECHA_BAJA	ACTIVO
    1199	JUAREZ NEPOMUCENO JAIR	2	106	2020-12-01 00:00:00.000	NULL		A

    En donde hice un CASE (Creo que lo formulé mal) en donde:

    En donde si el el usuario cambia a Inactivo (Activo='I') y la Fecha de Ingreso se mantiene igual (ya que cuando se da de baja la fecha de ingreso queda igual) en Fecha_Reingreso quede registro nulo, ya que el Empleado no está reingresando a laborar.

    En donde si el el usuario cambia a Activo (Activo='A') y la Fecha de Ingreso es diferente (ya que aquí sí cambia porque hay un reingreso) en Fecha_Reingreso agregue la fecha actual del movimiento.

    Pero al momento de hacer un UPDATE de la siguiente forma sí genera un registro:

    update EMPLEADOS set ACTIVO='I',FECHA_BAJA=CAST('2020-12-02' AS date) where CLAVE='1199'

    Se realiza el Update en la tabla pero en EmpleadosLog me genera el siguiente registro solamente:

    ID	CLAVE	DPTO	PUESTO	FECHA_ANTIGUA		FECHA_BAJA	FECHA_REINGRESO	ACTIVO
    1	1199	2	106	2020-12-01 00:00:00.000	NULL		NULL		A

    Como vemos, la FECHA_BAJA no se guarda, al igual que Activo no cambió de A por I.

    Ahora, si realizo un nuevo UPDATE para "darle reingreso" a dicho Empleado de la siguiente forma:

    update EMPLEADOS set ACTIVO='A',FECHA_INGRESO=CAST('2020-12-03' AS DATE),FECHA_BAJA=NULL where CLAVE='1199'

    Se realiza el Update en la tabla pero en EmpleadosLog me genera el siguiente registro:

    ID	CLAVE	DPTO	PUESTO	FECHA_ANTIGUA		FECHA_BAJA		FECHA_REINGRESO	ACTIVO
    2	1199	2	106	2020-12-01 00:00:00.000	2020-12-02 00:00:00.000	NULL		I

    Ahora la vemos que la FECHA_BAJA me guarda la que habíamos puesto con anterioridad, pero en FECHA_INGRESO no guarda nada y en ACTIVO me pone el estatus anterior.

    No se si estoy haciendo mal el código o estoy entendiéndolo mal.

    ¿Creen que puedan ayudarme?


    martes, 22 de diciembre de 2020 17:34

Respuestas

  • Hola alex0991:

    Primero el escenario. Como en la primera entrada utilizaste empleados, yo sigo con ello. Me elimino primero las tablas para no tener rastros.

    Drop table if exists dbo.EMPLEADOS;
    create table EMPLEADOS(
    CLAVE varchar(20),
    NOMBRE varchar(200),
    DPTO varchar(20),
    PUESTO varchar(20),
    FECHA_INGRESO datetime,
    FECHA_BAJA datetime,
    ACTIVO VARCHAR(1)
    constraint PK_secciones primary key(CLAVE)
    );
    go
    Drop table if exists dbo.EMPLEADOSLOG;
    CREATE TABLE EMPLEADOSLOG(
    ID int identity,
    CLAVE varchar(20),
    DPTO varchar(20) NULL,
    PUESTO varchar(20) NULL,
    FECHA_ANTIGUA datetime NULL,
    FECHA_BAJA datetime NULL,
    FECHA_REINGRESO datetime NULL,
    ACTIVO VARCHAR(1) NULL,
    );
    GO

    Ahora, creo el trigger, del que luego comentamos.

    Create or alter trigger Empleado_Update on dbo.Empleados
    After update
    As
    Begin
    	Set noCount ON 
    	if (UPDATE(Fecha_ingreso))
    	Begin
    	-- solo lo hacemos cuando se modifica el campo fecha_ingreso.
    
    		Insert into dbo.EMPLEADOSLOG (CLAVE, DPTO, PUESTO, FECHA_ANTIGUA, FECHA_BAJA, FECHA_REINGRESO, ACTIVO)
    		Select i.CLAVE, i.DPTO, i.PUESTO, d.FECHA_INGRESO, 
    			case when I.ACTIVO = 'A' then d.FECHA_BAJA else I.FECHA_INGRESO End,
    			case when i.ACTIVO = 'I' then NULL ELSE i.FECHA_INGRESO END,
    			i.ACTIVO
    			from inserted i inner join deleted d on i.clave = d.clave;
    
    		-- FÍJATE en la columna Fecha de baja, que cogemos el dato que tenía justo antes la columna Fecha_Ingreso
    		-- Fíjate en la columna Fecha_ingreso que condicionamos lo que insertamos, en función de lo que tenga la columna
    		-- Activo 
    
    	End
    
    End

    Con el trigger creado. Inserto dos registros, porque siempre hay que pensar en conjuntos no en filas. Las fechas en format yyyyMMdd para no tener nunca errores.

    Insert into dbo.EMPLEADOS (
    CLAVE,NOMBRE,DPTO,PUESTO,FECHA_INGRESO,FECHA_BAJA,ACTIVO
    )
    values
    ('1199','JUAREZ NEPOMUCENO JAIR',	'2','106',	'20201201 00:00:00.000', NULL,'A'),
    ('1198','OTRO',	'2','106',	'20201201 00:00:00.000', NULL,'A');
    GO

    Ahora sigo tús updates. Ojo hay alguna divergencia, pero con la explicación es suficiente creo, porque me pones en la segunda update la fecha el día 3 y el resultado me pintas el 7 y eso me genera dudas sobre que columna quieres coger, pero te explico el como, y la solución es muy simple.

    update dbo.Empleados
    	set ACTIVO='I',FECHA_INGRESO=CAST('2020-12-01' AS DATE),
    		FECHA_BAJA=CAST('2020-12-02' AS DATE) 
    		where CLAVE='1199';
    
    update dbo.Empleados 
    	set ACTIVO='A',FECHA_INGRESO=CAST('2020-12-03' AS DATE),FECHA_BAJA=NULL 
    		where CLAVE='1199';

    Updateamos los dos registros.

    Además si cambiamos cualquier otra columna que no sea fecha ingreso.

    Update dbo.EMPLEADOS set NOMBRE = NOMBRE + '_Old';

    Ahora el como se hace.

    En los triggers hay dos pseudoTablas Inserted y deleted, que tienen la misma estructura que el registro que estás modificando, pero deleted tiene los valores de justo antes de la update, e inserted tiene los que son cambiados. 

    Por tanto si updateas una fecha deleted tiene la fecha antigua e inserted tiene la nueva.

    Ademas Update(columna) no es una sentencia de updateo, sino una función que devuelve los registros que se han modificado la columna, por eso en la segunda update donde le pongo a ambos registros un old en el nombre estos no pasan al log.

    Luego puedes para jugar con los valores utilizar un case en cada columna escogiendo la expresión que quieres analizar, y el valor que quieres retornar.

    Otra opción en vez de utilizar update, es utilizar un where d.Fecha_Ingreso <> i.Fecha_ingreso siempre que estos no sean null, porque sino sería una evaluación mal hecho.

    Auditoria de Tabla con triggers

    https://javifer2.wordpress.com/2019/10/07/auditoria-de-tabla-con-triggers/

    • Marcado como respuesta Alejandro 0991 martes, 22 de diciembre de 2020 21:20
    martes, 22 de diciembre de 2020 21:02

Todas las respuestas

  • Hola alex0991:

    Solo por si del enunciado no lo expresas del todo claro. 

    Las pseudo tablas inserted y deleted en un trigger de update, contienen los valores para cada fila actualizada, donde inserted tiene los valores que se están cambiando, y deleted los que habría si no se realiza el cambio.

    Luego en los triggers, lo que tienes que jugar no es con el contenido del registro completo, sino establecer solo cuando se modifique la columna x.

    Puedes hacer un simple if (Update(columna))

    Insert ......

    También puedes saberlo relacionando conjuntos.

    Select * from inserted i inner join delete d on i.columna <> d.columna 

    con esto tienes que se ha modificado la columna x (salvo que haya null).

    Trigger update

    https://javifer2.wordpress.com/2020/04/15/trigger-que-es-como-se-hace-y-como-funciona-after-for-update/

    martes, 22 de diciembre de 2020 18:33
  • Hola Javi Fernández, gracias por tu tiempo.

    Ok intentaré explicarme un poco más. Lo que busco es que, cuando uno de mis usuarios reactiven a un Empleado como si fuera un ingreso nuevo, pero que está marcado de Inactivo (Activo='I') y lo pasen a Activo (Activo='A') se guarde un registro en la tabla EmpleadosLog.

    Ejemplo como desde cero:

    Primero uno de mis usuarios dió de baja a un Empleado y queda su información así:

    update RHEMPLEADO set ACTIVO='I',FECHA_INGRESO=CAST('2020-12-01' AS DATE),FECHA_BAJA=CAST('2020-12-02' AS DATE) where CLAVE='1199'

    CLAVE NOMBRE DPTO PUESTO FECHA_INGRESO FECHA_BAJA ACTIVO 1199 JUAREZ NEPOMUCENO JAIR 2 106 2020-12-01 00:00:00.000 2020-12-02 00:00:00.000 I

    Aquí vemos que el Empleado está dado de baja el día 02/12/2020 y que había entrado el día 01/12/2020 y por ende está Inactivo (I).

    Ok, ahora, digamos que ese mismo Empleado regresó a trabajar con nosotros el día 07/12/2020 y por ende es una nueva Fecha_Ingreso (que aquí sería un Reingreso) entonces la información quedaría algo así:

    update RHEMPLEADO set ACTIVO='A',FECHA_INGRESO=CAST('2020-12-03' AS DATE),FECHA_BAJA=NULL where CLAVE='1199'

    CLAVE NOMBRE DPTO PUESTO FECHA_INGRESO FECHA_BAJA ACTIVO 1199 JUAREZ NEPOMUCENO JAIR 2 106 2020-12-07 00:00:00.000 NULL I

    Lo que yo quisiera saber (porque creo que yo estoy entendiendo mal el código del trigger) es si en otra tabla pudiera tener la siguiente información algo así (tomando en cuenta ambos movimientos de updates anteriores):

    ID   CLAVE  DPTO  PUESTO   FECHA_ANTIGUA	      FECHA_BAJA	        FECHA_REINGRESO	      ACTIVO
    1 1199 2 106 2020-12-01 00:00:00.000 2020-12-02 00:00:00.000 NULL I
    2 1199 2 106 2020-12-01 00:00:00.000 2020-12-02 00:00:00.000 2020-12-07 00:00:00.000 A

    Y así tendría ambos registros en la tabla EmpleadosLog en donde la FECHA_REINGRESO sería la nueva Fecha_Ingreso.

    No se si me di a entender un poco mejor.

    Espero puedan apoyarme o guiarme, porque estoy viendo que estoy entendiendo mal la cuestión del trigger.

    Muchas gracias por todo.

    saludos.

    martes, 22 de diciembre de 2020 19:17
  • Hola alex0991:

    Primero el escenario. Como en la primera entrada utilizaste empleados, yo sigo con ello. Me elimino primero las tablas para no tener rastros.

    Drop table if exists dbo.EMPLEADOS;
    create table EMPLEADOS(
    CLAVE varchar(20),
    NOMBRE varchar(200),
    DPTO varchar(20),
    PUESTO varchar(20),
    FECHA_INGRESO datetime,
    FECHA_BAJA datetime,
    ACTIVO VARCHAR(1)
    constraint PK_secciones primary key(CLAVE)
    );
    go
    Drop table if exists dbo.EMPLEADOSLOG;
    CREATE TABLE EMPLEADOSLOG(
    ID int identity,
    CLAVE varchar(20),
    DPTO varchar(20) NULL,
    PUESTO varchar(20) NULL,
    FECHA_ANTIGUA datetime NULL,
    FECHA_BAJA datetime NULL,
    FECHA_REINGRESO datetime NULL,
    ACTIVO VARCHAR(1) NULL,
    );
    GO

    Ahora, creo el trigger, del que luego comentamos.

    Create or alter trigger Empleado_Update on dbo.Empleados
    After update
    As
    Begin
    	Set noCount ON 
    	if (UPDATE(Fecha_ingreso))
    	Begin
    	-- solo lo hacemos cuando se modifica el campo fecha_ingreso.
    
    		Insert into dbo.EMPLEADOSLOG (CLAVE, DPTO, PUESTO, FECHA_ANTIGUA, FECHA_BAJA, FECHA_REINGRESO, ACTIVO)
    		Select i.CLAVE, i.DPTO, i.PUESTO, d.FECHA_INGRESO, 
    			case when I.ACTIVO = 'A' then d.FECHA_BAJA else I.FECHA_INGRESO End,
    			case when i.ACTIVO = 'I' then NULL ELSE i.FECHA_INGRESO END,
    			i.ACTIVO
    			from inserted i inner join deleted d on i.clave = d.clave;
    
    		-- FÍJATE en la columna Fecha de baja, que cogemos el dato que tenía justo antes la columna Fecha_Ingreso
    		-- Fíjate en la columna Fecha_ingreso que condicionamos lo que insertamos, en función de lo que tenga la columna
    		-- Activo 
    
    	End
    
    End

    Con el trigger creado. Inserto dos registros, porque siempre hay que pensar en conjuntos no en filas. Las fechas en format yyyyMMdd para no tener nunca errores.

    Insert into dbo.EMPLEADOS (
    CLAVE,NOMBRE,DPTO,PUESTO,FECHA_INGRESO,FECHA_BAJA,ACTIVO
    )
    values
    ('1199','JUAREZ NEPOMUCENO JAIR',	'2','106',	'20201201 00:00:00.000', NULL,'A'),
    ('1198','OTRO',	'2','106',	'20201201 00:00:00.000', NULL,'A');
    GO

    Ahora sigo tús updates. Ojo hay alguna divergencia, pero con la explicación es suficiente creo, porque me pones en la segunda update la fecha el día 3 y el resultado me pintas el 7 y eso me genera dudas sobre que columna quieres coger, pero te explico el como, y la solución es muy simple.

    update dbo.Empleados
    	set ACTIVO='I',FECHA_INGRESO=CAST('2020-12-01' AS DATE),
    		FECHA_BAJA=CAST('2020-12-02' AS DATE) 
    		where CLAVE='1199';
    
    update dbo.Empleados 
    	set ACTIVO='A',FECHA_INGRESO=CAST('2020-12-03' AS DATE),FECHA_BAJA=NULL 
    		where CLAVE='1199';

    Updateamos los dos registros.

    Además si cambiamos cualquier otra columna que no sea fecha ingreso.

    Update dbo.EMPLEADOS set NOMBRE = NOMBRE + '_Old';

    Ahora el como se hace.

    En los triggers hay dos pseudoTablas Inserted y deleted, que tienen la misma estructura que el registro que estás modificando, pero deleted tiene los valores de justo antes de la update, e inserted tiene los que son cambiados. 

    Por tanto si updateas una fecha deleted tiene la fecha antigua e inserted tiene la nueva.

    Ademas Update(columna) no es una sentencia de updateo, sino una función que devuelve los registros que se han modificado la columna, por eso en la segunda update donde le pongo a ambos registros un old en el nombre estos no pasan al log.

    Luego puedes para jugar con los valores utilizar un case en cada columna escogiendo la expresión que quieres analizar, y el valor que quieres retornar.

    Otra opción en vez de utilizar update, es utilizar un where d.Fecha_Ingreso <> i.Fecha_ingreso siempre que estos no sean null, porque sino sería una evaluación mal hecho.

    Auditoria de Tabla con triggers

    https://javifer2.wordpress.com/2019/10/07/auditoria-de-tabla-con-triggers/

    • Marcado como respuesta Alejandro 0991 martes, 22 de diciembre de 2020 21:20
    martes, 22 de diciembre de 2020 21:02
  • Hola Javier Fernández.

    Muchas gracias :D deja reviso con mi código y en un ratito te digo cómo me fue :D pero hasta donde revisé junto con los ejemplos creo que es lo que ando buscando :D solo déjame hacer las pruebas y te aviso :D

    Muchísimas gracias en vdd por tu tiempo :D

    Saludos.

    martes, 22 de diciembre de 2020 21:10
  • Hola alex0991:

    Primero el escenario. Como en la primera entrada utilizaste empleados, yo sigo con ello. Me elimino primero las tablas para no tener rastros.

    Drop table if exists dbo.EMPLEADOS;
    create table EMPLEADOS(
    CLAVE varchar(20),
    NOMBRE varchar(200),
    DPTO varchar(20),
    PUESTO varchar(20),
    FECHA_INGRESO datetime,
    FECHA_BAJA datetime,
    ACTIVO VARCHAR(1)
    constraint PK_secciones primary key(CLAVE)
    );
    go
    Drop table if exists dbo.EMPLEADOSLOG;
    CREATE TABLE EMPLEADOSLOG(
    ID int identity,
    CLAVE varchar(20),
    DPTO varchar(20) NULL,
    PUESTO varchar(20) NULL,
    FECHA_ANTIGUA datetime NULL,
    FECHA_BAJA datetime NULL,
    FECHA_REINGRESO datetime NULL,
    ACTIVO VARCHAR(1) NULL,
    );
    GO

    Ahora, creo el trigger, del que luego comentamos.

    Create or alter trigger Empleado_Update on dbo.Empleados
    After update
    As
    Begin
    	Set noCount ON 
    	if (UPDATE(Fecha_ingreso))
    	Begin
    	-- solo lo hacemos cuando se modifica el campo fecha_ingreso.
    
    		Insert into dbo.EMPLEADOSLOG (CLAVE, DPTO, PUESTO, FECHA_ANTIGUA, FECHA_BAJA, FECHA_REINGRESO, ACTIVO)
    		Select i.CLAVE, i.DPTO, i.PUESTO, d.FECHA_INGRESO, 
    			case when I.ACTIVO = 'A' then d.FECHA_BAJA else I.FECHA_INGRESO End,
    			case when i.ACTIVO = 'I' then NULL ELSE i.FECHA_INGRESO END,
    			i.ACTIVO
    			from inserted i inner join deleted d on i.clave = d.clave;
    
    		-- FÍJATE en la columna Fecha de baja, que cogemos el dato que tenía justo antes la columna Fecha_Ingreso
    		-- Fíjate en la columna Fecha_ingreso que condicionamos lo que insertamos, en función de lo que tenga la columna
    		-- Activo 
    
    	End
    
    End

    Con el trigger creado. Inserto dos registros, porque siempre hay que pensar en conjuntos no en filas. Las fechas en format yyyyMMdd para no tener nunca errores.

    Insert into dbo.EMPLEADOS (
    CLAVE,NOMBRE,DPTO,PUESTO,FECHA_INGRESO,FECHA_BAJA,ACTIVO
    )
    values
    ('1199','JUAREZ NEPOMUCENO JAIR',	'2','106',	'20201201 00:00:00.000', NULL,'A'),
    ('1198','OTRO',	'2','106',	'20201201 00:00:00.000', NULL,'A');
    GO

    Ahora sigo tús updates. Ojo hay alguna divergencia, pero con la explicación es suficiente creo, porque me pones en la segunda update la fecha el día 3 y el resultado me pintas el 7 y eso me genera dudas sobre que columna quieres coger, pero te explico el como, y la solución es muy simple.

    update dbo.Empleados
    	set ACTIVO='I',FECHA_INGRESO=CAST('2020-12-01' AS DATE),
    		FECHA_BAJA=CAST('2020-12-02' AS DATE) 
    		where CLAVE='1199';
    
    update dbo.Empleados 
    	set ACTIVO='A',FECHA_INGRESO=CAST('2020-12-03' AS DATE),FECHA_BAJA=NULL 
    		where CLAVE='1199';

    Updateamos los dos registros.

    Además si cambiamos cualquier otra columna que no sea fecha ingreso.

    Update dbo.EMPLEADOS set NOMBRE = NOMBRE + '_Old';

    Ahora el como se hace.

    En los triggers hay dos pseudoTablas Inserted y deleted, que tienen la misma estructura que el registro que estás modificando, pero deleted tiene los valores de justo antes de la update, e inserted tiene los que son cambiados. 

    Por tanto si updateas una fecha deleted tiene la fecha antigua e inserted tiene la nueva.

    Ademas Update(columna) no es una sentencia de updateo, sino una función que devuelve los registros que se han modificado la columna, por eso en la segunda update donde le pongo a ambos registros un old en el nombre estos no pasan al log.

    Luego puedes para jugar con los valores utilizar un case en cada columna escogiendo la expresión que quieres analizar, y el valor que quieres retornar.

    Otra opción en vez de utilizar update, es utilizar un where d.Fecha_Ingreso <> i.Fecha_ingreso siempre que estos no sean null, porque sino sería una evaluación mal hecho.

    Auditoria de Tabla con triggers

    https://javifer2.wordpress.com/2019/10/07/auditoria-de-tabla-con-triggers/

    Efectivamente :D

    Solamente me guarda los registros de la actualización cuando se cambia la fecha de Ingreso a otra diferente.

    Creo que con eso me funcionaría para lo que necesito :)

    Muchísimas gracias :D

    Edito:

    Solamente cambié la línea del if dejándolo así:

    if (UPDATE(Fecha_ingreso) or UPDATE(activo) or UPDATE(fecha_baja))

    Y así ahora sí me registra los 2 cambios, cuando se da de baja al Empleado y cuando se Reactiva.

    Muchas gracias :D

    • Editado Alejandro 0991 martes, 22 de diciembre de 2020 22:45 Quedó solucionado :D
    martes, 22 de diciembre de 2020 22:35