ALTER TRIGGER trinsinf_soc_trabajador on dbo.inf_soc_trabajador 
FOR INSERT, UPDATE 
NOT FOR REPLICATION 
AS 
/**********************************************************/ 
/* Nombre fisico: t0057.tri */ 
/* Autor: Fernando Diaz Medina */ 
/* Fecha: 26/Dic/96 */ 
/* Proyecto: ADAMv3 */ 
/**********************************************************/ 
-- Reprogramado por JCL en Julio 14/06 
-- DATA TECNOLOGIA LTDA.
DECLARE 
    @i_flag_int TINYINT, 
    @i_dato_int DECIMAL(19,6), 
    @i_flag_char TINYINT, 
    @i_dato_char VARCHAR(255), 
    @i_flag_date TINYINT, 
    @i_dato_date SMALLDATETIME, 
    @i_flag_number TINYINT, 
    @i_dato_number DECIMAL(19,6), 
    @i_fin TINYINT, 
    @i_trabajador CHAR(10), 
    @i_status_char TINYINT, 
    @i_status_int TINYINT, 
    @i_status_number TINYINT, 
    @i_status_date TINYINT, 
    @i_indice CHAR(10), 
    @i_dato_01 VARCHAR(255), 
    @i_dato_02 VARCHAR(255), 
    @i_dato_03 VARCHAR(255), 
    @i_dato_04 VARCHAR(255), 
    @i_dato_05 VARCHAR(255), 
    @i_dato_06 VARCHAR(255), 
    @i_dato_07 VARCHAR(255), 
    @i_dato_08 VARCHAR(255), 
    @i_dato_09 VARCHAR(255), 
    @i_dato_10 VARCHAR(255), 
    @i_dato_11 DECIMAL(19,6), 
    @i_dato_12 DECIMAL(19,6), 
    @i_dato_13 DECIMAL(19,6), 
    @i_dato_14 DECIMAL(19,6), 
    @i_dato_15 DECIMAL(19,6), 
    @i_dato_16 DECIMAL(19,6), 
    @i_dato_17 DECIMAL(19,6), 
    @i_dato_18 DECIMAL(19,6), 
    @i_dato_19 DECIMAL(19,6), 
    @i_dato_20 DECIMAL(19,6), 
    @i_dato_21 SMALLDATETIME, 
    @i_dato_22 SMALLDATETIME, 
    @i_dato_23 SMALLDATETIME, 
    @i_dato_24 SMALLDATETIME, 
    @i_dato_25 SMALLDATETIME, 
    @d_dato_01 VARCHAR(255), 
    @d_dato_02 VARCHAR(255), 
    @d_dato_03 VARCHAR(255), 
    @d_dato_04 VARCHAR(255), 
    @d_dato_05 VARCHAR(255), 
    @d_dato_06 VARCHAR(255), 
    @d_dato_07 VARCHAR(255), 
    @d_dato_08 VARCHAR(255), 
    @d_dato_09 VARCHAR(255), 
    @d_dato_10 VARCHAR(255), 
    @d_dato_11 DECIMAL(19,6), 
    @d_dato_12 DECIMAL(19,6), 
    @d_dato_13 DECIMAL(19,6), 
    @d_dato_14 DECIMAL(19,6), 
    @d_dato_15 DECIMAL(19,6), 
    @d_dato_16 DECIMAL(19,6), 
    @d_dato_17 DECIMAL(19,6), 
    @d_dato_18 DECIMAL(19,6), 
    @d_dato_19 DECIMAL(19,6), 
    @d_dato_20 DECIMAL(19,6), 
    @d_dato_21 SMALLDATETIME, 
    @d_dato_22 SMALLDATETIME, 
    @d_dato_23 SMALLDATETIME, 
    @d_dato_24 SMALLDATETIME, 
    @d_dato_25 SMALLDATETIME, 
    @i_fecha SMALLDATETIME 
 
IF @@rowcount = 0 
    RETURN
 
