SQL Server Forums
Profile | Register | 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?
 New Topic  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

3712 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
52323 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
52323 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

3712 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

547 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

3712 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
52323 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000