Using not equal too with the OR operator

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 2005 Forums

Transact-SQL (2005)

Using not equal too with the OR operator

Author

Topic

djheath
Starting Member

7 Posts

Posted - 2010-02-26 : 10:31:34

Hi, I would be grateful for some help with this because it is driving me mad! Maybe it's easy and I cant see the wood for the trees!

I have a database which holds network kit. One table holds circuit information which can be of a type, DSL, MPLS, RXN etc. The circuit types "DSL", "MPLS", "RXN" are held in a reference table. There is certain information which some circuits must have and certain information which circuits should not have. For this particular scenario a circuit of type DSL can have a PSTN number associated with it. Any other circuit should throw a trigger to say no PSTN number is allowed. Recently, there has been a need to add a new circuit type DSLNonStd, which should also have a PSTN number associated with it, but the trigger is still getting thrown, when it shouldn't. The trigger is as follows:


IF EXISTS 
	(SELECT 1 
	 FROM	T_CIRCUIT c,
			T_CIRCUIT_TYPE ct
	 WHERE  c.circuit_type_id = ct.circuit_type_id
	 AND	ct.Name != 'DSL'
	 AND   (   MPLS_DSL_PSTN_Num IS NOT NULL)
	 AND	c.circuit_ID IN	
			(SELECT circuit_ID from inserted)) 
BEGIN 
    RAISERROR ('MPLS-DSL specific columns (MPLS_DSL_PSTN_Num) can only be populated for MPLS-DSL circuits', 16, 1)
	ROLLBACK TRAN
	RETURN
END;

So I edited the code to the following:

IF EXISTS 
	(SELECT 1 
	 FROM	T_CIRCUIT c,
			T_CIRCUIT_TYPE ct
	 WHERE  c.circuit_type_id = ct.circuit_type_id
	 AND	(ct.Name != 'DSL' OR ct.Name != 'DSLNonStd') -- New Code to check for DSLNonStd circuit types too
	 AND   (   MPLS_DSL_Option IS NOT NULL
			OR MPLS_DSL_Product IS NOT NULL
			OR MPLS_DSL_PSTN_Num IS NOT NULL)
	 AND	c.circuit_ID IN	
			(SELECT circuit_ID from inserted)) 
BEGIN 
    RAISERROR ('MPLS-DSL specific columns (MPLS_DSL_Option, MPLS_DSL_Product, MPLS_DSL_PSTN_Num) can only be populated for MPLS-DSL circuits', 16, 1)
	ROLLBACK TRAN
	RETURN
END;

But the trigger is still thrown. I can see why because using the or, it will always return all results from the circuit table like so:
Circuit table contents:


select * from t_circuit_type where (name != 'DSL' or name !='DSLNonStd')

1	DSL	MPLS	NULL
2	Dedicated	MPLS	NULL
3	Resilient	MPLS	NULL
4	Single	RXN	NULL
5	Dual	RXN	NULL
6	Split_Site	RXN	NULL
7	DedNonStd	MPLS	NULL
8	DSLNonStd	MPLS	NULL
9	RXNNonStd	RXN	NULL

I am not sure how best to implement this, so that the trigger will only fire if the circuit type is not DSL or not DSLNonStd?

Any ideas?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-26 : 11:05:57

quote:
AND (ct.Name != 'DSL' OR ct.Name != 'DSLNonStd')

I think that since you are using Or condition, it will become true for all scenaria..

Example : If you are inserting 'DSLNonStd' in that case ct.Name != 'DSL' will be true .
If you are inserting 'DSL' in that case ct.Name != 'DSLNonStd' will be true .

So the trigger will be raised.

Either you use AND condition or use NOT In ('DSLNonStd' , 'DSL' )

Try it and let us know if this satisfies your requirment..

Regards,
Bohra

djheath
Starting Member

7 Posts

Posted - 2010-03-01 : 09:52:39

Thanks so much. Yes, you're right. I have it fixed now. I couldn't see the easy answer and was over complicating it. I now have:

IF EXISTS 
	(SELECT 1 
	 FROM	T_CIRCUIT c,
			T_CIRCUIT_TYPE ct
	 WHERE  c.circuit_type_id = ct.circuit_type_id
	 AND	ct.Name not in('DSL', 'DSLNonStd')
	 AND   (   MPLS_DSL_Option IS NOT NULL
			OR MPLS_DSL_Product IS NOT NULL
			OR MPLS_DSL_PSTN_Num IS NOT NULL)
	 AND	c.circuit_ID IN	
			(SELECT circuit_ID from inserted)) 
BEGIN 
    RAISERROR ('MPLS-DSL specific columns (MPLS_DSL_Option, MPLS_DSL_Product, MPLS_DSL_PSTN_Num) can only be populated for MPLS-DSL circuits', 16, 1)
	ROLLBACK TRAN
	RETURN
END;

Easy!
Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources