Solved

A trigger that displays a message to a user

Posted on 2013-12-13
11
41 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
  • 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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now