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.
Philip van GassDatabase Developer/AdministratorAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>It's purpose is to display an error message if the user selects an invalid option
There is no such animal in SQL Server as 'display a message to the user', such as MsgBox(), as SQL is a database only and not a UI.   SQL does throw errors, but none that are user-friendly, and especially none that allow user interaction such as Ok, Cancel, Yes, No, etc.

A better way to attempt this would be that whatever SP/function is executed that attempts to update the table, if an error occurs anywhere, have that SP/function return a value that the UI can interpret as an error, and handle gracefully.
0
 
Shaun KlineLead Software EngineerCommented:
Please provide code.
0
 
Philip van GassDatabase Developer/AdministratorAuthor Commented:
USE [HST-ADMN2014]
GO
/****** Object:  Trigger [dbo].[tr_Reqn_to_PO_invalid_option]    Script Date: 2013/12/13 01:44:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_Reqn_to_PO_invalid_option]
      ON  [dbo].[_etblPOPRequisitionLines]
      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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Hi Jim,
Thanks for the reply.
Can you just confirm that UI stands for 'User Interface' and SP stands for 'stored procedure'.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Correct.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SQL triggers do not fire for every individual row, but only for the entire set of rows affected.  Therefore, triggers must be written to handle multiple rows updated at once.

For example:


ALTER TRIGGER [dbo].[tr_Reqn_to_PO_invalid_option]
      ON  [dbo].[_etblPOPRequisitionLines]
      AFTER UPDATE
AS
      SET NOCOUNT ON
      BEGIN TRY
          BEGIN TRAN
         
          DECLARE @IncidentIDs TABLE ( iIncidentID int )    
          UPDATE dbo._etblPOPRequisitionLines
                     SET iLineStatus = 3
                OUTPUT dbo._etblPOPRequisitionLines.iIncidentID INTO @IncidentIDs
                FROM dbo._etblPOPRequisitionLines
                INNER JOIN inserted i ON i.idPOPRequisitionLines = dbo._etblPOPRequisitionLines.idPOPRequisitionLines
                INNER JOIN deleted d ON d.idPOPRequisitionLines = i.idPOPRequisitionLines
                WHERE i.iLineStatus IN ( 4, 13 ) AND (d.iLineStatus IS NULL OR i.iLineStatus <> d.iLineStatus)
          IF @@ROWCOUNT > 0
          BEGIN                
              UPDATE dbo._rtblIncidents
                   SET iIncidentStatusID = 1, iClassID = 0
                   FROM dbo._rtblIncidents
                   INNER JOIN @IncidentIDs ii ON ii.iIncidentID = dbo._rtblIncidents.idIncidents
     
              COMMIT TRAN
              RAISERROR ('You have chosen an incorrect status option', 16, 1) WITH NOWAIT
          END --IF
      END TRY

      BEGIN CATCH
         --ROLLBACK TRANSACTION;        
         THROW
      END CATCH
0
 
Philip van GassDatabase Developer/AdministratorAuthor Commented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Recommend #39716721 and #39717417
0
 
Scott PletcherSenior DBACommented:
I agree with the recommendation of #39716721 and #39717417 (fair disclosure: one of those answers was mine).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.