Trigger in Table - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger in Table

Author

Topic

nagasundartn
Starting Member

3 Posts

Posted - 2011-11-09 : 02:26:31

I have a table which contains minmum marks and maximum marks columns. The new row should be inserted with the following conditions:

1. Minimum marks should be less then maximum marks
2. The existing range of minimum and maximum marks should not fall on the newly inserted row range.

Can anyone please explain the query with Triggers.

T.N.Nagasundar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 02:55:20

sounds like a check constraint is what you need. something like

ALTER TABLE <tablename> ADD CONSTRAINT Chk_Marks CHECK ((MinMark <MaxMark) AND dbo.MarkRangeCount(MinMark,MaxMark) = 1 )

MarkRangeCount function will be as follows


CREATE FUNCTION MarkRangeCount
(@MinMark int,@MaxMark int)
RETURNS int
AS
BEGIN 
DECLARE @Cnt int

SELECT @Cnt = COUNT(*)
FROM
(
SELECT *
FROM Tbl
WHERE (MinMark BETWEEN @MinMark AND @MaxMark
OR MaxMark BETWEEN @MinMark AND @MaxMark)
)t
RETURN @Cnt
END

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources