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
        )
);

• 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   <NULL>
   2      Andy        ASM001    End-Term   <NULL>
   3      Andy        ASM001    Project    <NULL>
   4      Bob         ASM001    Mid-Term   <NULL>
   5      Bob         ASM001    End-Term   <NULL>
   6      Bob         ASM001    Project    <NULL>
  -------------------------------------------------

quote:

---

Originally posted by blindman

So why not use a trigger?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

---

quote:

---

Originally posted by blindman

That makes no sense.
You can write triggers for inserts only, updates only, deletes only, or any combination of the tree.
How many tables need to be updated to reflect the change is dependent upon the business rules, not the method of implementation.
I can't see any reason why you could not use triggers for this.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

---

quote:

---

Originally posted by blindman

What are the business rules?
Which tables should be updated when the student specialization changes?
Should they change on inserts, updates, deletes?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

---

-------------------------------------------------
AM_ID Student_Name CourseID SS_MarksType SS_Mark
-------------------------------------------------
 1      Andy        ASM001    Mid-Term   <NULL>
 2      Andy        ASM001    End-Term   <NULL>
 3      Andy        ASM001    Project    <NULL>
 4      Bob         ASM001    Mid-Term   <NULL>
 5      Bob         ASM001    End-Term   <NULL>
 6      Bob         ASM001    Project    <NULL>
-------------------------------------------------

quote:

---

Originally posted by blindman

Is Student_Name unique in the StudentSpecialization table?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

---

quote:

---

Originally posted by blindman

So, write your trigger to update related tables based on the Student_Name.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

---

--This is my StudentMark table, here I had inserted new column called ind_active!
create table dbo.StudentMark(
	StudentMark_ID	bigint identity not null,
	Student_ID		bigint not null,
	Course_ID		bigint not null,
	AssgnMrk_ID		bigint not null,
	Mark			bigint null,
	ind_active		CHAR(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