Solved

A trigger that displays a message to a user

Posted on 2013-12-13
11
38 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Expert Comment

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

Assisted Solution

by:ScottPletcher
ScottPletcher 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:ScottPletcher
ID: 41704092
I agree with the recommendation of #39716721 and #39717417 (fair disclosure: one of those answers was mine).
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

12 Experts available now in Live!

Get 1:1 Help Now