Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Location
    India
    Posts
    14

    Red face Unanswered: how to Update One table from Multiple Tables?

    Hi All, I need your help...please help me!

    I have 4 tables:
    Code:
    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:
      Code:
      --------------------------------------
      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:
      Code:
      -------------------------------------------------
      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>
      -------------------------------------------------

    How can this be possible with trigger???

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    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)
    );
    Before I answer your question, some observations about the model.

    I noticed some inconsistencies in your column naming. I would not use names like C_xxxx to name columns of the Course table, SS_xxx for StudentSpecialisation columns, ... The columns are already present in that Course table, don't mention that fact twice by repeating it in the name of all its columns. And after a (short) while you are spending more time of coming up with clever abbreviations for the fields of the many tables that start with a C (Class, College, ...).
    And if you insist on doing so anyway, apply your rule consistently: CourseID should be C_ID, to be consistent with SS_ID, AM_ID, SM_ID, ...
    And what is the idea to let the name of those SS_Marksxx columns in AssignMarks start with SS_ ?

    The column Student_Name is also not in the right place. Use a foreign key to the STUDENT table (presuming there is such a table), using your naming convention that would be S_ID. The field Student_Name in StudentMarks should also be replaced by S_ID.

    SS_Specialisation1 and SS_Specialisation2 should not be included in the StudentSpecialisation table, but in a separate table with a foreign key towards StudentSpecialisation.

    Now about the trigger to populate StudentMarks.
    Don't use a trigger for that.
    People make mistakes or change their idea. Your trigger will have to fire not only for INSERTS but also for DELETES and UPDATES. Suppose a student changes his/her idea about those two specialisations, the StudentMarks table will have to be altered to reflect that (delete or update previous records). And you will have to write triggers for all the tables that are related to StudentMarks: Course, StudentSpecialisation and AssignMarks. A change in any of those three tables - like a correction of a typing error - might have an impact on StudentMarks.

    I would provide a means to populate the StudentMarks at certain moments with an INSERT INTO .. SELECT statement. And make it robust so it can run often. Make sure no data gets lost that should be kept, and on the other hand that combinations that are no longer valid are deleted or at least marked as "inactive".
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2009
    Location
    India
    Posts
    14

    Reply to Wim

    Code:
    create table dbo.Course(
            Crse_ID  smallint identity not null,
            Crse_Name     varchar(10) not null,
            Crse_Batch      varchar(10) not null,
            Crse_Term      varchar(10) not null,
            Crse_Specialisation     varchar(10) not null,
            constraint PK_Crse_ID primary key(Crse_ID)
    );
    
    create table dbo.StudentSpecialisation(
            StndtSpec_ID  smallint identity not null,
           Student_ID     varchar(100) not null,
            StndtSpec_Batch      varchar(10) not null,
            StndtSpec_Term      varchar(10) not null,
            StndtSpec_Specialisation1     varchar(10) not null,
           StndtSpec_Specialisation2     varchar(10) not null,
            constraint PK_SS_ID primary key(SS_ID)
    );
    
    create table dbo.AssignMarks(
            AssgnMrk_ID  smallint identity not null,
            Crse_ID  smallint not null,
            AssgnMrk_MarksType     varchar(10) null,
            AssgnMrk_Marks     bigint null,
    constraint PK_AssgnMrk_ID primary key(AssgnMrk_ID)
    );
    As per your advice, I had changed the column names to avoid inconsistencies.
    And SS_Marksxx is my typing mistake,sorry!

    There is a student_details table and here S_ID is the foreign key, I did not mentioned because I thought maybe you don't need that details.

    And regarding StudentSpecialisation, the advice is superb, thanks again, I will do the same changes as you said...

    Now, please help me with the update of one table from the multi-tables.
    I understand the problems of using trigger, will you please provide me the other way!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The way you changed the column names was not what I intended.
    Code:
    create table dbo.Course(
    	Course_ID		bigint identity not null,
    	Name			varchar(10) not null,
    	Batch			varchar(10) not null,
    	Term			varchar(10) not null,
    	Specialisation	varchar(10) not null,
    	constraint PK_Course primary key(Course_ID)
    );
    
    create table dbo.StudentSpecialisation(
    	StudentSpecialisation_ID	bigint identity not null,
    	Student_ID		bigint not null,
    	Batch			varchar(10) not null,
    	Term			varchar(10) not null,
    	Specialisation1	varchar(10) not null,
    	Specialisation2	varchar(10) not null,
    	constraint PK_StudentSpecialisation primary key(StudentSpecialisation_ID)
    );
    
    create table dbo.AssignMark(
    	AssignMark_ID		bigint identity not null,
    	Course_ID			bigint not null,
    	MarksType		varchar(10) null,
    	Marks			bigint null,
    constraint PK_AssignMarks primary key(AssignMark_ID)
    );
    
    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)
    );
    
    INSERT INTO dbo.StudentMark (Student_ID, Course_ID, AssgnMrk_ID, ind_active)
    SELECT StudentSpecialisation.Student_ID, 
    		Course.Course_ID, 
    		AssignMark.AssignMark_ID,
    		'Y'
    FROM  Course 
    	INNER JOIN AssignMark ON 
    		Course.Course_ID = AssignMark.Course_ID 
    	INNER JOIN StudentSpecialisation ON 
    		Course.Specialisation = StudentSpecialisation.Specialisation1
    
    DROP table dbo.Course;
    DROP table dbo.StudentSpecialisation;
    DROP table dbo.AssignMark;
    DROP table dbo.StudentMark;
    The INSERT statement will insert records in an empty StudentMark table. You will have to write other SQL's to check for new, obsolete, identical and altered records comapred to the ones present in StudentMark; and act according to that with INSERT, DELETE (or at least set ind_active to 'N'), nothing and UPDATE.

    While writing the SELECT statement from the INSERT, I noticed how strange it is to link StudentSpecialisation with Course through Specialisation. Is Specialisation unique over all Courses? If not, the join will give multiple courses who all have a certain Specialisation in common.

    Don't you have to take academic year in consideration?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2009
    Location
    India
    Posts
    14
    Thanks a lot for the code.
    Thank you again!

    Just one problem, everytime I have to run the code, can't it be automated means one I insert data in dbo.Course, dbo.StudentSpecialisation, and dbo.AssignMark, the data will automatically will be updated in dbo.StudentMark!

    And, Specialisation in Course is unique over all and Batch represents the academic year.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Yes, you can do that by writing triggers. Feel free to write them, I mentioned before how they should react.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Feb 2009
    Location
    India
    Posts
    14
    Sir,

    I don't know how to write trigger for this following insert/update query.
    Actually, I am only 3 weeks old to MS SQL 2000 and still am not so much familiar with this trigger, please guide me one last time, please!

    I have to automate this query, I understand the reaction, but I need to automate the part.

    Please help me one more time!

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I cannot help to observe that you're taking the wrong turn here by insisting on using triggers to tackle this problem. But what tool you are going to use is second order, first you need to get a good grab at the problem.

    Take a piece of paper and write down a simple starting point with certain values in the three base tables (Course, StudentSpecialisation and AssignMarks).

    Determine what the records in StudentMarks should be. Write an INSERT statement to do just that.

    Now alter the content of the three base tables: add, alter, delete records in those three tables.

    Make a new table StudentMarksAfter, with the very same structure as StudentMarks.
    Determine what the records in StudentMarksAfter should be. Write an INSERT statement to do just that (same as the one before). You should get a number of altered, unaltered records, new and "no longer there" records, compared with those in StudentMarks.

    Make a new table StudentMarksAssert, with the very same structure as StudentMarks.
    Create a number of INSERT, UPDATE and DELETE statements to go from a SELECT on StudentMarks and the current state of the three base tables to get the same records as you find in StudentMarksAfter.

    Determine what actions you have to take when a student has a mark for a course (present in StudentMarks with Mark NOT NULL), that is no longer a valid combination (not present in StudentMarksAfter) . This could range from putting the IND_ACTIVE to 'N' to warning the DBA or application developer about data corruption.

    Put all your INSERT, UPDATE and DELETE statements together and tweak them for use within a stored procedure. If you want to bring your system to its knees, call it from each and every INSERT, UPDATE and DELETE trigger you can define on those three base tables. That way at least, your system will be consistent.

    After 3 hours in production, you will be asked to undo whatever you have done and make the application work again, and run the stored procedure like once per day or per week, ....

    An alternative approach is: let the application that will be used for input of the student's results look at runtime what combinations are valid, and present all those to the user for input. I guess by the time the student's results are ready to be put into the system, all changes of specialisations, marks and whatever will be a thing of the past. This last approach is my suggestion.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Feb 2009
    Location
    India
    Posts
    14

    Thank You WIM

    Thanks a lot for your valuable suggestion, I'll follow the same steps as you said, Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •