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???
-------------------------------------------------------------------------------------------------
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