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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to make this?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Swirl
Starting Member

United Kingdom
2 Posts

Posted - 05/05/2013 :  16:32:36  Show Profile  Reply with Quote
I have the following table:


create table Sale(
bankname char(8) null,
controleoptiename char(10) not null,
creditcardnummer numeric(19) null,
username char(10) not null,
accountnummer numeric(7) null,
constraint pk_username primary key(username)
)

I'm trying to make a case/trigger with the following rule:
If the word "Creditcard" is inserted in controleoptiename then creditnummer must contain numbers else it will be NULL.

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 05/05/2013 :  20:49:26  Show Profile  Reply with Quote
Add a table-level check constraint like shown below:

alter table Sale
add constraint chkAccountNumber check
(
	(controleoptienname = 'Creditcard' and accountnummer is not null)
	or
	(controleoptienname <> 'Creditcard' and accountnummer is null)
);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/06/2013 :  02:19:41  Show Profile  Reply with Quote
it should be

alter table Sale
add constraint chkreditCardInfo check
(
	controleoptienname <> 'Creditcard' OR creditnummer IS NOT NULL
);


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Swirl
Starting Member

United Kingdom
2 Posts

Posted - 05/06/2013 :  04:01:27  Show Profile  Reply with Quote
ty guys !
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/06/2013 :  04:28:58  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 05/06/2013 :  08:18:41  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

it should be

alter table Sale
add constraint chkreditCardInfo check
(
	controleoptienname <> 'Creditcard' OR creditnummer IS NOT NULL
);


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


No. It should not be.

The OP's requirement is a "logical biconditional" which cannot be expressed as a simple OR condition. Think about the case where the controleoptienname = 'Cash' and creditnummer = 12345. This condition should not be allowed.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 05/06/2013 :  11:01:32  Show Profile  Reply with Quote
James is right.
Just a minor modification to his query:


alter table dbo.Sale
add constraint chkAccountNumber check
(
       (controleoptiename = 'Creditcard' and creditcardnummer is not null)  
       or
       (controleoptiename <> 'Creditcard' and creditcardnummer is null)  
);



visakh16's code does not work in the following scenario:

INSERT INTO dbo.sale(bankname, controleoptiename, creditcardnummer, username, accountnummer) 
       VALUES('HSBC', 'Cash', 12345, 'Joe Smoke', 12345.0); -- row will be added when it should not

Edited by - MuMu88 on 05/06/2013 11:13:22
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 05/06/2013 :  11:06:08  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

James is right.
Just a minor modification to his query:


alter table dbo.Sale
add constraint chkAccountNumber check
(
       (controleoptiename = 'Creditcard' and creditcardnummer is not null)  
       or
       (controleoptiename <> 'Creditcard' and creditcardnummer is null)  
);



visakh16's code does not work in the following scenario:

INSERT INTO dbo.sale(bankname, controleoptiename, creditcardnummer, username, accountnummer) 
       VALUES('HSBC', 'Cash', 12345, 'Joe Smoke', 12345.0); -- row will be added when it should not


Ah, sorry, I missed that. Thank you MuMu88!!!

Would it help if I said that, that was really what I had in mind?

Edited by - James K on 05/06/2013 11:17:06
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/07/2013 :  00:33:35  Show Profile  Reply with Quote
quote:
Originally posted by James K

quote:
Originally posted by visakh16

it should be

alter table Sale
add constraint chkreditCardInfo check
(
	controleoptienname <> 'Creditcard' OR creditnummer IS NOT NULL
);


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


No. It should not be.

The OP's requirement is a "logical biconditional" which cannot be expressed as a simple OR condition. Think about the case where the controleoptienname = 'Cash' and creditnummer = 12345. This condition should not be allowed.


yep thats true
thanks for the catch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000