/* Existencia de campos foráneos */ 
IF NOT EXISTS(SELECT 1 FROM trabajadores t INNER JOIN inserted i ON t.trabajador = i.trabajador)
BEGIN
    RAISERROR ('No existe el trabajador especificado [trabajadores]', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
END
IF NOT EXISTS(SELECT 1 FROM indices_inf_soc x INNER JOIN inserted i ON x.indice_inf_soc = i.indice_inf_soc)
BEGIN
    RAISERROR ('No existe el índice de inf. socioeconómica especificado [inf_soc_trabajador]', 16, 1)
    ROLLBACK TRANSACTION 
    RETURN 
END 

/* Seguridad por Clase de Nómina */ 
IF EXISTS(SELECT 1 FROM inserted i INNER JOIN trabajadores_grales tg ON tg.trabajador = i.trabajador
          WHERE tg.clase_nomina IS NOT NULL
            AND NOT EXISTS(SELECT 1 FROM rel_usu_clase_nom r WHERE tg.clase_nomina = r.clase_nomina AND r.usuario = system_user))
BEGIN 
    RAISERROR ('El usuario no tiene autoridad para la clase de nómina a la que pertenece el trabajador [inf_soc_trabajador]', 16, 1)
    ROLLBACK TRANSACTION 
    RETURN 
END
 
/* Seguridad por Apartado */
IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN trabajadores_grales tg ON tg.trabajador = i.trabajador
              INNER JOIN aut_apartados a ON a.compania = tg.compania
              WHERE a.usuario = system_user and a.num_apartado = 8 and a.nivel_seguridad > 1)
BEGIN 
    RAISERROR ('El usuario no tiene autoridad para modificar información socioeconómica en esta compañía', 16, 1)
    ROLLBACK TRANSACTION 
    RETURN 
END 
 
/* Seguridad por Agrupaciones de trabajador */ 
IF EXISTS
(SELECT 1 FROM inserted i
 INNER JOIN rel_trab_agr rta ON i.trabajador = rta.trabajador 
 INNER JOIN rel_agr_companias rac ON rta.compania = rac.compania AND rac.agrupacion = rta.agrupacion
 INNER JOIN rel_usu_agr_trab ruat ON rta.agrupacion = ruat.agrupacion AND rta.dato = ruat.dato
 WHERE ruat.usuario = system_user ) 
BEGIN 
    RAISERROR ('El usuario no tiene autoridad para el dato de agrupación al que pertenece el trabajador [inf_soc_trabajador]', 16, 1)
    ROLLBACK TRANSACTION 
    RETURN 
END 
 
/**** Si tipo_indice = 3 (Interpersonal) ****/  
/**** Verifica que exista el trabajador = la Persona Relacionada ****/ 
IF EXISTS
(SELECT 1 FROM inserted 
 WHERE (trabajador_persona IS NULL and persona_relacionada IS NOT NULL) 
    OR (trabajador_persona IS NOT NULL and persona_relacionada IS NULL)) 
BEGIN 
    RAISERROR ('No se ha especificado trabajador_persona o bien persona_relacionada. [inf_soc_trabajador]', 16, 1)
    ROLLBACK TRANSACTION 
    RETURN 
END 
 
IF EXISTS
(SELECT 1 FROM inserted i
 INNER JOIN indices_inf_soc iis ON i.indice_inf_soc = iis.indice_inf_soc
 WHERE i.trabajador_persona = 2 AND iis.tipo_indice = 3 
   AND NOT EXISTS
       (SELECT 1 FROM personas_relacionada pr
        WHERE i.trabajador = pr.trabajador 
          AND i.persona_relacionada = pr.persona_relacionada)
) 
BEGIN 
    RAISERROR ('No existe la persona relacionada con el trabajador [inf_soc_trabajador]', 16, 1)
    ROLLBACK TRANSACTION 
    RETURN 
END 
 
IF EXISTS
(SELECT 1 FROM inserted i
 INNER JOIN indices_inf_soc iis ON i.indice_inf_soc = iis.indice_inf_soc
 WHERE i.trabajador_persona = 1 AND iis.tipo_indice = 3 
   AND NOT EXISTS (SELECT 1 FROM trabajadores t WHERE i.trabajador = t.trabajador)
) 
BEGIN 
    RAISERROR ('No existe el trabajador relacionado con el trabajador [inf_soc_trabajador]' , 16, 1)
    ROLLBACK TRANSACTION 
    RETURN 
END 
 
-- ****************************************************************************************************
--  VALIDACIONES NUEVAS
--  JCL. JULIO 15/06
--  EVITAR QUE METAN BASURA EN CAMPOS DE SELECCION
-- ****************************************************************************************************
Declare @vch_indice char(10), 
        @vvc_des varchar(20)

