project

Question

project

kanika.bhagat

SSC Rookie

Points: 40
More actions
October 10, 2010 at 8:15 am

#229468

hi,
I have one table student and three fields mtt1,mtt2 and sum.I want to update sum field whenever there is a new value inserted in mtt1 and mtt2.I want to use trigger and nothing else for this

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Why is this posted under reporting services? No reports here that I can see.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 2

kanika.bhagat (10/10/2010)
hi,
I have one table student and three fields mtt1,mtt2 and sum.I want to update sum field whenever there is a new value inserted in mtt1 and mtt2.I want to use trigger and nothing else for this

A trigger is probably the worst thing to use here and, to be honest, the overall design is a problem here. That notwithstanding, look into persisted calculated columns.

If you insist on using a trigger for this, then read and heed the link Gail pointed you to and maybe show what you've tried and help in on the way. If you don't want to do that, then see Books Online for how to write a trigger with the advice to make sure it's a set-based trigger so you can handle more than just a single row insert.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kanika.bhagat SSC Rookie Points: 40 More actions · Answer 3

defination for the table student is given below:

create table "student"

(

"rollno" varchar2(4000),

"class" varchar2(4000),

"sem" varchar2(4000),

"sub" varchar2(4000),

"mtt1" number,

"mtt2" number,

"external" number,

"tot" number,

)

output will be like

if we insert values in all the fields such as

rollno - 08ejccs034

class -1csa

sem-1st

sub-maths

mtt1-7

mtt2-8

external-50

then total will be generated automatically like

tot-65

and I want to do this

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 4

kanika.bhagat (10/11/2010)
defination for the table student is given below:
create table "student"
(
"rollno" varchar2(4000),
"class" varchar2(4000),
"sem" varchar2(4000),
"sub" varchar2(4000),
"mtt1" number,
"mtt2" number,
"external" number,
"tot" number,
)
output will be like
if we insert values in all the fields such as
rollno - 08ejccs034
class -1csa
sem-1st
sub-maths
mtt1-7
mtt2-8
external-50
then total will be generated automatically like
tot-65
and I want to do this

Ah... sorry... I can't do it. VARCHAR2() is Oracle and PLSQL<>TSQL. I was going to show you how to use a calculated column to do this instead of a trigger but I can't even show you how to do it as a trigger because I've forgotten Oracle after all these years.

My recommendation would be to find an Oracle forum to ask your question on. I do remember that (if you really need a trigger) that you'll need to write a single row trigger using the INSERTED and DELETED variables for each affected column and THEN use the hint FOR EACH ROW in the trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

kanika.bhagat (10/10/2010)
I have one table student and three fields mtt1,mtt2 and sum.I want to update sum field whenever there is a new value inserted in mtt1 and mtt2.I want to use trigger and nothing else for this

This is homework, isn't it? 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.