Solved

A trigger that displays a message to a user

Posted on 2013-12-13
11
45 Views
Last Modified: 2016-07-11
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.
0
Comment
Question by:Philip van Gass
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39716611
Please provide code.
0
 

Author Comment

by:Philip van Gass
ID: 39716660
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 39716721
>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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:Philip van Gass
ID: 39716745
Hi Jim,
Thanks for the reply.
Can you just confirm that UI stands for 'User Interface' and SP stands for 'stored procedure'.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39716750
Correct.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 39717417
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
 

Author Comment

by:Philip van Gass
ID: 39728522
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41704023
Recommend #39716721 and #39717417
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41704092
I agree with the recommendation of #39716721 and #39717417 (fair disclosure: one of those answers was mine).
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Display Date and Time 7 48
Need more granular date groupings 4 42
What are the recommended security measures to put in place? 19 87
Begin Transaction 12 23
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question