Solved

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

Posted on 2006-06-19
5
296 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
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 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

15 Experts available now in Live!

Get 1:1 Help Now