I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:
> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
>
>
>
>
>
>
>
>
> - Show quoted text -|||rich wrote:
> Alejandro, thanks for the reply - I was trying to find some resource
> to confirm or deny the workings so I appreciate it.
> I will share the true source of my problem in case you, or others,
> would like to weigh in on the best method.
> Basically, all of our data tables have a delete_flag bit field.
> At the same time, I am trying to disallow duplicate combination of 3
> fields. (sounds like a primary key, huh?)
> Each table has an Identity field as the primary key.
> I can't create a unique index on the three field combo because I allow
> logical deletes using the delete_flag field.
> I tried including the delete_flag in a four field index, but then you
> can only delete each unique combination once.
> I need unlimited logically deleted rows where my three fields are not
> necessarily unique,
> but only one non-deleted one row for each unique combination.
> make sense?
The solution(s) to the problem "unique constraint with multiple NULLs"
also applies to your case, only in your case the delete flag acts as a
NULL.
So for example, something like the script below could be a solution:
ALTER TABLE YourTable
ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
ALTER TABLE YourTable
ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
HTH,
Gert-Jan|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
>
>
>
>
>
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne|||Well, for one thing, using a constraint will avoid unnecessary page
splits, because it will prevent duplicate rows. A trigger will roll back
duplicate rows.
Also, I think that in this case, writing a trigger is more error prone
than defining the extra column and unique constraint.
Of course, the unique index will use slightly more space. But then
again, you also benefit from the fact that there now is a highly
selective index on the natural key.
So in this case, IMO the constraint wins from the trigger hands down...
Gert-Jan
rich wrote:[vbcol=seagreen]
> Gert-Jan, thanks for the tip!
> I get it and it makes sense.
> I also just tried a trigger (based on the above "test1" table) and it
> works too.
> Not sure what I'd rather do - add a new trigger to a bunch of tables
> or a column and constraint.
> here's the trigger in case anyone is interested:
> CREATE TRIGGER dbo.tr_Test
> ON [dbo].[test1]
> FOR INSERT, UPDATE
> AS
> declare @.cnt int
> BEGIN
> select @.cnt=count(*) from test1 where test in (select test from
> inserted)
> if @.cnt>1 rollback tran --it's always 1 because the newly inserted
or
> updated row is included in the test1 table
> END
> Thanks for everyone's help!
> On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.
Showing posts with label processes. Show all posts
Showing posts with label processes. Show all posts
Sunday, March 11, 2012
Check Constraint UDF: Update vs. Insert
I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!
rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:
> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>
|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
>
>
>
>
> - Show quoted text -
|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
>
>
>
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -
|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne
|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!
rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:
> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>
|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
>
>
>
>
> - Show quoted text -
|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
>
>
>
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -
|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne
|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.
Check Constraint UDF: Update vs. Insert
I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:
> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
> > I am using a check constraint on one of my tables. It appears to me
> > that the constraint processes an insert AFTER the row is inserted on
> > an insert, but BEFORE the data is updated on an update. here's my
> > example:
> > Table:
> > CREATE TABLE [test1] (
> > [test] [int] NOT NULL )
> > Function:
> > CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> > RETURNS INT
> > AS
> > BEGIN
> > declare @.cnt int
> > select @.cnt = count(*) from test1 where test=@.test
> > return @.cnt
> > END
> > here's my constraint added to the table test1:
> > alter table test1 add constraint ck_test1 check
> > (dbo.fn_RI_test([test])=1)
> > Now, if I insert new rows:
> > insert into test1 values (1) - works fine
> > insert into test1 values (2) - works fine
> > insert into test1 values (1) - error:
> > Server: Msg 547, Level 16, State 1, Line 1
> > INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> > The conflict occurred in database 'JHRA_Test', table 'test1', column
> > 'test'.
> > The statement has been terminated.
> > BUT, if I perform an update:
> > update test1 set test=1 where test=2
> > IT WORKS and now there are 2 rows with test=1
> > Am I crazy? Is there any way for me to know in the function whether
> > this is an update or an insert? Any other options? BTW, I don't need
> > to make a single column unique in this manner, my needs are much more
> > complex. this is just an illustration which demonstrates my issue.
> > Thanks!- Hide quoted text -
> - Show quoted text -|||rich wrote:
> Alejandro, thanks for the reply - I was trying to find some resource
> to confirm or deny the workings so I appreciate it.
> I will share the true source of my problem in case you, or others,
> would like to weigh in on the best method.
> Basically, all of our data tables have a delete_flag bit field.
> At the same time, I am trying to disallow duplicate combination of 3
> fields. (sounds like a primary key, huh?)
> Each table has an Identity field as the primary key.
> I can't create a unique index on the three field combo because I allow
> logical deletes using the delete_flag field.
> I tried including the delete_flag in a four field index, but then you
> can only delete each unique combination once.
> I need unlimited logically deleted rows where my three fields are not
> necessarily unique,
> but only one non-deleted one row for each unique combination.
> make sense?
The solution(s) to the problem "unique constraint with multiple NULLs"
also applies to your case, only in your case the delete flag acts as a
NULL.
So for example, something like the script below could be a solution:
ALTER TABLE YourTable
ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
ALTER TABLE YourTable
ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
HTH,
Gert-Jan|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
> > Alejandro, thanks for the reply - I was trying to find some resource
> > to confirm or deny the workings so I appreciate it.
> > I will share the true source of my problem in case you, or others,
> > would like to weigh in on the best method.
> > Basically, all of our data tables have a delete_flag bit field.
> > At the same time, I am trying to disallow duplicate combination of 3
> > fields. (sounds like a primary key, huh?)
> > Each table has an Identity field as the primary key.
> > I can't create a unique index on the three field combo because I allow
> > logical deletes using the delete_flag field.
> > I tried including the delete_flag in a four field index, but then you
> > can only delete each unique combination once.
> > I need unlimited logically deleted rows where my three fields are not
> > necessarily unique,
> > but only one non-deleted one row for each unique combination.
> > make sense?
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne|||Well, for one thing, using a constraint will avoid unnecessary page
splits, because it will prevent duplicate rows. A trigger will roll back
duplicate rows.
Also, I think that in this case, writing a trigger is more error prone
than defining the extra column and unique constraint.
Of course, the unique index will use slightly more space. But then
again, you also benefit from the fact that there now is a highly
selective index on the natural key.
So in this case, IMO the constraint wins from the trigger hands down...
Gert-Jan
rich wrote:
> Gert-Jan, thanks for the tip!
> I get it and it makes sense.
> I also just tried a trigger (based on the above "test1" table) and it
> works too.
> Not sure what I'd rather do - add a new trigger to a bunch of tables
> or a column and constraint.
> here's the trigger in case anyone is interested:
> CREATE TRIGGER dbo.tr_Test
> ON [dbo].[test1]
> FOR INSERT, UPDATE
> AS
> declare @.cnt int
> BEGIN
> select @.cnt=count(*) from test1 where test in (select test from
> inserted)
> if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> updated row is included in the test1 table
> END
> Thanks for everyone's help!
> On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > rich wrote:
> >
> > > Alejandro, thanks for the reply - I was trying to find some resource
> > > to confirm or deny the workings so I appreciate it.
> >
> > > I will share the true source of my problem in case you, or others,
> > > would like to weigh in on the best method.
> >
> > > Basically, all of our data tables have a delete_flag bit field.
> > > At the same time, I am trying to disallow duplicate combination of 3
> > > fields. (sounds like a primary key, huh?)
> > > Each table has an Identity field as the primary key.
> > > I can't create a unique index on the three field combo because I allow
> > > logical deletes using the delete_flag field.
> > > I tried including the delete_flag in a four field index, but then you
> > > can only delete each unique combination once.
> >
> > > I need unlimited logically deleted rows where my three fields are not
> > > necessarily unique,
> > > but only one non-deleted one row for each unique combination.
> >
> > > make sense?
> >
> > The solution(s) to the problem "unique constraint with multiple NULLs"
> > also applies to your case, only in your case the delete flag acts as a
> > NULL.
> >
> > So for example, something like the script below could be a solution:
> >
> > ALTER TABLE YourTable
> > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> >
> > ALTER TABLE YourTable
> > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> >
> > HTH,
> > Gert-Jan- Hide quoted text -
> >
> > - Show quoted text -|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
> > Gert-Jan, thanks for the tip!
> > I get it and it makes sense.
> > I also just tried a trigger (based on the above "test1" table) and it
> > works too.
> > Not sure what I'd rather do - add a new trigger to a bunch of tables
> > or a column and constraint.
> > here's the trigger in case anyone is interested:
> > CREATE TRIGGER dbo.tr_Test
> > ON [dbo].[test1]
> > FOR INSERT, UPDATE
> > AS
> > declare @.cnt int
> > BEGIN
> > select @.cnt=count(*) from test1 where test in (select test from
> > inserted)
> > if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> > updated row is included in the test1 table
> > END
> > Thanks for everyone's help!
> > On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > > rich wrote:
> > > > Alejandro, thanks for the reply - I was trying to find some resource
> > > > to confirm or deny the workings so I appreciate it.
> > > > I will share the true source of my problem in case you, or others,
> > > > would like to weigh in on the best method.
> > > > Basically, all of our data tables have a delete_flag bit field.
> > > > At the same time, I am trying to disallow duplicate combination of 3
> > > > fields. (sounds like a primary key, huh?)
> > > > Each table has an Identity field as the primary key.
> > > > I can't create a unique index on the three field combo because I allow
> > > > logical deletes using the delete_flag field.
> > > > I tried including the delete_flag in a four field index, but then you
> > > > can only delete each unique combination once.
> > > > I need unlimited logically deleted rows where my three fields are not
> > > > necessarily unique,
> > > > but only one non-deleted one row for each unique combination.
> > > > make sense?
> > > The solution(s) to the problem "unique constraint with multiple NULLs"
> > > also applies to your case, only in your case the delete flag acts as a
> > > NULL.
> > > So for example, something like the script below could be a solution:
> > > ALTER TABLE YourTable
> > > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> > > ALTER TABLE YourTable
> > > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> > > HTH,
> > > Gert-Jan- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:
> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
> > I am using a check constraint on one of my tables. It appears to me
> > that the constraint processes an insert AFTER the row is inserted on
> > an insert, but BEFORE the data is updated on an update. here's my
> > example:
> > Table:
> > CREATE TABLE [test1] (
> > [test] [int] NOT NULL )
> > Function:
> > CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> > RETURNS INT
> > AS
> > BEGIN
> > declare @.cnt int
> > select @.cnt = count(*) from test1 where test=@.test
> > return @.cnt
> > END
> > here's my constraint added to the table test1:
> > alter table test1 add constraint ck_test1 check
> > (dbo.fn_RI_test([test])=1)
> > Now, if I insert new rows:
> > insert into test1 values (1) - works fine
> > insert into test1 values (2) - works fine
> > insert into test1 values (1) - error:
> > Server: Msg 547, Level 16, State 1, Line 1
> > INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> > The conflict occurred in database 'JHRA_Test', table 'test1', column
> > 'test'.
> > The statement has been terminated.
> > BUT, if I perform an update:
> > update test1 set test=1 where test=2
> > IT WORKS and now there are 2 rows with test=1
> > Am I crazy? Is there any way for me to know in the function whether
> > this is an update or an insert? Any other options? BTW, I don't need
> > to make a single column unique in this manner, my needs are much more
> > complex. this is just an illustration which demonstrates my issue.
> > Thanks!- Hide quoted text -
> - Show quoted text -|||rich wrote:
> Alejandro, thanks for the reply - I was trying to find some resource
> to confirm or deny the workings so I appreciate it.
> I will share the true source of my problem in case you, or others,
> would like to weigh in on the best method.
> Basically, all of our data tables have a delete_flag bit field.
> At the same time, I am trying to disallow duplicate combination of 3
> fields. (sounds like a primary key, huh?)
> Each table has an Identity field as the primary key.
> I can't create a unique index on the three field combo because I allow
> logical deletes using the delete_flag field.
> I tried including the delete_flag in a four field index, but then you
> can only delete each unique combination once.
> I need unlimited logically deleted rows where my three fields are not
> necessarily unique,
> but only one non-deleted one row for each unique combination.
> make sense?
The solution(s) to the problem "unique constraint with multiple NULLs"
also applies to your case, only in your case the delete flag acts as a
NULL.
So for example, something like the script below could be a solution:
ALTER TABLE YourTable
ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
ALTER TABLE YourTable
ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
HTH,
Gert-Jan|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
> > Alejandro, thanks for the reply - I was trying to find some resource
> > to confirm or deny the workings so I appreciate it.
> > I will share the true source of my problem in case you, or others,
> > would like to weigh in on the best method.
> > Basically, all of our data tables have a delete_flag bit field.
> > At the same time, I am trying to disallow duplicate combination of 3
> > fields. (sounds like a primary key, huh?)
> > Each table has an Identity field as the primary key.
> > I can't create a unique index on the three field combo because I allow
> > logical deletes using the delete_flag field.
> > I tried including the delete_flag in a four field index, but then you
> > can only delete each unique combination once.
> > I need unlimited logically deleted rows where my three fields are not
> > necessarily unique,
> > but only one non-deleted one row for each unique combination.
> > make sense?
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne|||Well, for one thing, using a constraint will avoid unnecessary page
splits, because it will prevent duplicate rows. A trigger will roll back
duplicate rows.
Also, I think that in this case, writing a trigger is more error prone
than defining the extra column and unique constraint.
Of course, the unique index will use slightly more space. But then
again, you also benefit from the fact that there now is a highly
selective index on the natural key.
So in this case, IMO the constraint wins from the trigger hands down...
Gert-Jan
rich wrote:
> Gert-Jan, thanks for the tip!
> I get it and it makes sense.
> I also just tried a trigger (based on the above "test1" table) and it
> works too.
> Not sure what I'd rather do - add a new trigger to a bunch of tables
> or a column and constraint.
> here's the trigger in case anyone is interested:
> CREATE TRIGGER dbo.tr_Test
> ON [dbo].[test1]
> FOR INSERT, UPDATE
> AS
> declare @.cnt int
> BEGIN
> select @.cnt=count(*) from test1 where test in (select test from
> inserted)
> if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> updated row is included in the test1 table
> END
> Thanks for everyone's help!
> On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > rich wrote:
> >
> > > Alejandro, thanks for the reply - I was trying to find some resource
> > > to confirm or deny the workings so I appreciate it.
> >
> > > I will share the true source of my problem in case you, or others,
> > > would like to weigh in on the best method.
> >
> > > Basically, all of our data tables have a delete_flag bit field.
> > > At the same time, I am trying to disallow duplicate combination of 3
> > > fields. (sounds like a primary key, huh?)
> > > Each table has an Identity field as the primary key.
> > > I can't create a unique index on the three field combo because I allow
> > > logical deletes using the delete_flag field.
> > > I tried including the delete_flag in a four field index, but then you
> > > can only delete each unique combination once.
> >
> > > I need unlimited logically deleted rows where my three fields are not
> > > necessarily unique,
> > > but only one non-deleted one row for each unique combination.
> >
> > > make sense?
> >
> > The solution(s) to the problem "unique constraint with multiple NULLs"
> > also applies to your case, only in your case the delete flag acts as a
> > NULL.
> >
> > So for example, something like the script below could be a solution:
> >
> > ALTER TABLE YourTable
> > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> >
> > ALTER TABLE YourTable
> > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> >
> > HTH,
> > Gert-Jan- Hide quoted text -
> >
> > - Show quoted text -|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
> > Gert-Jan, thanks for the tip!
> > I get it and it makes sense.
> > I also just tried a trigger (based on the above "test1" table) and it
> > works too.
> > Not sure what I'd rather do - add a new trigger to a bunch of tables
> > or a column and constraint.
> > here's the trigger in case anyone is interested:
> > CREATE TRIGGER dbo.tr_Test
> > ON [dbo].[test1]
> > FOR INSERT, UPDATE
> > AS
> > declare @.cnt int
> > BEGIN
> > select @.cnt=count(*) from test1 where test in (select test from
> > inserted)
> > if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> > updated row is included in the test1 table
> > END
> > Thanks for everyone's help!
> > On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > > rich wrote:
> > > > Alejandro, thanks for the reply - I was trying to find some resource
> > > > to confirm or deny the workings so I appreciate it.
> > > > I will share the true source of my problem in case you, or others,
> > > > would like to weigh in on the best method.
> > > > Basically, all of our data tables have a delete_flag bit field.
> > > > At the same time, I am trying to disallow duplicate combination of 3
> > > > fields. (sounds like a primary key, huh?)
> > > > Each table has an Identity field as the primary key.
> > > > I can't create a unique index on the three field combo because I allow
> > > > logical deletes using the delete_flag field.
> > > > I tried including the delete_flag in a four field index, but then you
> > > > can only delete each unique combination once.
> > > > I need unlimited logically deleted rows where my three fields are not
> > > > necessarily unique,
> > > > but only one non-deleted one row for each unique combination.
> > > > make sense?
> > > The solution(s) to the problem "unique constraint with multiple NULLs"
> > > also applies to your case, only in your case the delete flag acts as a
> > > NULL.
> > > So for example, something like the script below could be a solution:
> > > ALTER TABLE YourTable
> > > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> > > ALTER TABLE YourTable
> > > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> > > HTH,
> > > Gert-Jan- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.
Friday, February 10, 2012
Changing Windows accounts used by reporting services
I want to change the accounts used by Reporting Services processes. I am
tempted to just change the "Log on as" account for the ReportServer service
(in the Services management console) and move the Reports and ReportServer
web apps to a new Win 2003 AppPool that runs with the desired account.
Can I safely make these changes without breaking things? What rights to the
accounts I assign to the service and web apps need? I have searched Books
Online and can not find an answer.
Note that I do NOT want to change redentials for accesing the SQL Server
databases, which is what rsconfig.exe appears to be for.)Hi Stephen ,
From your descriptions, I understood that you would like to know change the
ReportServer account to limited privilege and change the application pool.
Have I understood you? Correct me if I was wrong.
Based on my knowledge, I have noticed that topic "Service Account" in BOL
specify the account under which the ReportServer Windows service runs.Here
is its link in MSDN Online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm
/f1_rsc_installation_v1_197m.asp
Secondly, If you have change identity of application pool to be
Configurable, make sure the account is member of IWAM_<Machine Name> and
IUSER_<Machine Name>
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
tempted to just change the "Log on as" account for the ReportServer service
(in the Services management console) and move the Reports and ReportServer
web apps to a new Win 2003 AppPool that runs with the desired account.
Can I safely make these changes without breaking things? What rights to the
accounts I assign to the service and web apps need? I have searched Books
Online and can not find an answer.
Note that I do NOT want to change redentials for accesing the SQL Server
databases, which is what rsconfig.exe appears to be for.)Hi Stephen ,
From your descriptions, I understood that you would like to know change the
ReportServer account to limited privilege and change the application pool.
Have I understood you? Correct me if I was wrong.
Based on my knowledge, I have noticed that topic "Service Account" in BOL
specify the account under which the ReportServer Windows service runs.Here
is its link in MSDN Online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm
/f1_rsc_installation_v1_197m.asp
Secondly, If you have change identity of application pool to be
Configurable, make sure the account is member of IWAM_<Machine Name> and
IUSER_<Machine Name>
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)