--*********
-- CAMPO 01
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 1 AND i.dato_01 IS NOT NULL AND i.dato_01 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 1 AND i.dato_01 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS 
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 1 AND i.dato_01 = d.dato_01)

        SELECT @vvc_des = des_col_01 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 02
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 2 AND i.dato_02 IS NOT NULL AND i.dato_02 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 2 AND i.dato_02 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc
        FROM inserted i WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 2 AND i.dato_02 = d.dato_01)

        SELECT @vvc_des = des_col_02 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 03
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 3 AND i.dato_03 IS NOT NULL AND i.dato_03 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 3 AND i.dato_03 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 3 AND i.dato_03 = d.dato_01)

        SELECT @vvc_des = des_col_03 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 04
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 4 AND i.dato_04 IS NOT NULL AND i.dato_04 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 4 AND i.dato_04 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 4 AND i.dato_04 = d.dato_01)

        SELECT @vvc_des = des_col_04 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 05
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 5 AND i.dato_05 IS NOT NULL AND i.dato_05 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 5 AND i.dato_05 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 5 AND i.dato_05 = d.dato_01)

        SELECT @vvc_des = des_col_05 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 06
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 6 AND i.dato_06 IS NOT NULL AND i.dato_06 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 6 AND i.dato_06 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 6 AND i.dato_06 = d.dato_01)

        SELECT @vvc_des = des_col_06 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 07
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 7 AND i.dato_07 IS NOT NULL AND i.dato_07 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 7 AND i.dato_07 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 7 AND i.dato_07 = d.dato_01)

        SELECT @vvc_des = des_col_07 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 08
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 8 AND i.dato_08 IS NOT NULL AND i.dato_08 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 8 AND i.dato_08 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 8 AND i.dato_08 = d.dato_01)

        SELECT @vvc_des = des_col_08 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 09
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 9 AND i.dato_09 IS NOT NULL AND i.dato_09 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 9 AND i.dato_09 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 9 AND i.dato_09 = d.dato_01)

        SELECT @vvc_des = des_col_09 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 10
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 10 AND i.dato_10 IS NOT NULL AND i.dato_10 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 10 AND i.dato_10 = d.dato_01)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 10 AND i.dato_10 = d.dato_01)

        SELECT @vvc_des = des_col_10 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 11
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 11 AND i.dato_11 IS NOT NULL AND i.dato_11 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 11 AND i.dato_11 = d.dato_02)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 11 AND i.dato_11 = d.dato_02)

        SELECT @vvc_des = des_col_11 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 12
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 12 AND i.dato_12 IS NOT NULL AND i.dato_12 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 12 AND i.dato_12 = d.dato_02)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 12 AND i.dato_12 = d.dato_02)

        SELECT @vvc_des = des_col_12 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 13
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 13 AND i.dato_13 IS NOT NULL AND i.dato_13 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 13 AND i.dato_13 = d.dato_02)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 13 AND i.dato_13 = d.dato_02)

        SELECT @vvc_des = des_col_13 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 14
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 14 AND i.dato_14 IS NOT NULL AND i.dato_14 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 14 AND i.dato_14 = d.dato_03)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 14 AND i.dato_14 = d.dato_03)

        SELECT @vvc_des = des_col_14 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 15
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 15 AND i.dato_15 IS NOT NULL AND i.dato_15 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 15 AND i.dato_15 = d.dato_03)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 15 AND i.dato_15 = d.dato_03)

        SELECT @vvc_des = des_col_15 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 16
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 16 AND i.dato_16 IS NOT NULL AND i.dato_16 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 16 AND i.dato_16 = d.dato_03)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 16 AND i.dato_16 = d.dato_03)

        SELECT @vvc_des = des_col_16 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 16
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 16 AND i.dato_16 IS NOT NULL AND i.dato_16 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 16 AND i.dato_16 = d.dato_03)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 16 AND i.dato_16 = d.dato_03)

        SELECT @vvc_des = des_col_16 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 17
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 17 AND i.dato_17 IS NOT NULL AND i.dato_17 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 17 AND i.dato_17 = d.dato_03)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 17 AND i.dato_17 = d.dato_03)

        SELECT @vvc_des = des_col_17 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 18
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 18 AND i.dato_18 IS NOT NULL AND i.dato_18 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 18 AND i.dato_18 = d.dato_03)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 18 AND i.dato_18 = d.dato_03)

        SELECT @vvc_des = des_col_18 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 19
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 19 AND i.dato_19 IS NOT NULL AND i.dato_19 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 19 AND i.dato_19 = d.dato_03)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 19 AND i.dato_19 = d.dato_03)

        SELECT @vvc_des = des_col_19 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 20
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 20 AND i.dato_20 IS NOT NULL AND i.dato_20 <> 0)
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 20 AND i.dato_20 = d.dato_03)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 20 AND i.dato_20 = d.dato_03)

        SELECT @vvc_des = des_col_20 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 21
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 21 AND i.dato_21 IS NOT NULL AND i.dato_21 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 21 AND i.dato_21 = d.dato_04)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 21 AND i.dato_21 = d.dato_04)

        SELECT @vvc_des = des_col_21 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 22
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 22 AND i.dato_22 IS NOT NULL AND i.dato_22 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 22 AND i.dato_22 = d.dato_04)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 22 AND i.dato_22 = d.dato_04)

        SELECT @vvc_des = des_col_22 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 23
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 23 AND i.dato_23 IS NOT NULL AND i.dato_23 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 23 AND i.dato_23 = d.dato_04)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 23 AND i.dato_23 = d.dato_04)

        SELECT @vvc_des = des_col_23 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 24
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 24 AND i.dato_24 IS NOT NULL AND i.dato_24 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 24 AND i.dato_24 = d.dato_04)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 24 AND i.dato_24 = d.dato_04)

        SELECT @vvc_des = des_col_24 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

