Wednesday, May 18, 2011

Trigger is not working with bulk update??

I have written a trigger like below...
-------------------------------------------------------------------------------------------------

IF UPDATE (GROUPCODE)
BEGIN
DECLARE @GROUPCODE_OLD VARCHAR(100),
@GROUPCODE_NEW VARCHAR(100)


SELECT @GROUPCODE_OLD = GROUPCODE FROM DELETED
SELECT @GROUPCODE_NEW = GROUPCODE FROM INSERTED
IF @GROUPCODE_OLD = @GROUPCODE_NEW
BEGIN
RETURN
END
ELSE
BEGIN


  SELECT  @GRADECODE=GRADECODE,       
  @COMPANYID = COMPANYID,      
  @COMPANYTYPECODE=COMPANYTYPECODE ,       
  @USER_CODE = LAST_UPD_BY,        
  @MASTERCODE = MASTERCODE
  FROM  INSERTED


  SELECT  @MASTER_COMPANYTYPECODE = COMPANYTYPECODE      
  FROM  TBL_COMPANY_MAS (NOLOCK)       
  WHERE  COMPANYID =@MASTERCODE 


  UPDATE TBL_COMMISSION_MAS SET ACTIVE=0 WHERE  COMPANYID=@COMPANYID

  SELECT  SERVICECODE AS SERVICE_CODE ,TMPL_ID    
  INTO  #TMP_DATA_GROUP  
  FROM  TBL_COMMISSION_TMPL_MAS WHERE 1=2  
        
  INSERT INTO #TMP_DATA_GROUP       
  SELECT  DISTINCT SERVICECODE as SERVICE_CODE ,TMPL_ID       
  FROM  TBL_COMMISSION_TMPL_MAS A (NOLOCK)      
  WHERE  CREATED_BY_COMPANY =@MASTERCODE      
  AND  CONVERT(NVARCHAR(10),FROM_DATE,111) <= 
(
SELECT MAX(CONVERT(NVARCHAR(10),FROM_DATE,111))       
FROM  TBL_COMMISSION_TMPL_MAS (NOLOCK)      
WHERE  CREATED_BY_COMPANY = @MASTERCODE      
AND SERVICECODE = A.SERVICECODE      
and CONVERT(NVARCHAR(10),FROM_DATE,111)<=CONVERT(NVARCHAR(10),GETDATE(),111)      
and GRADE_CODE =@GRADECODE
AND GROUPCODE = @GROUPCODE_NEW       
)        
  AND GRADE_CODE=@GRADECODE
  AND GROUPCODE = @GROUPCODE_NEW    
  AND ACTIVE =1          
     
  UNION      
     
  SELECT DISTINCT SERVICECODE as SERVICE_CODE ,TMPL_ID       
  FROM TBL_COMMISSION_TMPL_MAS A (NOLOCK)      
  WHERE  CREATED_BY_COMPANY =@MASTERCODE      
  AND CONVERT(NVARCHAR(10),FROM_DATE,111) > CONVERT(NVARCHAR(10),GETDATE(),111)               
  AND GRADE_CODE=@GRADECODE
  AND GROUPCODE = @GROUPCODE_NEW   
  AND ACTIVE =1    
          
--   DECLARE @TMPL_ID   VARCHAR(50)      
--   DECLARE @SERVICE_CODE VARCHAR(3)      
--   DECLARE @MSG_OUT int      
       
  WHILE EXISTS (SELECT TOP 1 '1' FROM  #TMP_DATA_GROUP)      
BEGIN      
SELECT @TMPL_ID = TMPL_ID FROM #TMP_DATA_GROUP ORDER BY TMPL_ID      
    
EXEC USP_SET_MARKUP_COMMISSION_WL @MASTERCODE, @MASTER_COMPANYTYPECODE,@TMPL_ID, @USER_CODE ,@MSG_OUT out,@COMPANYID,@GROUPCODE_NEW          
IF @@ERROR <> 0      
BEGIN      
ROLLBACK TRAN      
RETURN      
END      
    
DELETE  FROM #TMP_DATA_GROUP WHERE TMPL_ID= @TMPL_ID       
END 
END
END
-------------------------------------------------------------------------------------------------
but when i update this column either by query or by front end on by one it works fine, but when i write update query in bulk then its not working.

Any suggestion???

No comments:

Post a Comment