check constraint implemented in trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

check constraint implemented in trigger

Author

Topic

j2dizzo
Starting Member

6 Posts

Posted - 2004-07-11 : 07:26:41

I know that SQL Server doesn't support subqueries in check constraint. So how do I then implement the check constraint. I tried using a trigger but I'm having difficulties with the syntax. Could someone help me. Here's the check constraint

check(((Ph_P1Min is null)or(Ph_P1Max is null)or(Ph_P1Min<=Ph_P1Max))and((Ph_P1Min is null)or((select Ac_Calculated from Action where Ac_Id=Ph_Action)=1) or(Ph_P1Min<=all(select Pc_P1 from Procedure where Pc_Phase=Ph_Id))) and((Ph_P1Max is null)
or((select Ac_Calculated from Action where Ac_Id=Ph_Action)=1)
or(Ph_P1Max>=all(select Pc_P1 from Procedure where Pc_Phase=Ph_Id))) and(((select Ac_Calculated from Action where Ac_Id=Ph_Action)=0)
or(Ph_RawMat is not null)))

and here's the trigger that I converted it to

create trigger trInsUpd_Phase
on Phase
for insert, update as
if (select * from inserted, Action, Procedure where (((inserted.Ph_P1Min is null)or(inserted.Ph_P1Max is null)or(inserted.Ph_P1Min <= inserted.Ph_P1Max))and
((inserted.Ph_P1Min is null)or((select Ac_Calculated from Action where Action.Ac_Id = inserted.Ph_Action) = 1)or(inserted.Ph_P1Min <= all(select Pc_P1 from Procedure where Procedure.Pc_Phase = inserted.Ph_Id)))and((inserted.Ph_P1Max is null)or((select Ac_Calculated from Action where Action.Ac_Id = inserted.Ph_Action) = 1)or(inserted.Ph_P1Max >= all(select Pc_P1 from Procedure where Procedure.Pc_Phase = inserted.Ph_Id)))and(((select Ac_Calculated from Action where Action.Ac_Id = inserted.Ph_Action) = 0)or(inserted.Ph_RawMat is not null)))) = 0

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-11 : 14:05:48

????? Are you trying to see if this whatever this is returns any rows? If so, you need to change your "= 0" to "IS NULL". You SERIOUSLY need to consider rewriting this though so it makes sense. Do you know what this is doing?


	select * 
	from 
		inserted, 
		Action, 
		Procedure 
	where

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-11 : 15:05:28

I imagine what is being asked for is something along the lines of


IF EXISTS
(
    SELECT *
    FROM   inserted
           JOIN Action
               ON Ac_Id=Ph_Action
           JOIN Procedure 
               ON Pc_Phase=Ph_Id
    WHERE  <i>Stuff that finds rows that are Bad</i>
)
BEGIN
    ROLLBACK
--  ... possible RAISERROR here ...
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources