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);
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
Submit an article or tip