Click here to Skip to main content
15,936,821 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
in the past update trigger is working fine.now it is not excuting or not show any error.still the process is going on for 20 min also.can i know the reason.
SQL
USE [RMS]
GO
/****** Object:  Trigger [dbo].[UpdateOnBDTracker]    Script Date: 9/27/2013 10:54:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:B KALI SIDDAYYA		<author,,name>
-- Create date: 06-9-2013<create>
-- Description:	update trigger for BDTracker<description,,>
-- =============================================
ALTER TRIGGER [dbo].[UpdateOnBDTracker]
   ON  [dbo].[BDTracker]
   AFTER Update
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	DECLARE @CallID NVARCHAR(max)
    DECLARE @LeadID NVARCHAR(max)
    DECLARE @LeadName NVARCHAR(max)
    DECLARE @LeadPOCID NVARCHAR(max)
    DECLARE @LeadPOCName NVARCHAR(max)
    DECLARE @FollowUpByEmpName NVARCHAR(max)
    DECLARE @FollowUpByEmpID NVARCHAR(max)
    DECLARE @FollowUpDateAndTime NVARCHAR(max)
    DECLARE @FollowUpStatus NVARCHAR(max)
    DECLARE @FollowUpComments NVARCHAR(max)
    DECLARE @NextFollowUpDateAndTime NVARCHAR(max)
	DECLARE @FollowUpType NVARCHAR(MAX)
    DECLARE @CreatedOn NVARCHAR(max)
    DECLARE @CreatedBy NVARCHAR(max)
    DECLARE @UpdatedOn NVARCHAR(max)
    DECLARE @UpdatedBy NVARCHAR(max)
	DECLARE @FollowUpCompletedDateAndTime NVARCHAR(MAX)
	DECLARE @CompanyProfileSent NVARCHAR(MAX)
	DECLARE @CompanyProfileSentDateAndTime NVARCHAR(MAX)
	select 
	@CallID=Followup_ID,
    @LeadID=Lead_ID,
	@LeadName=Lead_Name,
	@LeadPOCID=Lead_POC_ID,
	@LeadPOCName=Lead_POC_Name,
	@FollowUpByEmpName=Follow_Up_By_Emp_Name,
	@FollowUpByEmpID=Follow_Up_By_Emp_ID,
	@FollowUpDateAndTime=Follow_Up_Date_And_Time,
	@FollowUpStatus=Follow_Up_Status,
	@FollowUpComments=Follow_Up_Comments,
	@NextFollowUpDateAndTime=Next_Follow_Up_Date_And_Time,
	@CreatedOn=Created_On,
	@CreatedBy=Created_By,
	@UpdatedOn=Updated_On,
	@UpdatedBy=Updated_By,
	@FollowUpType=Followup_Type,
	@FollowUpCompletedDateAndTime=FollowUp_Completed_Date_And_Time,
	@CompanyProfileSent=Company_Profile_Sent,
	@CompanyProfileSentDateAndTime=Company_Profile_Sent_Date_And_Time
	from inserted

	DECLARE @OldValueCallID NVARCHAR(max)
	DECLARE @OldValueLeadID NVARCHAR(max)
	DECLARE @OldValueLeadName NVARCHAR(max)
	DECLARE @OldValueLeadPOCID NVARCHAR(max)
	DECLARE @OldValueLeadPOCName NVARCHAR(max)
	DECLARE @OldValueFollowUpByEmpName NVARCHAR(max)
	DECLARE @OldValueFollowUpByEmpID NVARCHAR(max)
	DECLARE @OldValueFollowUpDateAndTime NVARCHAR(max)
	DECLARE @OldValueFollowUpStatus NVARCHAR(max)
	DECLARE @OldValueFollowUpComments NVARCHAR(max)
	DECLARE @OldValueNextFollowUpDateAndTime NVARCHAR(max)
	DECLARE @OldValueFollowupType NVARCHAR(MAX)
	DECLARE @OldValueCreatedOn NVARCHAR(max)
	DECLARE @OldValueCreatedBy NVARCHAR(max)
	DECLARE @OldValueUpdatedOn NVARCHAR(max)
	DECLARE @OldValueUpdatedBy NVARCHAR(max)
    DECLARE @OldValueFollowUpCompletedDateAndTime NVARCHAR(MAX)
	DECLARE @OldValueCompanyProfileSent NVARCHAR(MAX)
	DECLARE @OldValueCompanyProfileSentDateAndTime NVARCHAR(MAX)
	SELECT 
	@OldValueCallID=Followup_ID,
	@OldValueLeadID=Lead_ID,
	@OldValueLeadName=Lead_Name,
	@OldValueLeadPOCID=Lead_POC_ID,
	@OldValueLeadPOCName=Lead_POC_Name,
	@OldValueFollowUpByEmpName=Follow_Up_By_Emp_Name,
	@OldValueFollowUpByEmpID=Follow_Up_By_Emp_ID,
	@OldValueFollowUpDateAndTime=Follow_Up_Date_And_Time,
	@OldValueFollowUpStatus=Follow_Up_Status,
	@OldValueFollowUpComments=Follow_Up_Comments,
	@OldValueNextFollowUpDateAndTime=Next_Follow_Up_Date_And_Time,
	@OldValueCreatedOn=Created_On,
	@OldValueCreatedBy=Created_By,
	@OldValueUpdatedOn=Updated_On,
	@OldValueUpdatedBy=Updated_By,
	@OldValueFollowupType=Followup_Type,
	@OldValueFollowUpCompletedDateAndTime=FollowUp_Completed_Date_And_Time,
	@OldValueCompanyProfileSent=Company_Profile_Sent,
	@OldValueCompanyProfileSentDateAndTime=Company_Profile_Sent_Date_And_Time
	FROM deleted

	IF (UPDATE(Followup_ID))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Call ID',@OldValueCallID,@CallID,@UpdatedOn,@UpdatedBy);
		END
     IF (UPDATE(Lead_ID))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Lead ID',@OldValueLeadID,@LeadID,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Lead_Name))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Lead Name',@OldValueLeadName,@LeadName,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Lead_POC_ID))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Lead POC ID',@OldValueLeadPOCID,@LeadPOCID,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Lead_POC_Name))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'LeadPOC Name',@OldValueLeadPOCName,@LeadPOCName,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Follow_Up_By_Emp_Name))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Follow Up By Emp Name',@OldValueFollowUpByEmpName,@FollowUpByEmpName,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Follow_Up_By_Emp_ID))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Follow Up By Emp ID',@OldValueFollowUpByEmpID,@FollowUpByEmpID,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Follow_Up_Date_And_Time))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Follow Up Date And Time',@OldValueFollowUpDateAndTime,@FollowUpDateAndTime,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Follow_Up_Status))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Follow Up Status',@OldValueFollowUpStatus,@FollowUpStatus,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Follow_Up_Comments))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Follow Up Comments',@OldValueFollowUpComments,@FollowUpComments,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Next_Follow_Up_Date_And_Time))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Next Follow Up Date And Time',@OldValueNextFollowUpDateAndTime,@NextFollowUpDateAndTime,@UpdatedOn,@UpdatedBy);
		 --check for follow up status to create a new record in BDTracker
		 if(@FollowUpStatus ='Completed' or @FollowUpStatus='Cancelled')
	     BEGIN
		     IF( @NextFollowUpDateAndTime is NOT  NULL  and CONVERT(date,@NextFollowUpDateAndTime) >= CONVERT(date,GETDATE()))
		         BEGIN
			          INSERT INTO BDTracker(Lead_ID,Lead_Name,Lead_POC_ID,Lead_POC_Name,Follow_Up_By_Emp_Name,Follow_Up_By_Emp_ID,Followup_Type,Follow_Up_Date_And_Time,Follow_Up_Status,Follow_Up_Comments,Created_On,Created_By,Updated_On,Updated_By) 
			          VALUES (@LeadID,@LeadName,@LeadPOCID,@LeadPOCName,@FollowUpByEmpName,@FollowUpByEmpID,@FollowupType,@NextFollowUpDateAndTime,'Scheduled','Copied from Followup ID'+@CallID+@FollowUpComments,GETDATE(),@UpdatedBy,GETDATE(),@UpdatedBy);
					  --parent next follow date and time is setting as null based on follow up id
					  UPDATE BDTracker SET Next_Follow_Up_Date_And_Time=null WHERE Followup_ID=@CallID
		          END
	        END
        
		END
		
		IF (UPDATE(Followup_Type))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Followup Type',@OldValueFollowupType,@FollowupType,@UpdatedOn,@UpdatedBy);
		END

		IF (UPDATE(FollowUp_Completed_Date_And_Time))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'FollowUp Completed Date And Time',@OldValueFollowUpCompletedDateAndTime,@FollowUpCompletedDateAndTime,@UpdatedOn,@UpdatedBy);
		END
		IF (UPDATE(Company_Profile_Sent))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Company Profile Sent',@OldValueCompanyProfileSent,@CompanyProfileSent,@UpdatedOn,@UpdatedBy);
		END
		
		IF (UPDATE(Company_Profile_Sent_Date_And_Time))
	   BEGIN
		 INSERT INTO BDTrackerHistory(Follow_ID,Column_Name,Old_Value,New_Value,Updated_On,Updated_By) VALUES (@CallID,'Company Profile Sent Date And Time',@OldValueCompanyProfileSentDateAndTime,@CompanyProfileSentDateAndTime,@UpdatedOn,@UpdatedBy);
		END
		--create a record in bdTracker, If Next_Follow_Up_Date_And_Time is updated
		
END
Posted
Updated 26-Sep-13 20:12pm
v2

1 solution

No one can help you, because of set of reasons. I would suggest you to use SearchBox[^].
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900