How to make Column Total not more than 100%? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

How to make Column Total not more than 100%?

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-15 : 11:10:24

I want to create a table name: assign_marks and one of the column in the table will be marks.

Now I want that whatever value user insert in marks column, the column total should not exceed 100.

For e.g.

MARKS
40
30
20
10
value insert will be success as total is 100.

MARKS
40
30
30
10
value insert will not be success as total is 110 not 100.

I want to store this code in the database, I don't want to run the code. I just want user should insert the value in marks column.

Hope I can make you understand, what exactly is my problem??

Daipayan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-15 : 13:25:38

[code]CREATE TABLE Marks
(
UserID INT NOT NULL,
Marks TINYINT NOT NULL
)
GO
CREATE TRIGGER dbo.trgMarks_UpdateInsert
ON db.Marks
AFTER UPDATE,
INSERT
AS

IF EXISTS (
SELECT TOP 1 m.UserID
FROM dbo.Marks AS m
INNER JOIN (
SELECT UserID
FROM inserted
GROUP BY UserID
) AS i ON i.UserID = m.UserID
GROUP BY m.UserID
HAVING SUM(CAST(m.Marks AS SMALLINT)) > 100
)
BEGIN
RAISERROR('Sum is greater than 100.', 16, 1)
ROLLBACK TRAN
END
GO
INSERT dbo.Marks
(
UserID,
Marks
)
SELECT 1, 40 UNION ALL
SELECT 1, 30 UNION ALL
SELECT 2, 80 UNION ALL
SELECT 1, 30 UNION ALL
SELECT 1, 10
GO
INSERT dbo.Marks
(
UserID,
Marks
)
SELECT 2, 30
GO[/code]

E 12°55'05.63"
N 56°04'39.26"

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-15 : 15:44:15

@Peso
Sir, I exceuted the code, first I created the table and when am trying to create the trigger, am getting the following error:

Server: Msg 156, Level 15, State 1, Procedure trgMarks_UpdateInsert, Line 14
Incorrect syntax near the keyword 'WHERE'.

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-15 : 23:49:53

make a udf like this

create function TotalMarks
(
)
returns int 
as
begin
declare @total int
select @total=sum(Marks)
from yourtable
return @total
end



then link it to table via check constraint as


alter table yourtable add constraint chk_totalmarks check (dbo.TotalMarks()< 100)


now try inserting marks

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-16 : 04:52:09

@visakh16

I had created following table:
TABLE: marks_assign

course_code(varchar,50)
marks_type(varchar,50)
marks_assign(bigint,8)

I had little bit modified your function, it as follows:

CREATE function TotalMarks
(
)
returns int 
as
begin
declare @total int
select @total=sum(marks_assign)
from marks_assign
group by course_code
return @total
end

Hope this is ok, as I want the total 100 should be calculated according to a particular course.

Daipayan

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-16 : 05:44:39

@Peso
Sir, I used your edited code, but this time, it's giving following error:

Server: Msg 208, Level 16, State 4, Procedure trgMarks_UpdateInsert, Line 1
Invalid object name 'db.Marks'.

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 06:16:38

quote:
Originally posted by daipayan

@visakh16

I had created following table:
TABLE: marks_assign
course_code(varchar,50)
marks_type(varchar,50)
marks_assign(bigint,8)

I had little bit modified your function, it as follows:
CREATE function TotalMarks
(
)
returns int
as
begin
declare @total int
select @total=sum(marks_assign)
from marks_assign
group by course_code
return @total
end

Hope this is ok, as I want the total 100 should be calculated according to a particular course.

Daipayan

yup...thats fine. if you want total based on course, you need to group by it

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-16 : 08:37:28

@visakh16

This code showing server error, can't I generate self-created error message??

and Sir, I had created a school database in the following link, please go through it once and give any suggestions, this is the following link:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119218[/url]

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 09:09:54

quote:
Originally posted by daipayan

@visakh16

This code showing server error, can't I generate self-created error message??

and Sir, I had created a school database in the following link, please go through it once and give any suggestions, this is the following link:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119218[/url]

Daipayan

you need to use RAISERROR for genrating self created error.see below

http://www.sommarskog.se/error-handling-I.html

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-16 : 09:39:48

[code]CREATE TABLE dbo.Marks
(
UserID INT NOT NULL,
Marks TINYINT NOT NULL
)
GO
CREATE TRIGGER dbo.trgMarks_UpdateInsert
ON dbo.Marks
AFTER UPDATE,
INSERT
AS

IF EXISTS (
SELECT TOP 1 m.UserID
FROM dbo.Marks AS m
INNER JOIN (
SELECT UserID
FROM inserted
GROUP BY UserID
) AS i ON i.UserID = m.UserID
GROUP BY m.UserID
HAVING SUM(CAST(m.Marks AS SMALLINT)) > 100
)
BEGIN
RAISERROR('Sum is greater than 100.', 16, 1)
ROLLBACK TRAN
END
GO
INSERT dbo.Marks
(
UserID,
Marks
)
SELECT 1, 40 UNION ALL
SELECT 1, 30 UNION ALL
SELECT 2, 80 UNION ALL
SELECT 1, 30 UNION ALL
SELECT 1, 10
GO
INSERT dbo.Marks
(
UserID,
Marks
)
SELECT 2, 30
GO
DROP TABLE dbo.Marks
GO[/code]

E 12°55'05.63"
N 56°04'39.26"

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-16 : 13:32:43

THANKS A LOT AGAIN

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources