Hi,
I've a problem to check if there is a duplicate entry.
In my table i have a computed column which checks for a certain type of
entry which must be unique. E.g. there can be two types of entries. One
which has a type of 1 where period can be 6 or 7. The other type is 2
where period can be from 1 to 5.
Now what i would like to do is when type 1 is being entered it can only
be unique for 6 or 7. There cannot be both entries of 6 and 7. For type
2 it should be unique within each period, in that case there may be more
entries.
E.g.
type | contactid | period
1 1 6
1 1 7 <-should not be possible
2 1 1
2 1 2
2 1 3
2 5 1
2 5 1 <-should not be possible
Below is my ddl of my table and trigger:
CREATE TABLE [Mytable] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [int] NOT NULL ,
[contactId] [int] NOT NULL ,
[Period] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or [Period] =
7))) then null else [Id] end) ,
CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
(
[Id],
[TypeChk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TRIGGER DupCheck ON [dbo].[Mytable]
FOR INSERT, UPDATE
AS
IF (select count(contactid)
from mytable
group by contactid, period, type
having count(contactid)>1 and type=2)>1
ROLLBACK TRANSACTION
RAISERROR('there is a duplicate entry',16,1)
Can anyone help me?Jason
Why do you have three rows of 2 1 and it did not throw the error
My guess yiou need something like that
create table #test
(
pk int not null primary key,
type int not null,
contactid int not null,
period int,
)
go
alter table #test ADD CONSTRAINT
UNQ_test UNIQUE
(
type,
contactid
) ON [PRIMARY]
insert into #test values(1,1,1,6)
insert into #test values(2,1,1,7)--failed
insert into #test values(3,2,1,6)
insert into #test values(4,2,1,7)--failed
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:OU8bZM$%23FHA.1600@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've a problem to check if there is a duplicate entry.
> In my table i have a computed column which checks for a certain type of
> entry which must be unique. E.g. there can be two types of entries. One
> which has a type of 1 where period can be 6 or 7. The other type is 2
> where period can be from 1 to 5.
> Now what i would like to do is when type 1 is being entered it can only be
> unique for 6 or 7. There cannot be both entries of 6 and 7. For type 2 it
> should be unique within each period, in that case there may be more
> entries.
> E.g.
> type | contactid | period
> 1 1 6
> 1 1 7 <-should not be possible
> 2 1 1
> 2 1 2
> 2 1 3
> 2 5 1
> 2 5 1 <-should not be possible
>
> Below is my ddl of my table and trigger:
> CREATE TABLE [Mytable] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Type] [int] NOT NULL ,
> [contactId] [int] NOT NULL ,
> [Period] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or [Period] = 7)))
> then null else [Id] end) ,
> CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
> (
> [Id],
> [TypeChk]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TRIGGER DupCheck ON [dbo].[Mytable]
> FOR INSERT, UPDATE
> AS
> IF (select count(contactid)
> from mytable
> group by contactid, period, type
> having count(contactid)>1 and type=2)>1
> ROLLBACK TRANSACTION
> RAISERROR('there is a duplicate entry',16,1)
>
> Can anyone help me?|||First of all the trigger might not work as expected - multiple statements
following the IF statement must be enclosed in a BEGIN...END section if they
are to be executed only if the condition is true.
Right now the transaction in your trigger is rolled back every time, while
the error is raised only when it should be.
IF (select count(contactid)
from mytable
group by contactid, period, type
having count(contactid)>1 and type=2)>1
begin
ROLLBACK TRANSACTION
RAISERROR('there is a duplicate entry',16,1)
end
ML
http://milambda.blogspot.com/|||I was thinking pretty much the same way, but was thrown off by apparent
inconsistencies.
Maybe a table constraint using the following expression might help enforce
the business rule:
((Type = 1) and (Period between 1 and 5)) or ((Type = 2) and (Period between
6 and 7))
ML
http://milambda.blogspot.com/|||Uri Dimant wrote:
> Jason
> Why do you have three rows of 2 1 and it did not throw the error
> My guess yiou need something like that
> create table #test
> (
> pk int not null primary key,
> type int not null,
> contactid int not null,
> period int,
> )
> go
> alter table #test ADD CONSTRAINT
> UNQ_test UNIQUE
> (
> type,
> contactid
> ) ON [PRIMARY]
> insert into #test values(1,1,1,6)
> insert into #test values(2,1,1,7)--failed
> insert into #test values(3,2,1,6)
> insert into #test values(4,2,1,7)--failed
>
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:OU8bZM$%23FHA.1600@.TK2MSFTNGP11.phx.gbl...
>
>
>
Hi Uri,
The three 2 1 are what i want because they are a type 2 entry. It should
not allow a duplicate entry if the period is the same like the last row
in my example.
Type 1 should be unique for a period of 6 or 7. ML suggested a table
constraint would that work for me?|||On Thu, 08 Dec 2005 13:29:52 +0100, Jason wrote:
>Hi,
>I've a problem to check if there is a duplicate entry.
>In my table i have a computed column which checks for a certain type of
>entry which must be unique. E.g. there can be two types of entries. One
>which has a type of 1 where period can be 6 or 7. The other type is 2
>where period can be from 1 to 5.
>Now what i would like to do is when type 1 is being entered it can only
>be unique for 6 or 7. There cannot be both entries of 6 and 7. For type
>2 it should be unique within each period, in that case there may be more
>entries.
>E.g.
>type | contactid | period
>1 1 6
>1 1 7 <-should not be possible
>2 1 1
>2 1 2
>2 1 3
>2 5 1
>2 5 1 <-should not be possible
Hi Jason,
You can do this without a trigger, by changing the computed column and
the unique constraint:
CREATE TABLE [Mytable] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [int] NOT NULL ,
[contactId] [int] NOT NULL ,
[Period] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or
[Period] = 7))) then null else [Period] end) ,
CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
(
[Type],
[contactId],
[TypeChk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Thu, 08 Dec 2005 13:29:52 +0100, Jason wrote:
>
>
> Hi Jason,
> You can do this without a trigger, by changing the computed column and
> the unique constraint:
> CREATE TABLE [Mytable] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Type] [int] NOT NULL ,
> [contactId] [int] NOT NULL ,
> [Period] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or
> [Period] = 7))) then null else [Period] end) ,
> CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
> (
> [Type],
> [contactId],
> [TypeChk]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> Best, Hugo
Hi Hugo,
That did the job, thnx!
