February 25, 2009 at 1:29 pm
Hi All, I need your help...please help me!
I have 4 tables:
create table dbo.Course(
CourseID smallint identity not null,
C_Name varchar(10) not null,
C_Batch varchar(10) not null,
C_Term varchar(10) not null,
C_Specialisation varchar(10) not null,
constraint PK_CourseID primary key(CourseID)
);
create table dbo.StudentSpecialisation(
SS_ID smallint identity not null,
Student_Name varchar(100) not null,
SS_Batch varchar(10) not null,
SS_Term varchar(10) not null,
SS_Specialisation1 varchar(10) not null,
SS_Specialisation2 varchar(10) not null,
constraint PK_SS_ID primary key(SS_ID)
);
create table dbo.AssignMarks(
AM_ID smallint identity not null,
CourseID smallint not null,
SS_MarksType varchar(10) null,
SS_Marks bigint null,
constraint PK_AM_ID primary key(AM_ID)
);
create table dbo.StudentMarks(
SM_ID smallint identity not null,
Student_Name varchar(100) not null,
CourseID smallint not null,
SS_MarksType varchar(10) null,
SS_Mark bigint null,
constraint PK_SS_ID primary key(SM_ID)
);
Now, let me brief you about these tables:
Course: Here, I will include details of the courses.
StudentSpecialisation: Here, I will insert details of a student specialisation, a student have to opt 2 specialisation, thats why i had given 2 columns specialisation_1 & specialisation_2.
AssignMarks: here faculty can decide the divison of the marks of his/her particular course, e.g. for a course ASM001, divison can:
--------------------------------------
AM_ID CourseID SS_MarksType SS_Marks
--------------------------------------
1 ASM001 Mid-Term 30
2 ASM001 End-Term 40
3 ASM001 Project 30
--------------------------------------
StudentMarks: This is the part, where I want something. I want whenever data is being input in StudentSpecialisation table, then automatically Student_Name and CourseID will be updated in this table by checking specialisation_1/specialisation_2 of dbo.StudentSpecialisation is equal to C_Specialisation of dbo.Course.
Another thing, the SS_MarksType in dbo.StudentMarks will also be inserted by checking CourseID of dbo.StudentMarks is equal to dbo.AssignMarks of CourseID. This SS_MarksType can be come multiple times.
Let me show, how it should look:
-------------------------------------------------
AM_ID Student_Name CourseID SS_MarksType SS_Mark
-------------------------------------------------
1 Andy ASM001 Mid-Term
2 Andy ASM001 End-Term
3 Andy ASM001 Project
4 Bob ASM001 Mid-Term
5 Bob ASM001 End-Term
6 Bob ASM001 Project
-------------------------------------------------
How can this be possible???
February 25, 2009 at 1:53 pm
You could set up a trigger on the tables you want to monitor, and whenever they are updated, have them insert into this table, after checking to make sure they need to. That's pretty standard.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 1:57 pm
Thank You for your reply.
Can't I use function or stored procedure??
What can be the code snippet for trigger?
February 25, 2009 at 3:00 pm
Look up triggers in Books Online or MSDN. Either one will give you the data on how to write them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 3:10 pm
Ok Sir, I'll reply you back soon with the code snippet.
February 27, 2009 at 10:03 am
--This is my StudentMark table, here I had inserted new column called ind_active!
create table dbo.StudentMark(
StudentMark_IDbigint identity not null,
Student_IDbigint not null,
Course_IDbigint not null,
AssgnMrk_IDbigint not null,
Markbigint null,
ind_activeCHAR(1)NOT NULL
Constraint c_ind_active CHECK (ind_active IN ('Y', 'N')),
constraint PK_SM_ID primary key(StudentMark_ID)
);
--This is my auto Insert Statement
INSERT INTO dbo.StudentMark (Student_Name, CourseID, SS_MarksType, ind_active)
SELECT StudentSpecialisation.Student_Name,
Course.CourseID,
AssignMark.AM_ID,
'Y'
FROM Course
INNER JOIN AssignMark ON
Course.CourseID = AssignMark.CourseID
INNER JOIN StudentSpecialisation ON
Course.C_Specialisation = StudentSpecialisation.SS_Specialisation1 OR
Course.C_Specialisation = StudentSpecialisation.SS_Specialisation2
Now, How should I insert this code in Trigger, am confused on this part??
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply