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.
| Author |
Topic |
|
wessie
Starting Member
1 Post |
 Posted - 2002-07-09 : 03:26:24
|
| Well I looking voor a nice way to check a creditcard number with a contraint. I don't want to do it in the client software. So I thought I could use a contraint. But i can't get it right. Has anyone an idea? Please help me I'm desperate!Trying is the first step toward failure-Homer Simpson |
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-07-09 : 06:44:18
|
| IMHO, you can't put enough validation logic into a column data constraint. The best way would be to write an INSERT/UPDATE trigger to validate the action. This way, you have programmatical power to parse validate and preformat the data.Daniel Small MIAPwww.danielsmall.com IT Factoring |
 |
|
|
macka
Posting Yak Master
162 Posts |
Posted - 2002-07-09 : 07:49:22
|
| This is a difficult thing to accomplish well!Although you can check the length, check that it is a number and that its about the right length, there are a few Caveats:1) Some card numbers will be longer than others. This is true of Switch Cards in the UK. Some cards carry additional information such as issue numbers, which would be needed to validate the card.2) There are also a number of resources on the web which provide mappings between card types and card ranges. ie. Visa is from 1234-2345, MasterCard from 3456-4567 etc. The problem with using this information is that it is often out of date. Unless you have a subscription to a current list of valid numbers you may end up refusing cards which are indeed valid - CC companies are constantly being allocated new card ranges.Just a few words of caution.Good luck,macka. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-07-09 : 08:06:36
|
| best way would be to get a web service of sort setup that validates credit cards via a reliable source and then code a wrapper for the web service in an extendend stored procedure and then either with the insert/update trigger check it with the xproc or add a function that calls the xproc as the constraint create function ValidateCreditCardNumber(@number varchar(32)) returns bit as begin declare @valid bit exec @valid = xp_validate_credit_card_number @number return isnull(@valid, 0) end just an idea nothing more; its up to you to implement a method that is best for you. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-07-20 : 15:14:25
|
| It depends if you actually want to authorize the card, or just make sure that it is a valid card number. The web service approach makes sense for the former, but I don't think the logic belongs in a constrain.For the latter, it should be pretty easy to adapt the algorithms discussed here: http://www.beachnet.com/~hstiles/cardtype.htmlCheers-b |
|
|
|
|
|
|
|
|
Creditcard validation contraint