asked on A trigger that displays a message to a user
Hi there. Please examine the coding in the following trigger. It's purpose is to display an error message if the user selects an invalid option. It is processing on top of an accounting application. The message does appear but I also want to change the values of certain fields so that the requisition lines do not disappear from view after processing has completed in the main application. These two update statements do not happen in spite of having a COMMIT TRAN immediately afterwards and commenting out the ROLLBACK TRAN instruction. I am thinking that the act of throwing up an error message is causing these updates to be cancelled. Do you agree and if so how do I solve this ?
The same thing happens if I use an INSTEAD OF trigger.
The same thing happens if I use an INSTEAD OF trigger.
Microsoft DevelopmentMicrosoft SQL ServerMicrosoft SQL Server 2008
Last Comment
Scott Pletcher
Please provide code.
ASKER
USE [HST-ADMN2014]
GO
/****** Object: Trigger [dbo].[tr_Reqn_to_PO_inval
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_Reqn_to_PO_inval
ON [dbo].[_etblPOPRequisition
AFTER UPDATE
AS
BEGIN
BEGIN TRY
BEGIN TRAN
DECLARE @LineID int, @Module int, @Description varchar(100), @Supplier int, @Quantity float, @ExpectedPrice float, @ExpectedDate datetime, @Project int, @LineStatus int, @Incident int, @ActualPrice float, @Sector varchar(100)
SELECT @LineID = idPOPRequisitionlines, @Module = iModuleID, @Description = cDescription, @Supplier = iSupplierID, @Quantity = fQuantity, @ExpectedPrice = fExpectedPrice, @ExpectedDate = dExpectedDate,
@Project = iProjectID, @LineStatus = iLineStatus, @Incident = iIncidentID,
@ActualPrice = fActualPrice, @Sector = cSector
FROM inserted
IF UPDATE(iLineStatus)
BEGIN
IF @LineStatus = 4
OR @LineStatus = 13
BEGIN
UPDATE _etblPOPRequisitionLines
SET iLineStatus = 3
WHERE idPOPRequisitionLines = @LineID
UPDATE _rtblIncidents
SET iIncidentStatusID = 1, iClassID = 0
WHERE idIncidents = @Incident
COMMIT TRAN
RAISEERROR ('You have chosen an incorrect status option', 16, 1)
WITH NOWAIT
END
END
COMMIT TRAN
END TRY
BEGIN CATCH
--ROLLBACK TRANSACTION;
THROW
END CATCH
END
GO
/****** Object: Trigger [dbo].[tr_Reqn_to_PO_inval
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_Reqn_to_PO_inval
ON [dbo].[_etblPOPRequisition
AFTER UPDATE
AS
BEGIN
BEGIN TRY
BEGIN TRAN
DECLARE @LineID int, @Module int, @Description varchar(100), @Supplier int, @Quantity float, @ExpectedPrice float, @ExpectedDate datetime, @Project int, @LineStatus int, @Incident int, @ActualPrice float, @Sector varchar(100)
SELECT @LineID = idPOPRequisitionlines, @Module = iModuleID, @Description = cDescription, @Supplier = iSupplierID, @Quantity = fQuantity, @ExpectedPrice = fExpectedPrice, @ExpectedDate = dExpectedDate,
@Project = iProjectID, @LineStatus = iLineStatus, @Incident = iIncidentID,
@ActualPrice = fActualPrice, @Sector = cSector
FROM inserted
IF UPDATE(iLineStatus)
BEGIN
IF @LineStatus = 4
OR @LineStatus = 13
BEGIN
UPDATE _etblPOPRequisitionLines
SET iLineStatus = 3
WHERE idPOPRequisitionLines = @LineID
UPDATE _rtblIncidents
SET iIncidentStatusID = 1, iClassID = 0
WHERE idIncidents = @Incident
COMMIT TRAN
RAISEERROR ('You have chosen an incorrect status option', 16, 1)
WITH NOWAIT
END
END
COMMIT TRAN
END TRY
BEGIN CATCH
--ROLLBACK TRANSACTION;
THROW
END CATCH
END
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Jim,
Thanks for the reply.
Can you just confirm that UI stands for 'User Interface' and SP stands for 'stored procedure'.
Thanks for the reply.
Can you just confirm that UI stands for 'User Interface' and SP stands for 'stored procedure'.
Correct.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Scott and Jim.
Thanks for this T-SQL code attempting to solve the problem which has given me a greater perspective on SQL 2012 coding.
I have been testing with it but because the trigger is working 'on top of' the Pastel accounting package, Pastel takes control as soon as the trigger is complete and overwrites the changes that it made, so it does not work.
The Pastel front end is written in Visual Basic .Net, but I don't have access to those programs. We are now having a Christmas break so I shall use the opportunity to look into the possibility of adding some Visual Basic .Net code to do the job.
regards
Philip
Thanks for this T-SQL code attempting to solve the problem which has given me a greater perspective on SQL 2012 coding.
I have been testing with it but because the trigger is working 'on top of' the Pastel accounting package, Pastel takes control as soon as the trigger is complete and overwrites the changes that it made, so it does not work.
The Pastel front end is written in Visual Basic .Net, but I don't have access to those programs. We are now having a Christmas break so I shall use the opportunity to look into the possibility of adding some Visual Basic .Net code to do the job.
regards
Philip
Recommend #39716721 and #39717417
I agree with the recommendation of #39716721 and #39717417 (fair disclosure: one of those answers was mine).