Triggers - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Triggers

Author

Topic

aidmondo
Starting Member

23 Posts

Posted - 2012-05-25 : 06:52:49

Create table PaymentMethods(
PaymentMethodID int Primary Key,
Description not null
)
The Descriotion is either cheque, cash or Credit Card.

Create table Payments(
PaymentID int Primary Key,
PaymentMenthodID int Constraint cfkPay Foreign Key References (#above) not null,
ChequeNo varchar(20) not null
)

Now my question is if the description is cheque, the ChequeNo should be entered and if not it should be left blank. How?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 09:40:41

you can use a check constraint based on UDF like below


CREATE FUNCTION dbo.GetPaymentStatus
(
@PaymentMethodID int,
@ChequeNo varchar(20)
)
RETURNS bit
AS
BEGIN 
DECLARE @Ret bit

SELECT @Ret=CASE WHEN Description ='cheque' AND @ChequeNo IS NULL THEN 0
ELSE 1 END
FROM PaymentMethods
WHERE PaymentMethodID = @PaymentMethodID 

RETURN(@Ret)
END


then create check constraint based on it
ALTER TABLE Payments ADD CONSTRAINT Chk_PaymentMethod CHECK (dbo.GetPaymentStatus(PaymentMenthodID,ChequeNo)=1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-25 : 09:47:21

Sparse columns aren't really a good fit for relational databases.

you could make the ChequeNo NULL'able and insert a null... If you end up with a bunch of these guys you are going to be wasting a lot of space for no benefit.

or, perhaps cleaner you could have a PatmentDetails table with a foreign key back to pyments and then insert rows into there for the details.

Transact Charlie
Msg 3903, Level 16, State 1, Line 1736:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
http://nosqlsolution.blogspot.co.uk/

aidmondo
Starting Member

23 Posts

Posted - 2012-06-02 : 07:48:00

When I execute the function, i get the error message 'cannot insert null into the column ChequeNo and if i allow ChequeNo accepts null values, the ChequeNo column accept all values even if description in not Cheque.

aidmondo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 13:44:18

quote:
Originally posted by aidmondo

When I execute the function, i get the error message 'cannot insert null into the column ChequeNo and if i allow ChequeNo accepts null values, the ChequeNo column accept all values even if description in not Cheque.

aidmondo

do you mean while creating check constraint?

I'm not doing any insert to check field inside function so didnt understand what you mean by getting an error while creating function.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aidmondo
Starting Member

23 Posts

Posted - 2012-06-07 : 14:03:13

After the function have been executed and table altered, the chequeno column in the Payments table still accepts values even if the PaymentMethod Description is cash or creditcard.

aidmondo

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-07 : 14:35:18

I'm not sure if this is any better or more correc that Visakh's solution (as I didn't try it). but, maybe it'l help?


-- Create Tables

CREATE TABLE PaymentMethod
(
	PaymentMethodID INT IDENTITY(1, 1) PRIMARY KEY,
	Description VARCHAR(20) NOT NULL
)


CREATE TABLE Payment
(
	PaymentID INT IDENTITY(1, 1) PRIMARY KEY,
	PaymentMenthodID INT NOT NULL CONSTRAINT cfkPay FOREIGN KEY (PaymentMenthodID) REFERENCES PaymentMethod (PaymentMethodID),
	ChequeNo varchar(20) not null 
)

ALTER TABLE Payment
ADD CONSTRAINT CK_Payment_ChequeNo CHECK ((CASE WHEN PaymentMenthodID = 1 AND LEN(ChequeNo) > 0 THEN 1 WHEN PaymentMenthodID <> 1 AND LEN(ChequeNo) = 0 THEN 1 ELSE 0 END) = 1)

-- Popualte Domain Data

SET IDENTITY_INSERT PaymentMethod ON
INSERT PaymentMethod (PaymentMethodID, Description)
VALUES
(1, 'Cheque'),
(2, 'Cash'),
(3, 'Credit Card')
SET IDENTITY_INSERT PaymentMethod ON


-- Try Inserting

INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (1, NULL) -- Fail
INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (1, '') -- Fail
INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (1, '12345') -- Success
INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (2, '12345') -- Fail
INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (2, '') -- Success

-- Drop tables
DROP TABLE Payment
DROP TABLE PaymentMethod

EDIT: Added DROP

aidmondo
Starting Member

23 Posts

Posted - 2012-06-07 : 18:15:07

Thanks a lot for the help

aidmondo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources