Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-06-19
5
Medium Priority
?
364 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 500 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 500 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 1000 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 66

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

972 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