urgent sql assignment
-
hi all,
i need some help... i have an assignment that is over due by 2 hrs... I need an who knows SQL... I need help to create a trigger... I can create a table but i need a trigger to calcuate 4 marks and add them up automatically... and creates a column for the total automatically..
For example (mark1+mark2+marks3+marks4) = total...
please respond within 26 hrs after seeing these post... i have to hand in results early thursday morning...
have a great day...
me
-
Wow, you're not putting any peer pressure on us, do you?
Why do you intend to keep a redundant "total" column anyway? When you have the single pieces, you can easily calculate the sum in a query.
For the rest you might want to have a look at BOL for CREATE TRIGGER. There's a section that deals with IF UPDATE(column). Should do what yo want.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
BOL is the SQL Server online help and CREATE TRIGGER is the command you should search for to find the solution I mentioned.
You're working with SQL Server and don't have BOL installed?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Personally, I'd be asking for a refund of tuition fees. Any Sql Server class that doesn't start by showing your where to find & how to use the online documentation is a rip off.
-
Here's the link to the BOL download page:
It's a free download.
Next, whoever assigned you such a stupid task to keep a redundant column like "total" should sit himself for a beginner data modelling course.^
Anyway, your trigger might look something like this:
set nocount on
use tempdb
go
create table why
(
mark1 int
, mark2 int
, mark3 int
, mark4 int
, total int
)
go
create trigger calctotal on dbo.why
for insert, update
as
update why set total = mark1+mark2+mark3+mark4
go
insert into why (mark1, mark2, mark3, mark4) values(1,2,3,4)
set nocount off
select * from why
update why set mark1 = 1000
select * from why
drop table why
mark1 mark2 mark3 mark4 total
----------- ----------- ----------- ----------- -----------
1 2 3 4 10
(1 row(s) affected)
(1 row(s) affected)
mark1 mark2 mark3 mark4 total
----------- ----------- ----------- ----------- -----------
1000 2 3 4 1009
(1 row(s) affected)
Happy Easter
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
thanks. i will use it
these is how my table should look like
sno |Names |mark1 |mark2 |mark3 |mark4 |Total
Correct me if im wrong any where below:-
---------------------------------------
set nocount on
use tempdb
go
create table class( sno numeric primary key, Names varchar(15),
mark1 int
, mark2 int
, mark3 int
, mark4 int
, total int
)
go
create trigger clagger on dbo.class
for insert, update
as
update why set total = mark1+mark2+mark3+mark4
go
insert into class (101,'nfor',mark1, mark2, mark3, mark4) values(1,2,3,4)
set nocount off
select * from class
update class set mark1 = 1000
select * from class
drop table class
--------------------------------------
Please correct me...
have a great easter weekend...
nforchange
-
Some slight correction, and I got this to work
set nocount on
use tempdb
go
create table class( sno numeric primary key, Names varchar(15),
mark1 int
, mark2 int
, mark3 int
, mark4 int
, total int
)
go
create trigger clagger on dbo.class
for insert, update
as
update class set total = mark1+mark2+mark3+mark4
go
insert into class (sno,names,mark1, mark2, mark3, mark4) values(101, 'nfor',1,2,3,4)
set nocount off
select * from class
update class set mark1 = 1000
select * from class
drop table class
sno Names mark1 mark2 mark3 mark4 total
-------------------- --------------- ----------- ----------- ----------- ----------- -----------
101 nfor 1 2 3 4 10
(1 row(s) affected)
(1 row(s) affected)
sno Names mark1 mark2 mark3 mark4 total
-------------------- --------------- ----------- ----------- ----------- ----------- -----------
101 nfor 1000 2 3 4 1009
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Forget to post, I would prefer this:
set nocount on
use tempdb
go
create table class( sno numeric primary key, Names varchar(15),
mark1 int
, mark2 int
, mark3 int
, mark4 int
)
go
insert into class (sno,names,mark1, mark2, mark3, mark4) values(101, 'nfor',1,2,3,4)
set nocount off
select
sno
, names
, mark1
, mark2
, mark3
, mark4
, (mark1+mark2+mark3+mark4) total
from
class
update class set mark1 = 1000
select
sno
, names
, mark1
, mark2
, mark3
, mark4
, (mark1+mark2+mark3+mark4) total
from
class
drop table class
sno names mark1 mark2 mark3 mark4 total
-------------------- --------------- ----------- ----------- ----------- ----------- -----------
101 nfor 1 2 3 4 10
(1 row(s) affected)
(1 row(s) affected)
sno names mark1 mark2 mark3 mark4 total
-------------------- --------------- ----------- ----------- ----------- ----------- -----------
101 nfor 1000 2 3 4 1009
(1 row(s) affected)
You see, there is no need for a "total" column.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply