更新触发器未在sql server 2012中执行 [英] update Trigger is not execution in sql server 2012

问题描述

在过去的更新触发器工作正常。现在它没有激发或没有显示任何错误。此过程持续20分钟也可以。我知道原因。

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.

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

推荐答案

由于各种原因，没有人可以帮助你。我建议你使用 SearchBox [ ^ ]。

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

这篇关于更新触发器未在sql server 2012中执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！