CHECK CONSTRAINT WITH USER DEFINED FUNCTION

-- HERE IS THE SIMPLE DEMONSTRATION CHECK CONSTRAINT WITH USER DEFINED FUNCTION.

-- SENARIO : ONLY ONE STUDENT CAN JOIN THE AT A TIME . ALL STUDENTS CAN WAIT UNTILL THE JOINED STUDENT CAN INACTIVE.

-- CREATE FUNCTION

CREATE FUNCTION CHECK_StudentJoinSQLClass(@JOINED BIT)

RETURNS BIT

AS

BEGIN

IF(@JOINED=1 AND (SELECT COUNT(*) FROM StudentJoinSQLClass WHERE  JOINED=@JOINED)>1)

BEGIN

                 SET @JOINED=0 --FALSE

END

ELSE

BEGIN

                SET @JOINED=1 --TRUE

END

RETURN @JOINED

END

-- CREATE TABLE

 CREATE TABLE StudentJoinSQLClass

(

 ID INT IDENTITY(1,1),

 NAME VARCHAR(10),

 JOINED BIT  NOT NULL CHECK(DBO.CHECK_StudentJoinSQLClass(JOINED)=1)

)

--INSERT DATA

INSERT INTO StudentJoinSQLClass(NAME,JOINED) VALUES('RAKESH',0)

INSERT INTO StudentJoinSQLClass(NAME,JOINED) VALUES('RAVI',1)

--HERE WE ARE INSERTING RAJU RECORD WITH TRUE VALUE .IN THIS CASE WE GET ERROR ,BEACUSE OF WE HAVE ALREADY RAVI RECORD WITH TRUE VALUE.

INSERT INTO StudentJoinSQLClass(NAME,JOINED) VALUES('RAJU',1)

-- UPDATE RAKESH RECORD FALSE TO TRUE.HERE ALSO WE GET THE ERROR.

UPDATE StudentJoinSQLClass SET JOINED=0 WHERE ID=1

--HERE WE UPDATING THE RAVI RECORD TO FALSE.

UPDATE StudentJoinSQLClass SET JOINED=0 WHERE ID=2

--TRY TO INSERT RAVI RECORD ONCE AGAIN.HERE THE VALUE IS INSERTED SUCESSFULLY.BEACAUSE NO TRUE RECORDS EXISTED IN THE TABLE RIGTH NOW.

INSERT INTO StudentJoinSQLClass(NAME,JOINED) VALUES('RAJU',1)

SELECT * FROM StudentJoinSQLClass

DROP TABLE StudentJoinSQLClass