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


    Kindest Regards,

    *4*

  • 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]

  • what is BOL for CREATE TRIGGER?


    Kindest Regards,

    *4*

  • 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]

  • Im a newbie in sql. i started learning SQL at the begining of february...

    the assignment is a class assignment... Do you know any free online sql server? Any site with BOL will help me...

    have a great week...


    Kindest Regards,

    *4*

  • 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.

  • i can use an online documentation... I just need a script that is almost connected to my assignment...

    have a great week.... the clocking is ticking towards Easter and my Stomach is clicking for the food...


    Kindest Regards,

    *4*

  • Here's the link to the BOL download page:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en

    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


    Kindest Regards,

    *4*

  • 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]

  • thanks alot.... i will use both and see....

    have a great week

    nforchange


    Kindest Regards,

    *4*

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply