当表上的插入触发器失败时，如何使插入语句成功? [英] how to make the insert statement successfull when the insert trigger fail on table?

问题描述

在插入触发器之后没有提交插入语句.如何使插入语句得以提交?我在c#中编写插入语句.在触发器之后我做了一些更新并将列保存到另一个表中但该记录不是如果发生任何错误，请保存在表中.

当表上的插入触发器失败时，如何使插入语句成功?








在C#中插入语句:-

Insert statement is not commited in the after insert trigger .how to make the insert statement to committed ?i writing the insert statement in c#.After Trigger i doing updating some column and save a record in another table but the record is not saving in the table if any error occur.

how to make the insert statement successfull when the insert trigger fail on table?








Insert Statement in c#:-

SqlCommand RMSRecievedEmailsSqlCommand = new SqlCommand("INSERT INTO RMS_Recieved_Emails
(To_Email_IDs,From_Email_ID,CC_Email_IDs,BCC_Email_IDs,Email_Subject,Email_Body,Message_ID,
Email_Received_Date_And_Time,Read_From_Email_Account,Created_On,
Processing_Completed,Process_For_Email_Command_Name) 
VALUES (@To_Email_IDs,@From_Email_ID,@CC_Email_IDs,@BCC_Email_IDs,@Email_Subject,@Email_Body,@Message_ID,
@Email_Received_Date_And_Time,@Read_From_Email_Account,@Created_On,@Processing_Completed,@Process_For_Email_Command_Name)", RMSRecievedEmailsSqlConnection);

触发器:-
使用[RMS]
ALTER TRIGGER [dbo].[After_Insert_On_RMSEmailRecievedEmails]
开启[dbo].[RMS_Recieved_Emails]
插入后
AS
开始
-添加了SET NOCOUNT ON以防止产生额外的结果集
-干扰SELECT语句.
设置NOCOUNT ON;

声明@FromEmailID VARCHAR(MAX)
声明@ToEmailID VARCHAR(MAX)
DECLARE @EmailSubject VARCHAR(MAX)
DECLARE @EmailBody VARCHAR(MAX)
DECLARE @EmailCommand VARCHAR(MAX)
宣告@AssignedByEmpName VARCHAR(MAX)
宣告@AssignedByEmpID VARCHAR(MAX)
宣告@AssignedByTeamName VARCHAR(MAX)
宣告@AssignedToEmpName VARCHAR(MAX)
宣告@AssignedToEmpID VARCHAR(MAX)
宣告@AssignedToTeamName VARCHAR(MAX)
宣告@AssignedByEmailID VARCHAR(MAX)
宣告@AssignedToEmailID VARCHAR(MAX)
DECLARE @类别varchar(max)
宣告@AssignedToRole VARCHAR(MAX)
宣告@ProcessForEmailCommandName VARCHAR(MAX)
DECLARE @InsertResult数值
宣告@RMSRecievedEmailID INT
选择
@ FromEmailID =发件人电子邮件ID，
@ ToEmailID = To_Email_IDs，
@ EmailSubject = Email_Subject，
@ EmailBody = Email_Body，
@ ProcessForEmailCommandName = Process_For_Email_Command_Name，
@ RMSRecievedEmailID = RMS_Recieved_Email_ID
从插入

开始尝试
IF(@ProcessForEmailCommandName =''CTASK'')
开始

-电子邮件主题长度超过255个字符.电子邮件主题将
-添加到电子邮件正文中.
IF(LEN(@EmailSubject)> 255)
开始
宣告@TempEmailSubject varchar(max);
SET @ TempEmailSubject = @ EmailSubject;
SET @ EmailSubject = SUBSTRING(@ EmailSubject，0,255);
SET @ EmailBody = SUBSTRING(@ EmailSubject，0，LEN(@TempEmailSubject))+ @ EmailBody;
END

-如果Toemailid包含多个电子邮件ID
-它将使用第一个电子邮件ID并创建任务
IF(CHARINDEX(''，''，@ ToEmailID)> 0)
开始
SET @ ToEmailID = SUBSTRING(@ ToEmailID，0，CHARINDEX(''，''，@ ToEmailID));
END

--ToEmailID不能为null或为空白
-我们无法为电子邮件创建任务
IF(@ToEmailID为NULL或@ToEmailID ='''')
开始
UPDATE RMS_Recieved_Emails SET Email_Processing_Error_Description =''到电子邮件ID不存在''
哪里RMS_Recieved_Email_ID = @ RMSRecievedEmailID
GOTO Task_Cannot_Be_Created;
END

--From EmailID不能为空或空白
-我们无法为电子邮件创建任务
IF(@FromEmailID为NULL)
开始
UPDATE RMS_Recieved_Emails SET Email_Processing_Error_Description = +''从电子邮件ID不存在''
哪里RMS_Recieved_Email_ID = @ RMSRecievedEmailID
GOTO Task_Cannot_Be_Created;
END
-检查是否来自对流域的电子邮件ID
-如果来自电子邮件ID并非来自convate，则忽略电子邮件
-我们无法创建任务
ELSE
开始
IF(CHARINDEX(''@ convate.com''，@ FromEmailID)< 0)
开始
GOTO Task_Cannot_Be_Created;
END
END

-根据电子邮件ID从用户表中获取详细信息
-获取详细信息，例如Emp名称，Emp Id，Emp TeamName，Emp EmailId
创建表#AssignedBy(AssignedByID INT IDENTITY(1,1)非空，AssignedByEmpID INT，AssignedByEmpName VARCHAR(255)，AssignedByEmpEmailID VARCHAR(5000)，AssignedByEmpTeamName VARCHAR(255)主键(AssignedByID)); 插入#AssignedBy(AssignedByEmpID，AssignedByEmpName，AssignedByEmpEmailID，AssignedByEmpTeamName)
SELECT EMPLOYEE_ID，EMPLOYEE_NAME，EMAIL_ID，TEAM_NAME
来自用户
WHERE EMAIL_ID = @ FromEmailID
AND STATUS =活动''<​​br/> -如果临时表包含多个记录，我们只需获取
-最高纪录
SET @ AssignedByEmpID =(从#AssignedBy中选择前1个AssignedByEmpID);
SET @ AssignedByEmpName =(从#AssignedBy中选择前1个AssignedByEmpName);
SET @ AssignedByTeamName =(选择顶部1 AssignedByEmpTeamName FROM #AssignedBy);
SET @ AssignedByEmailID =(从#AssignedBy中选择前1个AssignedByEmpEmailID);

-根据收件人电子邮件ID"从用户表获取详细信息
-获取详细信息，例如Emp名称，Emp Id，Emp TeamName，Emp EmailId，Emp角色
CREATE TABLE #AssignedTo(AssignedToID INT IDENTITY(1,1)NOT NULL，AssignedToEmpID INT，AssignedToEmpName VARCHAR(255)，AssignedToEmpEmailID VARCHAR(5000)，AssignedToEmpTeamName VARCHAR(255)，AssignedToRole varchar(255);主键( 插入到#AssignedTo(AssignedToEmpID，AssignedToEmpName，AssignedToEmpEmailID，AssignedToEmpTeamName，AssignedToRole)
SELECT EMPLOYEE_ID，EMPLOYEE_NAME，EMAIL_ID，TEAM_NAME，角色
来自用户
WHERE EMAIL_ID = @ ToEmailID或Alias_Email_Id = @ ToEmailID
AND STATUS =活动''<​​br/>
-如果临时表包含多个记录，我们只需获取
-最高纪录
SET @ AssignedToEmpID =(从#AssignedTo中选择前1个AssignedToEmpID);
SET @ AssignedToEmpName =(从#AssignedTo选择TOP 1的AssignedToEmpName);
SET @ AssignedToTeamName =(从#AssignedTo中选择前1个AssignedToEmpTeamName);
SET @ AssignedToEmailID =(从#AssignedTo选择前1个AssignedToEmpEmailID);
SET @ AssignedToRole =(从#AssignedTo选择头1个AssignedToRole);

-根据角色，我们将确定类别
-如果角色是SYS Admin，类别是ITHelpDesk
-如果角色是HR，则类别是HRHelpDesk
--else类别是招聘
IF(@ AssignedToRole =''系统管理员'')
开始
SET @Category =''ITHelpDesk'';
END
ELSE IF(@AssignedToRole =''HR'')
开始
SET @Category =''HRHelpDesk'';
END
ELSE
开始
SET @ Category =''招聘'';
END
-创建任务

插入任务(
ASSIGNED_BY，
CUSTOMER_NAME，
类别，
ASSIGNED_TO，
NEXT_FOLLOWUP_DATE_AND_TIME，
优先级，
TASK_STATUS，
TASK_DESCRIPTION，
DEAD_LINE_DATE_AND_TIME，
CREATED_ON，
UPDATED_ON，
CREATED_BY，
LAST_MODIFIED_BY，
ASSIGNED_TO_TEAM_NAME，
ASSIGNED_BY_EMAIL_ID，
ASSIGNED_TO_EMAIL_ID，
ASSIGNED_BY_TEAM_NAME，
Task_Sub_Category，
Task_Objective
)
值
(@AssignedByEmpName，
"转换"，
@类别，
@AssignedToEmpName，
GETDATE()+ 1，
``中''，
``打开''，
@EmailBody，
GETDATE()+ 2，
GETDATE()，
GETDATE()，
@AssignedByEmpName，
@AssignedByEmpName，
@AssignedToTeamName，
@AssignedByEmailID，
@AssignedToEmailID，
@AssignedByTeamName，
null，
@EmailSubject
)

-SCOPE_IDENTITY()返回插入记录的最新主键值
-如果插入失败
SET @ InsertResult = SCOPE_IDENTITY();

-检查以更新sp
中使用的处理完成 --sp将无法再次创建任务
IF(@InsertResult> 0)
开始
UPDATE RMS_Recieved_Emails SET Processing_Completed =''是''
RMS_Recieved_Email_ID = @ RMSRecievedEmailID
结束-插入检查

结束-任务检查
Task_Cannot_Be_Created:

-基于电子邮件处理计数存储过程
-将决定是否执行
UPDATE RMS_Recieved_Emails SET Email_Processing_Count =电子邮件_Processing_Count + 1
哪里RMS_Recieved_Email_ID = @ RMSRecievedEmailID;

结束尝试
开始捕捉
-如果未创建任务，则更新电子邮件处理错误说明.
更新RMS_Recieved_Emails SET Email_Processing_Error_Description =''SQL Server错误''+ ERROR_MESSAGE()+ convert(varchar(255)，ERROR_LINE())，
Email_Processing_Count = Email_Processing_Count + 1
哪里RMS_Recieved_Email_ID = @ RMSRecievedEmailID

结束观看



END--TRIGGER CLOSE

Trigger:-
USE [RMS]
ALTER TRIGGER [dbo].[After_Insert_On_RMSEmailRecievedEmails]
ON [dbo].[RMS_Recieved_Emails]
AFTER insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @FromEmailID VARCHAR(MAX)
DECLARE @ToEmailID VARCHAR(MAX)
DECLARE @EmailSubject VARCHAR(MAX)
DECLARE @EmailBody VARCHAR(MAX)
DECLARE @EmailCommand VARCHAR(MAX)
DECLARE @AssignedByEmpName VARCHAR(MAX)
DECLARE @AssignedByEmpID VARCHAR(MAX)
DECLARE @AssignedByTeamName VARCHAR(MAX)
DECLARE @AssignedToEmpName VARCHAR(MAX)
DECLARE @AssignedToEmpID VARCHAR(MAX)
DECLARE @AssignedToTeamName VARCHAR(MAX)
DECLARE @AssignedByEmailID VARCHAR(MAX)
DECLARE @AssignedToEmailID VARCHAR(MAX)
DECLARE @Category varchar(max)
DECLARE @AssignedToRole VARCHAR(MAX)
DECLARE @ProcessForEmailCommandName VARCHAR(MAX)
DECLARE @InsertResult Numeric
DECLARE @RMSRecievedEmailID INT
select
@FromEmailID=From_Email_ID,
@ToEmailID=To_Email_IDs,
@EmailSubject=Email_Subject,
@EmailBody=Email_Body,
@ProcessForEmailCommandName=Process_For_Email_Command_Name,
@RMSRecievedEmailID=RMS_Recieved_Email_ID
from inserted

BEGIN TRY
IF(@ProcessForEmailCommandName=''CTASK'')
BEGIN

--Email subject length more than 255 character.Email subject will
--added to the email body.
IF(LEN(@EmailSubject)>255)
BEGIN
DECLARE @TempEmailSubject varchar(max);
SET @TempEmailSubject=@EmailSubject;
SET @EmailSubject=SUBSTRING(@EmailSubject,0,255);
SET @EmailBody=SUBSTRING(@EmailSubject,0,LEN(@TempEmailSubject))+@EmailBody;
END

--if Toemailid contains more than one email id
--it will take the first email id and create the task
IF(CHARINDEX('','',@ToEmailID) > 0)
BEGIN
SET @ToEmailID=SUBSTRING(@ToEmailID,0,CHARINDEX('','',@ToEmailID));
END

--ToEmailID Cannot be null or blank
--we cannot create a task for the email
IF (@ToEmailID IS NULL or @ToEmailID ='''')
BEGIN
UPDATE RMS_Recieved_Emails SET Email_Processing_Error_Description=''To Email Id is not there''
WHERE RMS_Recieved_Email_ID=@RMSRecievedEmailID
GOTO Task_Cannot_Be_Created;
END

--From EmailID Cannot be null or blank
--we cannot create a task for the email
IF ( @FromEmailID IS NULL)
BEGIN
UPDATE RMS_Recieved_Emails SET Email_Processing_Error_Description=+''From Email Id is not there''
WHERE RMS_Recieved_Email_ID=@RMSRecievedEmailID
GOTO Task_Cannot_Be_Created;
END
--Checking for from email id from convate domain or not
--if from email id is not from convate ignore the email
--and we cannot create a task
ELSE
BEGIN
IF(CHARINDEX(''@convate.com'',@FromEmailID)<0)
BEGIN
GOTO Task_Cannot_Be_Created;
END
END

--Getting details from users table based on from email id
--getting details like Emp Name,Emp Id,Emp TeamName,Emp EmailId
CREATE TABLE #AssignedBy (AssignedByID INT IDENTITY(1,1) NOT NULL,AssignedByEmpID INT,AssignedByEmpName VARCHAR(255),AssignedByEmpEmailID VARCHAR(5000),AssignedByEmpTeamName VARCHAR(255) PRIMARY KEY(AssignedByID));
INSERT INTO #AssignedBy(AssignedByEmpID,AssignedByEmpName,AssignedByEmpEmailID,AssignedByEmpTeamName)
SELECT EMPLOYEE_ID,EMPLOYEE_NAME,EMAIL_ID,TEAM_NAME
FROM users
WHERE EMAIL_ID=@FromEmailID
AND STATUS=''Active''
--if temp table contain multiple record we need to get only
--top most record
SET @AssignedByEmpID=(SELECT TOP 1 AssignedByEmpID FROM #AssignedBy);
SET @AssignedByEmpName=(SELECT TOP 1 AssignedByEmpName FROM #AssignedBy);
SET @AssignedByTeamName=(SELECT TOP 1 AssignedByEmpTeamName FROM #AssignedBy);
SET @AssignedByEmailID=(SELECT TOP 1 AssignedByEmpEmailID FROM #AssignedBy);

--Getting details from users table based on To email id
--getting details like Emp Name,Emp Id,Emp TeamName,Emp EmailId,Emp Role
CREATE TABLE #AssignedTo (AssignedToID INT IDENTITY(1,1) NOT NULL,AssignedToEmpID INT,AssignedToEmpName VARCHAR(255),AssignedToEmpEmailID VARCHAR(5000),AssignedToEmpTeamName VARCHAR(255),AssignedToRole varchar(255) PRIMARY KEY(AssignedToID));
INSERT INTO #AssignedTo(AssignedToEmpID,AssignedToEmpName,AssignedToEmpEmailID,AssignedToEmpTeamName,AssignedToRole)
SELECT EMPLOYEE_ID,EMPLOYEE_NAME,EMAIL_ID,TEAM_NAME,Role
FROM users
WHERE EMAIL_ID =@ToEmailID or Alias_Email_Id=@ToEmailID
AND STATUS=''Active''

--if temp table contain multiple record we need to get only
--top most record
SET @AssignedToEmpID=(SELECT TOP 1 AssignedToEmpID FROM #AssignedTo);
SET @AssignedToEmpName=(SELECT TOP 1 AssignedToEmpName FROM #AssignedTo);
SET @AssignedToTeamName=(SELECT TOP 1 AssignedToEmpTeamName FROM #AssignedTo);
SET @AssignedToEmailID=(SELECT TOP 1 AssignedToEmpEmailID FROM #AssignedTo);
SET @AssignedToRole=(SELECT TOP 1 AssignedToRole FROM #AssignedTo);

--Based On Role we will decide Category
--if role is SYS Admin ,Category is ITHelpDesk
--if role is HR ,Category is HRHelpDesk
--else Category is Recruitment
IF(@AssignedToRole=''Sys Admin'')
BEGIN
SET @Category=''ITHelpDesk'';
END
ELSE IF(@AssignedToRole=''HR'')
BEGIN
SET @Category=''HRHelpDesk'';
END
ELSE
BEGIN
SET @Category=''Recruitment'';
END
--Create the task

insert into tasks (
ASSIGNED_BY,
CUSTOMER_NAME,
CATEGORY,
ASSIGNED_TO,
NEXT_FOLLOWUP_DATE_AND_TIME,
PRIORITY,
TASK_STATUS,
TASK_DESCRIPTION,
DEAD_LINE_DATE_AND_TIME,
CREATED_ON,
UPDATED_ON,
CREATED_BY,
LAST_MODIFIED_BY,
ASSIGNED_TO_TEAM_NAME,
ASSIGNED_BY_EMAIL_ID,
ASSIGNED_TO_EMAIL_ID,
ASSIGNED_BY_TEAM_NAME,
Task_Sub_Category,
Task_Objective
)
values
(@AssignedByEmpName,
''Convate'',
@Category,
@AssignedToEmpName,
GETDATE()+1,
''Medium'',
''Open'',
@EmailBody,
GETDATE()+2,
GETDATE(),
GETDATE(),
@AssignedByEmpName,
@AssignedByEmpName,
@AssignedToTeamName,
@AssignedByEmailID,
@AssignedToEmailID,
@AssignedByTeamName,
null,
@EmailSubject
)

-- SCOPE_IDENTITY() returns latest primary key value for inserted record
-- if insert fails
SET @InsertResult=SCOPE_IDENTITY();

--Checking to update the Processing Completed used in sp
--so that sp will cannot create the task again
IF (@InsertResult > 0)
BEGIN
UPDATE RMS_Recieved_Emails SET Processing_Completed=''Yes''
WHERE RMS_Recieved_Email_ID=@RMSRecievedEmailID
END--INSERT CHECKING

END--CTASK CHECKING
Task_Cannot_Be_Created:

--Based on Email Processing Count stored procedure
-- will decide to execute or not
UPDATE RMS_Recieved_Emails SET Email_Processing_Count=Email_Processing_Count+1
WHERE RMS_Recieved_Email_ID=@RMSRecievedEmailID;

END TRY
BEGIN CATCH
--Updating the Email Processing Error Description if the task is not created.
UPDATE RMS_Recieved_Emails SET Email_Processing_Error_Description=''SQL Server Error''+ERROR_MESSAGE()+convert(varchar(255),ERROR_LINE()),
Email_Processing_Count=Email_Processing_Count+1
WHERE RMS_Recieved_Email_ID=@RMSRecievedEmailID

END CATCH



END--TRIGGER CLOSE

推荐答案

您是否尝试过将C#用作触发器?

代码项目上一个质量检查 [

Have you tried using C# for the trigger?

Code Project Previous QA[^]

______________________________________________________________________________________________
Wow, embarrassing :-0
This was meant to be a comment not solution. But since it is a solution...

Look at the link and then use that example to trigger the trigger with C#. The QA shows how to stop a trigger but the same logic can be used to trigger a trigger.

;-)

插入另一个表(任务)时出错.分配为null

Error while inserting into another table(task).Assigned by is null

这篇关于当表上的插入触发器失败时，如何使插入语句成功?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！