--*********
-- CAMPO 25
-- ********
IF EXISTS
(SELECT 1 FROM datos_ind_inf_soc d 
 INNER JOIN inserted i ON i.indice_inf_soc = d.indice_inf_soc
 WHERE d.num_col = 25 AND i.dato_25 IS NOT NULL AND i.dato_25 <> '')
Begin
    IF NOT EXISTS(SELECT 1 FROM inserted i INNER JOIN datos_ind_inf_soc d ON i.indice_inf_soc = d.indice_inf_soc
                  WHERE d.num_col = 25 AND i.dato_25 = d.dato_04)
    BEGIN
        SELECT TOP 1 @vch_indice = i.indice_inf_soc FROM inserted i
        WHERE NOT EXISTS
        (SELECT 1 FROM datos_ind_inf_soc d WHERE i.indice_inf_soc = d.indice_inf_soc AND d.num_col = 25 AND i.dato_25 = d.dato_04)

        SELECT @vvc_des = des_col_25 FROM indices_inf_soc WHERE indice_inf_soc = @vch_indice

        RAISERROR ('El dato digitado para %s no es válido, seleccione uno de la lista', 16, 1, @vvc_des)
        ROLLBACK TRANSACTION
        RETURN
    END 
End

/*Actualización de Historial_laboral*/ 
IF EXISTS 
(SELECT 1 FROM parametros_rh p
 INNER JOIN inserted i ON i.indice_inf_soc = p.alfanumerico
 WHERE p.compania = '0' AND p.parametro_rh = 'Inc_HL_ID')
BEGIN
-- Esta parte del programa se dejó igual... está diseñada para un solo registro, no para inserts o updates masivos
SELECT @i_flag_int    = 0,    
       @i_dato_int    = NULL,    
       @i_flag_char   = 0,    
       @i_dato_char   = NULL,    
       @i_flag_date   = 0,    
       @i_dato_date   = NULL,    
       @i_flag_number = 0,    
       @i_dato_number = NULL,    
       @i_fin = 0,    
       @i_status_char = 0,    
       @i_status_int  = 0,    
       @i_status_number = 0,    
       @i_status_date = 0,    
       @i_indice = ' '    
    
select @i_fecha = getdate()    
    
select @i_dato_01 = i.dato_01, @i_dato_02 = i.dato_02, @i_dato_03 = i.dato_03, @i_dato_04 = i.dato_04, @i_dato_05 = i.dato_05,       
       @i_dato_06 = i.dato_06, @i_dato_07 = i.dato_07, @i_dato_08 = i.dato_08, @i_dato_09 = i.dato_09, @i_dato_10 = i.dato_10,       
       @i_dato_11 = i.dato_11, @i_dato_12 = i.dato_12, @i_dato_13 = i.dato_13, @i_dato_14 = i.dato_14, @i_dato_15 = i.dato_15,      
       @i_dato_16 = i.dato_16, @i_dato_17 = i.dato_17, @i_dato_18 = i.dato_18, @i_dato_19 = i.dato_19, @i_dato_20 = i.dato_20,       
       @i_dato_21 = i.dato_21, @i_dato_22 = i.dato_22, @i_dato_23 = i.dato_23, @i_dato_24 = i.dato_24, @i_dato_25 = i.dato_25,       
       @i_trabajador = i.trabajador, @i_indice = i.indice_inf_soc    
from inserted i    
    
