Solved

Trigger code to send a message but allow INSERT...

Posted on 2006-06-19
5
341 Views
Last Modified: 2012-06-27
Hi all

I'm writting a trigger to validate entry, and I have two specific situations:
  If a value < 1, then display a message, and do not allow the INSERT
  If a value > 10, then display a message, and allow the INSERT.

I'm using RaiseError for the first instance, and need a recommendation on what to use for the second.

Thanks in advance.
-Jim
_______________


CREATE trigger INSD_CPHY_CPRD_XREF_copy_INS_T on dbo.INSD_CPHY_CPRD_XREF for insert as
if @@rowcount = 0 return

-- blah blah blah

Declare @CPHY_ID int, @MORTALITY_PERCENT decimal(15,9)

SELECT @CPHY_ID = cpcpx.CPHY_ID, @MORTALITY_PERCENT = inserted.INSD_CPRD_MORTALITY_PCT
FROM CPHY_CPRD_XREF cpcpx
INNER JOIN inserted ON cpcpx.CPHY_CPRD_XREF_ID = inserted.CPHY_CPRD_XREF_ID

if @CPHY_ID IN (6,7)
      begin

      -- Do not allow a mortality percent under 100% (1)
      if @MORTALITY_PERCENT < 1  
            begin
             raiserror ('The mortaility percent for AVS and Fasano consults must be greater than 100%.   (INSD_CPHY_CPRD_XREF_INS_T)', 16, 1,  @insd_cphy_cprd_xref_id)
                    rollback tran
            end

      -- Prompt user and allow a mortality percent over 1000% (10)
      IF @MORTALITY_PERCENT > 10
            begin
             raiserror ('You have entered a mortality percent greater than 1000%.  Verify that this is correct.   (INSD_CPHY_CPRD_XREF_INS_T)', 16, 1,  @insd_cphy_cprd_xref_id)
            end

      end

end
return
0
Comment
Question by:Jim Horn
[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
5 Comments
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 125 total points
ID: 16937638
T-SQL doesn't really have an interactive prompt like VB or VBA.  You'll need to bury the code in some app like a front-end Access app.
0
 
LVL 13

Assisted Solution

by:MikeWalsh
MikeWalsh earned 125 total points
ID: 16937808
Be very careful about implementing something like this in a trigger. If you do it incorrectly a transaction may be left hanging waiting for the trigger code to finish executing.

You could implement a job to run on a regular basis and get the information and call a process, or you should handle this through your front end code.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 16938931
remember that the trigger will fire when multiple rows are affected as well as single update cases...

so that a mixture of messages and situations could occur... in that case
you'd have to rollback the whole batch of updates or accept the lot
if you continue with the style of trigger you've currently written...


as mike said this is really a client side issue...
have your application issue the messages ...
then commit your updates without the trigger code...
0
 
LVL 65

Author Comment

by:Jim Horn
ID: 16942869
Thanks guys.  This really put things into perspective for me.  

I will write this validation code behind the Access form that handles its data entry.

-Jim
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16944240
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

735 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