Jim Horn
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_I NS_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_MORTALI TY_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
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_I
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_MORTALI
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
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
end
end
end
return
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to be of assistance. May all your days get brighter and brighter.
ASKER
I will write this validation code behind the Access form that handles its data entry.
-Jim