SALFIT TRIGGER PROCEDURE

CREATE TRIGGER SALFIT_LOCK                    
   ON  H01_IDENTFCATN                  
   FOR  INSERT,UPDATE                  
AS                      
BEGIN  
 
                 
 IF EXISTS (SELECT '*' FROM INSERTED            
       WHERE H01_CONF_DATE IS NULL
         AND H01_REHIRE_DATE IS NULL
         AND H01_ACQUISITION_DATE IS NULL
         AND H01_EXIT_DATE IS NULL
         AND H01_EFFECTIVE_DATE_TO IS NULL)
 BEGIN  
   
DECLARE @NAME VARCHAR(100)      
SET @NAME = (SELECT A.EMP_NAME FROM SALARY_FITMENT_CODE A,INSERTED B    
WHERE A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO)      
     
EXEC SALFIT_ASKY @NAME    
   
 SET NOCOUNT ON                    
 IF NOT EXISTS (SELECT '*' FROM SALARY_FITMENT_CODE A,INSERTED B                  
       WHERE A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO    
  AND B.H01_EFFECTIVE_DATE_FROM IS NOT NULL)    
 BEGIN                    
 RAISERROR('ERROR: PLEASE VERIFY PRF NO.',16,1)                    
 END      
       
 IF NOT EXISTS (SELECT '*' FROM SALARY_FITMENT_CODE A,INSERTED B                  
       WHERE A.DOB = B.H01_BIRTH_DATE    
  AND A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO    
  AND B.H01_EFFECTIVE_DATE_FROM IS NOT NULL)      
 BEGIN                    
 RAISERROR('ERROR: PLEASE VERIFY EMPLOYEE BIRTH DATE',16,1)                    
 END      
       
 IF NOT EXISTS (SELECT '*' FROM SALARY_FITMENT_CODE A,INSERTED B                  
       WHERE A.DOJ = B.H01_JOIN_DATE    
  AND A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO    
  AND B.H01_EFFECTIVE_DATE_FROM IS NOT NULL)      
 BEGIN                    
 RAISERROR('ERROR: PLEASE VERIFY EMPLOYEE JOINING DATE',16,1)                    
 END    
     
DECLARE @NAMES VARCHAR(100)        
SET @NAMES = (SELECT LETTER FROM ASCII_SALFIT)  
   
--SELECT @NAMES  
   
DECLARE @EMPNAME VARCHAR(100)  
SELECT @EMPNAME = LOWER(ISNULL(B.H01_FIRST_NAME,'')+ISNULL(B.H01_MIDDLE_NAME,'')+ISNULL(B.H01_LAST_NAME,''))  
FROM SALARY_FITMENT_CODE A,INSERTED B    
WHERE A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO  
   
--SELECT @EMPNAME  
   
IF (@NAMES != @EMPNAME)  
 BEGIN                    
 RAISERROR('ERROR: PLEASE VERIFY EMPLOYEE NAME',16,1)                    
 END  
     
END  
END