Check Contraint question

I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),
1;LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:

>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Check Contraint question

I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),[LicensePlateNumber]),0) =
0)
which works fine, throwing an error if those characters are entered. Is the
re a way to have it not throw an error, but rather just remove the offending
characters if entered? ThanksNo, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:

>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showin
g
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicenseP
late, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Check Contraint question

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C67383.F9AD44F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + = ']%'),[LicensePlateNumber]),0) =3D 0)
which works fine, throwing an error if those characters are entered. Is = there a way to have it not throw an error, but rather just remove the = offending characters if entered? Thanks
--=_NextPart_000_0008_01C67383.F9AD44F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I have the following check constraint

(isnull(patindex(('%[' + ' ' + = char(9) + char(10) + char(13) + ']%'),[LicensePlateNumber]),0) =3D 0)

which works fine, throwing an = error if those characters are entered. Is there a way to have it not throw = an error, but rather just remove the offending characters if entered? = Thanks
--=_NextPart_000_0008_01C67383.F9AD44F0--No, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:
>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showing
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicensePlate, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP

CHECK CONTRAINT issue

I need something like this:
ALTER TABLE MatchResults
ADD CONSTRAINT ck_MatchResults
CHECK (
NOT EXISTS (
SELECT B1.Id, B2.Id
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
)
)
But it yields following errors:
Server: Msg 8142, Level 16, State 1, Line 1
Subqueries are not supported in CHECK constraints, table 'MatchResults'.
Server: Msg 1759, Level 16, State 1, Line 1
Invalid column 'ProfielId' is specified in a constraint or computed-column d
efinition.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
I already tried this:
CREATE TRIGGER cti_MatchResults ON MatchResults
INSTEAD OF INSERT
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
INSERT INTO MatchResults
SELECT ParentId, DatMatch, Id, Updated, Deleted
FROM inserted
ELSE
RAISERROR ('WARNING (Insert): you are inserting faulty data into table!', 0,
1) WITH NOWAIT
END
GO
CREATE TRIGGER ctu_MatchResults ON MatchResults
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
UPDATE M
SET M.ParentId = I.ParentId, M.DatMatch = I.DatMatch, M.Id = I.Id, M.Updated
= I.Updated, M.Deleted = I.Deleted
FROM MatchResults M, inserted I
WHERE M.ParentId = I.ParentId AND M.DatMatch = I.DatMatch AND M.Id = I.Id
ELSE
RAISERROR (''WARNING (Update): you are inserting faulty data into table!', 0
,1) WITH NOWAIT
END
GO
But for some reason this has no effect at all when I try to update a record
with faulty data (that violates the contraint).
I am updating it via a stored procedure:
CREATE PROCEDURE xsp_AddMatchResult
(
@.ParentId INT,
@.DatMatch DATETIME = NULL,
@.Id INT,
@.Updated DATETIME = NULL,
@.Deleted BIT = 0
) AS SET NOCOUNT ON
IF (@.Updated IS NULL) SET @.Updated = GETDATE()
IF (@.DatMatch IS NULL) SET @.DatMatch = @.Updated
IF EXISTS(SELECT ParentId FROM MatchResults
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id)
UPDATE MatchResults SET Updated=@.Updated, Deleted=@.Deleted
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id
ELSE
INSERT INTO MatchResults(ParentId,DatMatch,Id,Update
d,Deleted)
VALUES(@.ParentId,@.DatMatch,@.Id,@.Updated,
@.Deleted)
GO
Does anyone have a clue?
LisaHi Lisa
Please check if column ProfielId exists in the Table Bedrijven
thanks and regards
Chandra
"Lisa Pearlson" wrote:

> I need something like this:
> ALTER TABLE MatchResults
> ADD CONSTRAINT ck_MatchResults
> CHECK (
> NOT EXISTS (
> SELECT B1.Id, B2.Id
> FROM MatchResults M, Bedrijven B1, Bedrijven B2
> WHERE M.Deleted!=1
> AND B1.Id = M.ParentId
> AND B2.Id = M.Id
> AND (B1.ProfielId!=3 OR B2.ProfielId=3)
> )
> )
> But it yields following errors:
> Server: Msg 8142, Level 16, State 1, Line 1
> Subqueries are not supported in CHECK constraints, table 'MatchResults'.
> Server: Msg 1759, Level 16, State 1, Line 1
> Invalid column 'ProfielId' is specified in a constraint or computed-column
definition.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
>
> I already tried this:
> CREATE TRIGGER cti_MatchResults ON MatchResults
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> BEGIN
> IF (NOT EXISTS (
> SELECT B1.Id, B2.Id
> FROM inserted M, Bedrijven B1, Bedrijven B2
> WHERE M.Deleted!=1
> AND B1.Id = M.ParentId
> AND B2.Id = M.Id
> AND (B1.ProfielId!=3 OR B2.ProfielId=3)
> ))
> INSERT INTO MatchResults
> SELECT ParentId, DatMatch, Id, Updated, Deleted
> FROM inserted
> ELSE
> RAISERROR ('WARNING (Insert): you are inserting faulty data into table!',
0,1) WITH NOWAIT
> END
> GO
> CREATE TRIGGER ctu_MatchResults ON MatchResults
> INSTEAD OF UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF (NOT EXISTS (
> SELECT B1.Id, B2.Id
> FROM inserted M, Bedrijven B1, Bedrijven B2
> WHERE M.Deleted!=1
> AND B1.Id = M.ParentId
> AND B2.Id = M.Id
> AND (B1.ProfielId!=3 OR B2.ProfielId=3)
> ))
> UPDATE M
> SET M.ParentId = I.ParentId, M.DatMatch = I.DatMatch, M.Id = I.Id, M.Upd
ated = I.Updated, M.Deleted = I.Deleted
> FROM MatchResults M, inserted I
> WHERE M.ParentId = I.ParentId AND M.DatMatch = I.DatMatch AND M.Id = I.I
d
> ELSE
> RAISERROR (''WARNING (Update): you are inserting faulty data into table!'
, 0,1) WITH NOWAIT
> END
> GO
> But for some reason this has no effect at all when I try to update a recor
d with faulty data (that violates the contraint).
> I am updating it via a stored procedure:
> CREATE PROCEDURE xsp_AddMatchResult
> (
> @.ParentId INT,
> @.DatMatch DATETIME = NULL,
> @.Id INT,
> @.Updated DATETIME = NULL,
> @.Deleted BIT = 0
> ) AS SET NOCOUNT ON
> IF (@.Updated IS NULL) SET @.Updated = GETDATE()
> IF (@.DatMatch IS NULL) SET @.DatMatch = @.Updated
> IF EXISTS(SELECT ParentId FROM MatchResults
> WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id)
> UPDATE MatchResults SET Updated=@.Updated, Deleted=@.Deleted
> WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id
> ELSE
> INSERT INTO MatchResults(ParentId,DatMatch,Id,Update
d,Deleted)
> VALUES(@.ParentId,@.DatMatch,@.Id,@.Updated,
@.Deleted)
> GO
> Does anyone have a clue?
> Lisa|||Lisa
It's hard to suggest something without seeing the data. But if your stored p
rocedure does the job for you I would not change it to the trigger.
"Lisa Pearlson" <no@.spam.plz> wrote in message news:%23ETDzrEUFHA.2128@.TK2MS
FTNGP15.phx.gbl...
I need something like this:
ALTER TABLE MatchResults
ADD CONSTRAINT ck_MatchResults
CHECK (
NOT EXISTS (
SELECT B1.Id, B2.Id
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
)
)
But it yields following errors:
Server: Msg 8142, Level 16, State 1, Line 1
Subqueries are not supported in CHECK constraints, table 'MatchResults'.
Server: Msg 1759, Level 16, State 1, Line 1
Invalid column 'ProfielId' is specified in a constraint or computed-column d
efinition.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
I already tried this:
CREATE TRIGGER cti_MatchResults ON MatchResults
INSTEAD OF INSERT
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
INSERT INTO MatchResults
SELECT ParentId, DatMatch, Id, Updated, Deleted
FROM inserted
ELSE
RAISERROR ('WARNING (Insert): you are inserting faulty data into table!', 0,
1) WITH NOWAIT
END
GO
CREATE TRIGGER ctu_MatchResults ON MatchResults
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
UPDATE M
SET M.ParentId = I.ParentId, M.DatMatch = I.DatMatch, M.Id = I.Id, M.Updated
= I.Updated, M.Deleted = I.Deleted
FROM MatchResults M, inserted I
WHERE M.ParentId = I.ParentId AND M.DatMatch = I.DatMatch AND M.Id = I.Id
ELSE
RAISERROR (''WARNING (Update): you are inserting faulty data into table!', 0
,1) WITH NOWAIT
END
GO
But for some reason this has no effect at all when I try to update a record
with faulty data (that violates the contraint).
I am updating it via a stored procedure:
CREATE PROCEDURE xsp_AddMatchResult
(
@.ParentId INT,
@.DatMatch DATETIME = NULL,
@.Id INT,
@.Updated DATETIME = NULL,
@.Deleted BIT = 0
) AS SET NOCOUNT ON
IF (@.Updated IS NULL) SET @.Updated = GETDATE()
IF (@.DatMatch IS NULL) SET @.DatMatch = @.Updated
IF EXISTS(SELECT ParentId FROM MatchResults
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id)
UPDATE MatchResults SET Updated=@.Updated, Deleted=@.Deleted
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id
ELSE
INSERT INTO MatchResults(ParentId,DatMatch,Id,Update
d,Deleted)
VALUES(@.ParentId,@.DatMatch,@.Id,@.Updated,
@.Deleted)
GO
Does anyone have a clue?
Lisa|||I'm not sure why you couldn't use a standard After trigger for this. You wan
t
other check and unique constraints to fire. So why not something like:
Create Trigger trigMatchResultsIU
On dbo.MatchResults
For Insert, Update
As
If Not Exists(
Select *
From inserted As I, dbo.Bedrijven As B
Where I.Deleted <> 1
And (
(B.Id = I.ParentId And B.ProfielId <> 3)
Or (B.Id = I.Id And B.ProfielId = 3)
)
)
Begin
Raiserror('Warning Will Robenson! Danger! Danger!, 16, 1)
Rollback Tran
End
Thomas|||You can simply call "rollback tran" inside a trigger to undo the
insert/delete even if you didn't call "begin tran" yourself?
I didn't know that.
Can you attatch multiple triggers to insert/update on same table? Do they
get executed in the order the triggers were created?
Lisa
"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:OezbXQLUFHA.3584@.TK2MSFTNGP14.phx.gbl...
> I'm not sure why you couldn't use a standard After trigger for this. You
> want other check and unique constraints to fire. So why not something
> like:
> Create Trigger trigMatchResultsIU
> On dbo.MatchResults
> For Insert, Update
> As
> If Not Exists(
> Select *
> From inserted As I, dbo.Bedrijven As B
> Where I.Deleted <> 1
> And (
> (B.Id = I.ParentId And B.ProfielId <> 3)
> Or (B.Id = I.Id And B.ProfielId = 3)
> )
> )
> Begin
> Raiserror('Warning Will Robenson! Danger! Danger!, 16, 1)
> Rollback Tran
> End
>
> Thomas
>
>|||Yes you can use Rollback Tran because each DML statement (Insert, Update,
Delete) is in an implicit transaction.
Yes, you can attach multiple Insert, Update and/or Delete triggers on the sa
me
table although you should do it with caution. In general, it is difficult to
determine any sort of firing order with multiple triggers. Thus, I would
recommend that you assume that the order is random when writing triggers.
That said, there is a system stored proc called sp_settriggerorder which wil
l
allow you to specify which trigger should fire first or last. That's about t
he
extent of the firing order.
HTH
Thomas
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23HXATgQUFHA.580@.TK2MSFTNGP15.phx.gbl...
> You can simply call "rollback tran" inside a trigger to undo the insert/de
lete
> even if you didn't call "begin tran" yourself?
> I didn't know that.
> Can you attatch multiple triggers to insert/update on same table? Do they
get
> executed in the order the triggers were created?
> Lisa
> "Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
> news:OezbXQLUFHA.3584@.TK2MSFTNGP14.phx.gbl...
>

CHECK contraint

Hi,
Hi, this is the situation.. I have somewhat simplified the tables here that
are insignificant to the structure.
There are organizations, who can be assigned different codes and users (by
an administrator).
These users can access all codes that are assigned to the same organization
that the user himself belongs to.
However, these users (parents) can create sub-users, and assign them a
SUBSET of all the codes that the parent user itself has access to.
So the administrator creates an organization "O" and and assignes user "A".
And assignes codes 1,2 and 3 to this organization. So user A has access to
codes 1, 2 and 3.
Then user A may create one or more users, such as user B, and assign it a
subset of the codes it has access to, such as 2 and 3.
While this type of nesting could go on, in reality we only have 3 types of
users, Admin, A-type users (created by Admin), and B-type users (created by
A-type users).
This is what I came up with:
-- organizations
CREATE TABLE organizations (
`organizationid` INT UNSIGNED PRIMARY KEY
`name` VARCHAR(100)
)
-- each organization can be assigned multiple codes
CREATE codes (
`codeid` INT UNSIGNED PRIMARY KEY,
`organizationid` INT UNSIGNED FOREIGN KEY organizations (`organizationid)
)
-- admins, A-type users and B-type users
CREATE TABLE users (
`userid` INT UNSIGNED PRIMARY KEY,
`userparentid` INT UNSIGNED FOREIGN KEY users (`userid`),
`organizationid` INT UNSIGNED FOREIGN KEY organizations (`organizationid`)
`name` VARCHAR(50)
)
-- map B-users to subset of codes that its parent has access to
CREATE users_codes (
`userid` INT UNSIGNED NOT NULL FOREIGN KEY users (`userid`),
`codeid` INT UNSIGNED NOT NULL FOREIGN KEY codes (`codeid`)
)
This is how I differentiate between Admins, A-type users and B-type users.
Admins don't have a parent id (NULL) and no organization id (NULL).
A-type users have Admins as parent id or NULL, and non-null
organizationid's.
B-type users have parentid's pointing to A-type users and non-null
organization id's.
There's some duplication because B-type id's can get the organizationid from
its parent (A-type) user too.
codes are assigned to organizations, and to B-type users only, not to A-type
users or Admins.
Somehow I am not comfortable with this model. It seems like I am doing
something wrong and there's a better solution.
Anyway, if I do use this model, I need some CHECK constraints on
users_codes, that makes sure that B-type users (with parent-id) can only be
assigned codeids that their parents (A-type users) have access to.
How do I put this in some kind of CHECK constraint?
Please advise on the structure / model, as well as the CHECK constraint(s)
required.
LisaHow about a standard nested set model with nodes that hold the various
codes
CREATE TABLE Tree
(node_id INTEGER NOT NULL
REFERENCES Nodes(node_id),
lft INTEGER NOT NULL UNIQUE
CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt));
CREATE TABLE Nodes
(node_id INTEGER NOT NULL PRIMARY KEY,
code_1 INTEGER DEFAULT 0 NOT NULL
CHECK (code_1 IN (0,1)),
code_2 INTEGER DEFAULT 0 NOT NULL
CHECK (code_2 IN (0,1)),
code_3 INTEGER DEFAULT 0 NOT NULL
CHECK (code_3 IN (0,1)),
code_4 INTEGER DEFAULT 0 NOT NULL
CHECK (code_4 IN (0,1)),
code_5 INTEGER DEFAULT 0 NOT NULL
CHECK (code_5 IN (0,1))
);
The rule seems to be that you can only inherit codes from a superior.
You could write this with a CREATE ASSERTION in SQL-92; SQL Server
needs a stored procedure that checks for allowed codes before doing an
insertion of a subordinate.|||Interesting concept, but I am having some difficulty grasping how this fits
with my situation. Is the treestructure just for codes only? Users point to
a certain node? An organization (and subusers) could be assigned up to 65536
codes. So it has to be a set, not fields in a table (like the 5 code fields
in your example).
I think my model is more like what I need, but what makes me feel
uncomfortabe is that 3 types of users, Admins, A-type and B-type users, are
all in the same users table. The type of user can only be recognized and
distinguished indirectely by whether organizationid and/or userparentid is
NULL or not.
And A-type users are not linked directely to codes, but are linked to
organizations, and codes are also linked to organizations and via the
organization, A-type users are INDIRECTELY linked to these codes.
But B-type users require a subset of codes, thus they have to be linked
DIRECTELY to the codes table:
( A-type users )===>( Organizations )<===( Codes )<===( B-type users )
Anyway, let me ask you another question... can check constrains refer to
other tables?
CREATE TABLE A (
val INT
)
CREATE TABLE B (
val INT CHECK( val > A(val))
)
something like this?
Lisa
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141180347.823645.141760@.t39g2000cwt.googlegroups.com...
> How about a standard nested set model with nodes that hold the various
> codes
> CREATE TABLE Tree
> (node_id INTEGER NOT NULL
> REFERENCES Nodes(node_id),
> lft INTEGER NOT NULL UNIQUE
> CHECK (lft > 0),
> rgt INTEGER NOT NULL UNIQUE,
> CHECK (lft < rgt));
>
> CREATE TABLE Nodes
> (node_id INTEGER NOT NULL PRIMARY KEY,
> code_1 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_1 IN (0,1)),
> code_2 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_2 IN (0,1)),
> code_3 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_3 IN (0,1)),
> code_4 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_4 IN (0,1)),
> code_5 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_5 IN (0,1))
> );
> The rule seems to be that you can only inherit codes from a superior.
> You could write this with a CREATE ASSERTION in SQL-92; SQL Server
> needs a stored procedure that checks for allowed codes before doing an
> insertion of a subordinate.
>

check constraints

HI all,
I have a contraint on a table, the actual contriant does'nt matter for this
question, however it is something like "(len(ltrim([perildesc])) <> 0)'
which in conjucntion to not allowinh nulls, does not allow spaces (If there
is a better way let me know)
But what I want to know is, when the contraint is breached, an error message
is presented to the user, How can I trap for this particular contraint and
give a more meaningfull error messge. The error number is 547, which is a
generic contraint error message
Thanks
RobertRobert Bravery (me@.u.com) writes:
> I have a contraint on a table, the actual contriant does'nt matter for
> this question, however it is something like "(len(ltrim([perildesc])) <>
> 0)' which in conjucntion to not allowinh nulls, does not allow spaces
> (If there is a better way let me know) But what I want to know is, when
> the contraint is breached, an error message is presented to the user,
> How can I trap for this particular contraint and give a more meaningfull
> error messge. The error number is 547, which is a generic contraint
> error message
There is not really any good way to do this. Of course, you can examine
the error text and extract the constraint name, and you could have a lookup
table that translates the constraint name to an error message.
The way I see it, the purpose of a constraint is not to trap errors
committed by the user, but to trap errors committed by the GUI/middle layer.
That is, the GUI is responsible for validating the user input.
Yes, this means that rules needs to be in two places, but for the GUI to
work well, this may be almost necessary. Say that you have one field A
that must be blank if B is filled in. The GUI should disable B as soon there
is data in A. Just permitting everything, and the hope that the database
validates it all, is not always a feasible strategy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks
This was what I thought to be the case.
Robert
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B180CEC3B3Yazorman@.127.0.0.1...
> Robert Bravery (me@.u.com) writes:
> There is not really any good way to do this. Of course, you can examine
> the error text and extract the constraint name, and you could have a
lookup
> table that translates the constraint name to an error message.
> The way I see it, the purpose of a constraint is not to trap errors
> committed by the user, but to trap errors committed by the GUI/middle
layer.
> That is, the GUI is responsible for validating the user input.
> Yes, this means that rules needs to be in two places, but for the GUI to
> work well, this may be almost necessary. Say that you have one field A
> that must be blank if B is filled in. The GUI should disable B as soon
there
> is data in A. Just permitting everything, and the hope that the database
> validates it all, is not always a feasible strategy.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could use an INSTEAD OF trigger and check perildesc in that and then use
RAISERROR to return your own user definied message and error number.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Robert Bravery" <me@.u.com> wrote in message
news:uFouplRaGHA.3304@.TK2MSFTNGP04.phx.gbl...
> HI all,
> I have a contraint on a table, the actual contriant does'nt matter for
> this
> question, however it is something like "(len(ltrim([perildesc])) <> 0)'
> which in conjucntion to not allowinh nulls, does not allow spaces (If
> there
> is a better way let me know)
> But what I want to know is, when the contraint is breached, an error
> message
> is presented to the user, How can I trap for this particular contraint and
> give a more meaningfull error messge. The error number is 547, which is a
> generic contraint error message
> Thanks
> Robert
>|||Now there is an idea,
Thanks
RObert
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:#6FTJHTaGHA.4424@.TK2MSFTNGP02.phx.gbl...
> You could use an INSTEAD OF trigger and check perildesc in that and then
use
> RAISERROR to return your own user definied message and error number.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:uFouplRaGHA.3304@.TK2MSFTNGP04.phx.gbl...
and
a
>