select @d_dato_01 = d.dato_01, @d_dato_02 = d.dato_02, @d_dato_03 = d.dato_03, @d_dato_04 = d.dato_04, @d_dato_05 = d.dato_05,       
       @d_dato_06 = d.dato_06, @d_dato_07 = d.dato_07, @d_dato_08 = d.dato_08, @d_dato_09 = d.dato_09, @d_dato_10 = d.dato_10,       
       @d_dato_11 = d.dato_11, @d_dato_12 = d.dato_12, @d_dato_13 = d.dato_13, @d_dato_14 = d.dato_14, @d_dato_15 = d.dato_15,      
       @d_dato_16 = d.dato_16, @d_dato_17 = d.dato_17, @d_dato_18 = d.dato_18, @d_dato_19 = d.dato_19, @d_dato_20 = d.dato_20,       
       @d_dato_21 = d.dato_21, @d_dato_22 = d.dato_22, @d_dato_23 = d.dato_23, @d_dato_24 = d.dato_24, @d_dato_25 = d.dato_25    
from deleted d    
    
WHILE @i_fin = 0     
BEGIN    
if @i_dato_char is null and @i_flag_char < 1     
begin    
   if @i_dato_01 is not null and @d_dato_01 is null    
      select @i_status_char = 1       
   else if @i_dato_01 is null and @d_dato_01 is not null    
           select @i_status_char = 2      
   else if @i_dato_01 is not null and @d_dato_01 is not null and @i_dato_01 <> @d_dato_01    
      select @i_status_char = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 1    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 1))    
      begin    
       select @i_dato_char = @i_dato_01    
       select @i_flag_char = 1    
      end    
end    
if @i_dato_char is null and @i_flag_char < 2     
begin    
   if @i_dato_02 is not null and @d_dato_02 is null    
      select @i_status_char = 1       
   else if @i_dato_02 is null and @d_dato_02 is not null    
           select @i_status_char = 2      
   else if @i_dato_02 is not null and @d_dato_02 is not null and @i_dato_02 <> @d_dato_02    
      select @i_status_char = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 2    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 2))    
      begin    
       select @i_dato_char = @i_dato_02    
       select @i_flag_char = 2    
      end    
end    
if @i_dato_char is null and @i_flag_char < 3     
begin    
   if @i_dato_03 is not null and @d_dato_03 is null    
      select @i_status_char = 1       
   else if @i_dato_03 is null and @d_dato_03 is not null    
           select @i_status_char = 2      
   else if @i_dato_03 is not null and @d_dato_03 is not null and @i_dato_03 <> @d_dato_03    
      select @i_status_char = 3    
      
if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 3    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 3))    
      begin    
       select @i_dato_char = @i_dato_03    
       select @i_flag_char = 3    
      end    
end    
if @i_dato_char is null and @i_flag_char < 4     
begin    
   if @i_dato_04 is not null and @d_dato_04 is null    
      select @i_status_char = 1       
   else if @i_dato_04 is null and @d_dato_04 is not null    
           select @i_status_char = 2      
   else if @i_dato_04 is not null and @d_dato_04 is not null and @i_dato_04 <> @d_dato_04    
      select @i_status_char = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 4    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 4))    
      begin    
       select @i_dato_char = @i_dato_04    
       select @i_flag_char = 4    
      end    
end    
if @i_dato_char is null and @i_flag_char < 5     
begin    
   if @i_dato_05 is not null and @d_dato_05 is null    
      select @i_status_char = 1       
   else if @i_dato_05 is null and @d_dato_05 is not null    
           select @i_status_char = 2      
   else if @i_dato_05 is not null and @d_dato_05 is not null and @i_dato_05 <> @d_dato_05    
      select @i_status_char = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 5    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 5))    
      begin    
       select @i_dato_char = @i_dato_05    
       select @i_flag_char = 5    
      end    
end    
if @i_dato_char is null and @i_flag_char < 6     
begin    
   if @i_dato_06 is not null and @d_dato_06 is null    
      select @i_status_char = 1       
   else if @i_dato_06 is null and @d_dato_06 is not null    
           select @i_status_char = 2      
   else if @i_dato_06 is not null and @d_dato_06 is not null and @i_dato_06 <> @d_dato_06    
      select @i_status_char = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 6    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
 and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 6))    
      begin    
       select @i_dato_char = @i_dato_06    
       select @i_flag_char = 6    
      end    
end    
if @i_dato_char is null and @i_flag_char < 7     
begin    
   if @i_dato_07 is not null and @d_dato_07 is null    
      select @i_status_char = 1       
   else if @i_dato_07 is null and @d_dato_07 is not null    
           select @i_status_char = 2      
   else if @i_dato_07 is not null and @d_dato_07 is not null and @i_dato_07 <> @d_dato_07    
      select @i_status_char = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 7    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 7))                  
      begin    
       select @i_dato_char = @i_dato_07    
       select @i_flag_char = 7    
      end    
