Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

A trigger that displays a message to a user

Posted on 2013-12-13
11
Medium Priority
?
48 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 27

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 66

Accepted Solution

by:
Jim Horn earned 1000 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

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 66

Expert Comment

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

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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 66

Expert Comment

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

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

722 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