I've inherited the following situation...
The table contains 4 columns... script below
Note that the first 3 columns denote the primary Key...
Actually what is really meant is the following...
Let's say the values for one row are as follows...
Code= A
LowVal = 25
HighVal=50
UseThis=Fred
What they want to be implied by this row... if Code=A and the test val is
between 25 and 50 UseThis= Fred
They want to disallow any row that overlaps from being added... such as the
following...
Code= A
LowVal = 30
HighVal=40
UseThis=Joe
How can you enforce something like this ?
CREATE TABLE [dbo].[Table1] (
[Code] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[LowVal] [decimal](6, 0) NOT NULL ,
[HighVal] [decimal](6, 0) NOT NULL ,
[UseThis] [char] (10) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code],
[LowVal],
[HighVal]
) ON [PRIMARY]"Rob" <rwchome@.comcast.net> wrote in message
news:l4WdnX_h78I-eyzeRVn-vA@.comcast.com...
> I've inherited the following situation...
> The table contains 4 columns... script below
> Note that the first 3 columns denote the primary Key...
> Actually what is really meant is the following...
> Let's say the values for one row are as follows...
> Code= A
> LowVal = 25
> HighVal=50
> UseThis=Fred
> What they want to be implied by this row... if Code=A and the test val is
> between 25 and 50 UseThis= Fred
> They want to disallow any row that overlaps from being added... such as
> the following...
> Code= A
> LowVal = 30
> HighVal=40
> UseThis=Joe
> How can you enforce something like this ?
>
> CREATE TABLE [dbo].[Table1] (
> [Code] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
> [LowVal] [decimal](6, 0) NOT NULL ,
> [HighVal] [decimal](6, 0) NOT NULL ,
> [UseThis] [char] (10) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code],
> [LowVal],
> [HighVal]
> ) ON [PRIMARY]
>
You'll have to use a trigger for this, eg:
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.LowVal
and l.HighVal > r.LowVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
David|||Thanks David,
Maybe I am doing something wrong, but I was able to add the following rows
after applying the trigger...
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',20,500,'FRED')
Rob
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eDxVWD1CGHA.344@.TK2MSFTNGP11.phx.gbl...
> "Rob" <rwchome@.comcast.net> wrote in message
> news:l4WdnX_h78I-eyzeRVn-vA@.comcast.com...
> You'll have to use a trigger for this, eg:
> create trigger Table1_no_overlap
> on Table1 for insert, update
> as
> begin
> if exists
> (
> select *
> from Table1 l
> join Table1 r
> on l.LowVal < r.LowVal
> and l.HighVal > r.LowVal
> )
> begin
> raiserror('Change would create overlapping range.',16,1)
> rollback transaction
> end
> end
>
> David
>|||Hi, Rob
Use the following trigger:
alter trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 t
join inserted i
on t.LowVal between i.LowVal and i.HighVal
or i.LowVal between t.LowVal and t.HighVal
where i.Code<>t.Code or i.LowVal<>t.LowVal or i.HighVal<>t.HighVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
If you want to allow overlapping ranges for different codes (but not
for the same code), the trigger would be like this:
alter trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 t
join inserted i
on t.Code=i.Code and (
t.LowVal between i.LowVal and i.HighVal
or i.LowVal between t.LowVal and t.HighVal
)
where i.LowVal<>t.LowVal or i.HighVal<>t.HighVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
Razvan|||Rob wrote:
> Thanks David,
> Maybe I am doing something wrong, but I was able to add the following rows
> after applying the trigger...
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',20,500,'FRED')
> Rob
>
Try it like this. Notice that I've added an extra constraint, modified
the join in the trigger and added CODE to the join. That's my reading
of what you want to achieve. Test carefully.
ALTER TABLE table1 ADD CONSTRAINT ck_table1_lowval_highval
CHECK (lowval <= highval) ;
GO
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.HighVal
and l.HighVal > r.LowVal
and l.code = r.code
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
GO
David Portas
SQL Server MVP
--|||Hi, David
Your trigger doesn't allow any row to be inserted.
Razvan|||Razvan Socol wrote:
> Hi, David
> Your trigger doesn't allow any row to be inserted.
> Razvan
You're right. Here's a correction:
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.HighVal
and l.HighVal > r.LowVal
and l.code = r.code
and (l.LowVal <> r.LowVal
or l.HighVal <> r.HighVal)
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
GO
David Portas
SQL Server MVP
--|||Hi, David
My understanding of the original post is that the following rows are
not allowed (but your trigger allows them):
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',100,150,'FRED')
Rob wrote:
> What they want to be implied by this row... if Code=A and the test val is
> between 25 and 50 UseThis= Fred
The following rows would be ok:
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',101,150,'FRED')
Razvan|||Thank you both Razvan and David...
Sorry I was not clear on this, actually same HighVal on one row may be equal
to LowVal on another...
The code applied uses > LowVal and <= HighVal...
Rob
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1135755688.241085.105660@.g49g2000cwa.googlegroups.com...
> Hi, David
> My understanding of the original post is that the following rows are
> not allowed (but your trigger allows them):
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',100,150,'FRED')
> Rob wrote:
> The following rows would be ok:
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',101,150,'FRED')
> Razvan
>|||You might want to add some other constraints. I would not allow the
low and high values to be the same; disjoint ranges will allow you to
use a more readable BETWEEN predicate.
CREATE TABLE Table1
(foo_code CHAR (10) NOT NULL,
low_val DECIMAL(6,0) NOT NULL,
high_val DECIMAL(6,0) NOT NULL,
use_this CHAR (10) DEFAULT '{{ none }}' NOT NULL,
CHECK (low_val <= high_val)
PRIMARY KEY (foo_code,low_val, high_val),
UNIQUE (foo_code,low_val),
UNIQUE (foo_code, high_val)
);
Besides not having overlaps, you might want to avoid gaps in the
ranges.
CREATE TRIGGER Table1_No_Gaps
ON Table1 FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS
(SELECT *
FROM Table1 AS T1
GROUP BY T1.foo_code
HAVING MAX(high_val)- MIN(low_val) +1
= SUM(high_val - low_val + 1)
BEGIN
RAISERROR ('Code Range Errors',16,1);
ROLLBACK TRANSACTION;
END;
END;
sql