end    
if @i_dato_char is null and @i_flag_char < 8     
begin    
   if @i_dato_08 is not null and @d_dato_08 is null    
      select @i_status_char = 1       
   else if @i_dato_08 is null and @d_dato_08 is not null    
           select @i_status_char = 2      
   else if @i_dato_08 is not null and @d_dato_08 is not null and @i_dato_08 <> @d_dato_08    
      select @i_status_char = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 8    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                           from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 8))                  
      begin    
       select @i_dato_char = @i_dato_08    
       select @i_flag_char = 8    
      end    
end    
if @i_dato_char is null and @i_flag_char < 9     
begin    
   if @i_dato_09 is not null and @d_dato_09 is null    
      select @i_status_char = 1       
   else if @i_dato_09 is null and @d_dato_09 is not null    
           select @i_status_char = 2      
   else if @i_dato_09 is not null and @d_dato_09 is not null and @i_dato_09 <> @d_dato_09    
      select @i_status_char = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 9    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 9))                  
      begin    
       select @i_dato_char = @i_dato_09    
       select @i_flag_char = 9    
      end    
end    
if @i_dato_char is null and @i_flag_char < 10     
begin    
   if @i_dato_10 is not null and @d_dato_10 is null    
      select @i_status_char = 1       
   else if @i_dato_10 is null and @d_dato_10 is not null    
           select @i_status_char = 2      
   else if @i_dato_10 is not null and @d_dato_10 is not null and @i_dato_10 <> @d_dato_10    
      select @i_status_char = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 10    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 10))                  
      begin    
       select @i_dato_char = @i_dato_10    
       select @i_flag_char = 10    
      end    
end    
if @i_dato_int is null and @i_flag_int < 1     
begin    
   if @i_dato_11 is not null and @d_dato_11 is null or @d_dato_11 = 0    
      select @i_status_int = 1       
   else if (@i_dato_11 is null or @i_dato_11 = 0) and @d_dato_11 is not null    
           select @i_status_int = 2      
   else if @i_dato_11 is not null and @d_dato_11 is not null and @i_dato_11 <> @d_dato_11    
      select @i_status_int = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 11    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 11))                  
      begin    
       select @i_dato_int = @i_dato_11    
       select @i_flag_int = 1    
      end    
end    
if @i_dato_int is null and @i_flag_int < 2     
begin    
   if @i_dato_12 is not null and @d_dato_12 is null or @d_dato_12 = 0    
      select @i_status_int = 1       
   else if (@i_dato_12 is null or @i_dato_12 = 0) and @d_dato_12 is not null    
           select @i_status_int = 2      
   else if @i_dato_12 is not null and @d_dato_12 is not null and @i_dato_12 <> @d_dato_12    
      select @i_status_int = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))     
                and rh.entero = 12    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 12))                  
      begin    
       select @i_dato_int = @i_dato_12    
       select @i_flag_int = 2    
      end    
end    
if @i_dato_int is null and @i_flag_int < 3     
begin    
   if @i_dato_13 is not null and @d_dato_13 is null or @d_dato_13 = 0    
      select @i_status_int = 1       
   else if (@i_dato_13 is null or @i_dato_13 = 0) and @d_dato_13 is not null    
           select @i_status_int = 2      
   else if @i_dato_13 is not null and @d_dato_13 is not null and @i_dato_13 <> @d_dato_13    
      select @i_status_int = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
         and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 13    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 13))                  
      begin    
       select @i_dato_int = @i_dato_13    
       select @i_flag_int = 3    
      end    
end    
if @i_dato_number is null and @i_flag_number < 1     
begin    
   if @i_dato_14 is not null and @d_dato_14 is null or @d_dato_14 = 0    
      select @i_status_number = 1       
   else if (@i_dato_14 is null or @i_dato_14 = 0) and @d_dato_14 is not null    
           select @i_status_number = 2      
   else if @i_dato_14 is not null and @d_dato_14 is not null and @i_dato_14 <> @d_dato_14    
      select @i_status_number = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 14    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 14))                  
      begin    
       select @i_dato_number = @i_dato_14    
       select @i_flag_number = 1    
end    
end    
if @i_dato_number is null and @i_flag_number < 2     
begin    
   if @i_dato_15 is not null and @d_dato_15 is null or @d_dato_15 = 0    
      select @i_status_number = 1       
   else if (@i_dato_15 is null or @i_dato_15 = 0) and @d_dato_15 is not null    
           select @i_status_number = 2      
   else if @i_dato_15 is not null and @d_dato_15 is not null and @i_dato_15 <> @d_dato_15    
      select @i_status_number = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 15    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 15))                  
      begin    
       select @i_dato_number = @i_dato_15    
       select @i_flag_number = 2    
      end    
end    
if @i_dato_number is null and @i_flag_number < 3     
begin    
   if @i_dato_16 is not null and @d_dato_16 is null or @d_dato_16 = 0    
      select @i_status_number = 1       
   else if (@i_dato_16 is null or @i_dato_16 = 0) and @d_dato_16 is not null    
           select @i_status_number = 2      
   else if @i_dato_16 is not null and @d_dato_16 is not null and @i_dato_16 <> @d_dato_16    
      select @i_status_number = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 16    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'                                 
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 16))                  
      begin    
       select @i_dato_number = @i_dato_16    
       select @i_flag_number = 3    
      end    
end    
if @i_dato_number is null and @i_flag_number < 4     
begin    
   if @i_dato_17 is not null and @d_dato_17 is null or @d_dato_17 = 0    
      select @i_status_number = 1       
   else if (@i_dato_17 is null or @i_dato_17 = 0) and @d_dato_17 is not null    
           select @i_status_number = 2      
   else if @i_dato_17 is not null and @d_dato_17 is not null and @i_dato_17 <> @d_dato_17    
      select @i_status_number = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 17    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 17))                  
      begin    
       select @i_dato_number = @i_dato_17    
       select @i_flag_number = 4    
      end    
end     
if @i_dato_number is null and @i_flag_number < 5    
begin    
   if @i_dato_18 is not null and @d_dato_18 is null or @d_dato_18 = 0    
     select @i_status_number = 1       
   else if (@i_dato_18 is null or @i_dato_18 = 0) and @d_dato_18 is not null    
           select @i_status_number = 2      
   else if @i_dato_18 is not null and @d_dato_18 is not null and @i_dato_18 <> @d_dato_18    
      select @i_status_number = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 18    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 18))                  
      begin    
       select @i_dato_number = @i_dato_18    
       select @i_flag_number = 5    
      end    
end    
if @i_dato_number is null and @i_flag_number < 6    
begin    
   if @i_dato_19 is not null and @d_dato_19 is null or @d_dato_19 = 0    
      select @i_status_number = 1       
   else if (@i_dato_19 is null or @i_dato_19 = 0) and @d_dato_19 is not null    
           select @i_status_number = 2      
   else if @i_dato_19 is not null and @d_dato_19 is not null and @i_dato_19 <> @d_dato_19    
      select @i_status_number = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 19    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 19))                  
      begin    
       select @i_dato_number = @i_dato_19    
       select @i_flag_number = 6    
      end    
end    
if @i_dato_number is null and @i_flag_number < 7    
begin    
   if @i_dato_20 is not null and @d_dato_20 is null or @d_dato_20 = 0    
      select @i_status_number = 1       
   else if (@i_dato_20 is null or @i_dato_20 = 0) and @d_dato_20 is not null    
           select @i_status_number = 2      
   else if @i_dato_20 is not null and @d_dato_20 is not null and @i_dato_20 <> @d_dato_20    
      select @i_status_number = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 20    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 20))                  
      begin    
       select @i_dato_number = @i_dato_20    
       select @i_flag_number = 7    
      end    
end    
if @i_dato_date is null and @i_flag_date < 1    
begin    
   if @i_dato_21 is not null and @d_dato_21 is null    
      select @i_status_date = 1       
   else if @i_dato_21 is null and @d_dato_21 is not null    
           select @i_status_date = 2      
   else if @i_dato_21 is not null and @d_dato_21 is not null and @i_dato_21 <> @d_dato_21    
      select @i_status_date = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 21    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 21))                  
      begin    
       select @i_dato_date = @i_dato_21    
       select @i_flag_date = 1    
      end    
end    
if @i_dato_date is null and @i_flag_date < 2    
begin    
   if @i_dato_22 is not null and @d_dato_22 is null    
      select @i_status_date = 1       
   else if @i_dato_22 is null and @d_dato_22 is not null    
           select @i_status_date = 2      
   else if @i_dato_22 is not null and @d_dato_22 is not null and @i_dato_22 <> @d_dato_22    
      select @i_status_date = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 22    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 22))                  
      begin    
       select @i_dato_date = @i_dato_22    
       select @i_flag_date = 2    
      end    
end    
if @i_dato_date is null and @i_flag_date < 3    
begin    
   if @i_dato_23 is not null and @d_dato_23 is null    
      select @i_status_date = 1       
   else if @i_dato_23 is null and @d_dato_23 is not null    
           select @i_status_date = 2      
   else if @i_dato_23 is not null and @d_dato_23 is not null and @i_dato_23 <> @d_dato_23    
      select @i_status_date = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 23    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 23))                  
      begin    
       select @i_dato_date = @i_dato_23    
       select @i_flag_date = 3    
      end    
end    
if @i_dato_date is null and @i_flag_date < 4    
begin    
   if @i_dato_24 is not null and @d_dato_24 is null    
      select @i_status_date = 1       
   else if @i_dato_24 is null and @d_dato_24 is not null    
           select @i_status_date = 2      
   else if @i_dato_24 is not null and @d_dato_24 is not null and @i_dato_24 <> @d_dato_24    
      select @i_status_date = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 24    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 24))                  
      begin    
       select @i_dato_date = @i_dato_24    
       select @i_flag_date = 4    
      end    
end    
if @i_dato_date is null and @i_flag_date < 5    
begin    
   if @i_dato_25 is not null and @d_dato_25 is null    
      select @i_status_date = 1       
   else if @i_dato_25 is null and @d_dato_25 is not null    
           select @i_status_date = 2      
   else if @i_dato_25 is not null and @d_dato_25 is not null and @i_dato_25 <> @d_dato_25    
      select @i_status_date = 3    
      
      if exists(select 1 from parametros_rh rh, inserted i    
                where rh.compania = '0'    
                and rh.parametro_rh = 'Inc_HL_ID'    
                and rh.alfanumerico = ltrim(rtrim(@i_indice))    
                and rh.entero = 25    
                and rh.secuencia_parametro = (select max(secuencia_parametro)    
                                              from parametros_rh    
                                              where compania = '0'    
                                              and parametro_rh = 'Inc_HL_ID'    
                                              and alfanumerico = ltrim(rtrim(@i_indice))    
                                              and entero = 25))                  
      begin    
       select @i_dato_date = @i_dato_25    
       select @i_flag_date = 5    
      end    
end    
  
    -- JCL NOV. 24/04 EVITA ERROR DE NO EXISTE LA REGLA DE HISTORIAL LABORAL    
    IF EXISTS(SELECT 1 FROM parametros_rh WHERE parametro_rh = 'Inc_HL_ID' AND alfanumerico = @i_indice AND compania = '0')     
    -- END JCL  
        if  @i_status_char = 2 or @i_status_int = 2 or @i_status_number = 2 or @i_status_date = 2     
             begin    
                 exec sp_alta_historial_laboral '0', @i_trabajador, 'ID', @i_indice, @i_fecha, NULL,    
                 'Baja dato inf. socioeconómica', @i_dato_int, @i_dato_number, @i_dato_date,     
                 @i_dato_char, NULL ,NULL,NULL    
             end    
    
   if @i_dato_char is not null or @i_dato_number is not null or @i_dato_date is not null or @i_dato_int is not null    
     begin    
      if @i_status_char = 1 or @i_status_int = 1 or @i_status_number = 1 or @i_status_date = 1     
      begin          
         exec sp_alta_historial_laboral '0', @i_trabajador, 'ID', @i_indice, @i_fecha, NULL,    
         'Alta dato inf. socioeconómica', @i_dato_int, @i_dato_number, @i_dato_date,    
         @i_dato_char, NULL     
      end        
      else if  @i_status_char = 3 or @i_status_int = 3 or @i_status_number = 3 or @i_status_date = 3     
        begin                  
       exec sp_alta_historial_laboral '0', @i_trabajador, 'ID', @i_indice, @i_fecha, NULL,    
                 'Cambio dato inf. socioeconómica',@i_dato_int, @i_dato_number, @i_dato_date,    
                 @i_dato_char, NULL     
        end        
    
      select @i_dato_char = NULL    
      select @i_dato_number = NULL    
      select @i_dato_int = NULL    
      select @i_dato_date = NULL       
      select @i_status_char = 0    
      select @i_status_number = 0    
      select @i_status_int = 0    
      select @i_status_date = 0    
     end    
   else    
      select @i_fin = 1    
    
end    
     
END -- Actualizar historial laboral
 
/* Evita cambio de llave */ 
 
if (update(trabajador) or update(indice_inf_soc) or update(secuencia)) and exists(SELECT * FROM deleted) 
BEGIN 
    RAISERROR ('El usuario ha intentado actualizar campo llave [inf_soc_trabajador]', 16, 1)
    ROLLBACK TRANSACTION 
    RETURN 
END 

go
