3 tables trigger me with 11 tables involved !!

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)

3 tables trigger me with 11 tables involved !!

Author

Topic

saja
Starting Member

3 Posts

Posted - 2006-04-25 : 14:17:06

Pleas help me on this.. its driving me nuts!

the image shows the relations between the tables

the table Grades contains all the students & their registered courses with default vales "0" for the rest of the columns.

1. whenever an Assignment_submition's grade is updated it updates the Grades table -> adds the updated grade to the Grades.assignment
all the assignments for a specific course# & course_code

2.whenever an Project_submition's grade is updated it updates the Grades table -> adds the updated grade to the Grades.project
all the projects for a specific course# & course_code

3.whenever an Question_answer is inserted its grade updates the Grades table -> adds the updated grade to the Grades.quiz
all the questions of a quiz for a specific course# & course_code

plsss help, mu graduation project is Due this month!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-25 : 14:52:19

First, what is your equestion? Second, please show us what you have so far to at least show you've tried your homework on your own.

Tara Kizer
aka tduggan

saja
Starting Member

3 Posts

Posted - 2006-04-25 : 15:00:44

thanks for the reply.

my question is.. how can i write these triggers ??!

i tried these but all give a wrong result or i can't insert into the tables.

---------------------------------------------------------------------------------

CREATE TRIGGER [update_project_grade] ON [dbo].[Project_Submition]
FOR update
AS
Update Grades Set Project=
(SELECT SUM(Project_Submition.Grade) AS totalgrades
FROM Project_Submition INNER JOIN
Project ON Project_Submition.Project_ID = Project.Project_ID
GROUP BY Project.Course#, Project.Course_code, Project_Submition.Student_ID)

---------------------------------------------------------------------------------

CREATE TRIGGER [update_project_grade1] ON [dbo].[Project_Submition]
FOR UPDATE
AS
Update Grades Set Project=
(select SUM(Project_Submition.Grade) AS totalgrades
FROM Project_Submition INNER JOIN
Project ON Project_Submition.Project_ID = Project.Project_ID INNER JOIN
Course ON Project.Course# = Course.Course# AND Project.Course_code = Course.Course_code
WHERE (Project_Submition.Student_ID = N'group by'))

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 21:13:52

don't complicate it...

handle both updates, individual table against the grades table, in your apps

you can write an sproc to contain both updates or two sprocs for each update calling one after the other in your apps

--------------------
keeping it simple...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 11:48:27

The teacher complicated it. The assignment is to use triggers to solve this. If I find some time today, I'll have a look at it again.

Tara Kizer
aka tduggan

X002548
Not Just a Number

15586 Posts

Posted - 2006-04-26 : 11:54:49

quote:
Originally posted by saja

plsss help, mu graduation project is Due this month!

Guess you should have started sooner....

Sounds like a bunch of long days ahead...my current days are like 16 hours each...and it's not enough time

Read the hint link in my sig....

BUT! Most people don't like to assist with homework

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

saja
Starting Member

3 Posts

Posted - 2006-04-27 : 07:36:43

still working on it.. thanks guys

wish me luck!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-27 : 09:20:41

Did you come up with this design, or was it defined in the assignment? Your schema looks a little loopy to me, and frankly if you are using triggers to update derived data this frequently then I think there are problems with your application design. I'd say you are right on the hairy edge of what triggers should and should not be used for, and you may be adding code just to compensate for deficiencies in your design.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources