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