question

I am trying to create a trigger that will check ranges for imported data. The data may sometimes have < or > leading characters, as such I want to remove these and check the range as well as check the range on the standard numerical data. The data goes to a varchar (20) field to accommodate the < and > but needs to be converted to float to check the ranges. Below is my script to create tables, trigger and test. My trigger has two scenarios, the first scenario in the where clause works when the second scenario (second line) is not included and the second works when the first scenario is not included. However when both are included separated by an OR, I get an Error converting data type varchar to float. Interesting they both work separately but not together. I am using SQL express 2008. GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CQL_VARIABLES]( [VARIABLE] [varchar](20) NOT NULL, [DESCRIPTION] [varchar](254) NULL, [UNITS] [varchar](10) NULL, [BASIS] [varchar](10) NULL, [METHOD] [varchar](20) NULL, [GROUP] [varchar](20) NULL, [MIN] [float] NULL, [MAX] [float] NULL, [DATA_TYPE] [varchar](10) NULL, [FIELD_LEN] [float] NULL, [FIELD_DEC] [float] NULL, CONSTRAINT [PK_CQL_VARIABLES] PRIMARY KEY CLUSTERED ( [VARIABLE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT INTO CQL_VARIABLES SELECT 'DefOx_degC','TEST','DegC',NULL,NULL,'TEST',1000,2000,'Numeric',5,0 GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CQ_ANALYSIS]( [SITE_ID] [varchar](16) NOT NULL, [SAMPNUMB] [varchar](8) NOT NULL, [GROUP] [varchar](20) NOT NULL, [VARIABLE] [varchar](20) NOT NULL, [VALUE] [varchar](20) NOT NULL, CONSTRAINT [PK_CQ_ANALYSIS] PRIMARY KEY CLUSTERED ( [SITE_ID] ASC, [SAMPNUMB] ASC, [GROUP] ASC, [VARIABLE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[CheckVariableRange] ON [dbo].[CQ_ANALYSIS] AFTER INSERT AS IF EXISTS (SELECT i.VALUE FROM INSERTED i INNER JOIN dbo.CQL_VARIABLES v ON v.VARIABLE = i.VARIABLE WHERE ((LEFT(i.VALUE,1) in ('<','>') AND isnumeric(Right(i.VALUE,Len(i.VALUE)-1))=1 AND (CONVERT(FLOAT,Right(i.VALUE,LEN(i.VALUE)-1)) < v.[MIN] or CONVERT(FLOAT,Right(i.VALUE,LEN(i.VALUE)-1)) > v.[MAX]))) --SCENARIO 1 CHECKS RANGE FOR <> VALUES OR (( (isnumeric(i.VALUE)=1 and i.VALUE < v.MIN) or (isnumeric(i.VALUE)=1 and i.VALUE > v.MAX) ))--SCENARIO 2 CHECKS RANGE FOR ACTUAL NUMERICS ) BEGIN RAISERROR ('Variable value is outside of acceptable range', 16, 1); ROLLBACK TRANSACTION; RETURN END; --test trigger with BEGIN TRAN INSERT INTO dbo.CQ_ANALYSIS (SITE_ID, SAMPNUMB, [GROUP], VARIABLE,VALUE) SELECT 'TEST01', 'TEST005', 'TEST', 'DefOx_DegC', '<1000' ROLLBACK TRAN