Link to home
Start Free TrialLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

asked on

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
SOLUTION
Avatar of Jim P.
Jim P.
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn

ASKER

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
Glad to be of assistance. May all your days get brighter and brighter.