Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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

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
Jim Horn
Asked:
Jim Horn
3 Solutions
 
Jim P.Commented:
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
 
MikeWalshCommented:
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
 
LowfatspreadCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now