March 22, 2005 at 5:01 am
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
March 22, 2005 at 5:58 am
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]
March 22, 2005 at 6:04 am
March 22, 2005 at 6:06 am
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]
March 22, 2005 at 6:12 am
March 22, 2005 at 8:15 am
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.
March 22, 2005 at 9:29 am
March 22, 2005 at 11:57 pm
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]
March 23, 2005 at 3:05 am
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
March 23, 2005 at 3:31 am
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]
March 23, 2005 at 3:38 am
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]
March 23, 2005 at 3:46 am
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy