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...
>