SQL Server Performance

How to write this trigger??

Discussion in 'General Developer Questions' started by daipayan, Feb 27, 2009.

  1. daipayan New Member

    Hi All,
    I have a code snippet with insert T-SQL code, I want to make my insert code automatic through trigger, but I don't no, how should I do it, please help me, the table creation and the insert query is as follows:
    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;
  2. Adriaan New Member

    Are you sure you're not confusing a trigger with a script or a stored procedure?
  3. daipayan New Member

    Actually, I want to automate the code and for that I need trigger so that update/insert in StudentMark become automated, whenever data is inserted/updated in AssignMark, StudentSpecialisation and Course.
    Hope, I can make you understand!
  4. Adriaan New Member

    You could either create triggers on those three tables, or create a job that starts a stored procedure that handles everything. You can set the job to run at the most suitable interval.
    Assuming you need data from those three tables together before it can be added to the target table, a job would be better.
    If you are adding to those three tables from a client application, I would strongly suggest that you create a stored procedure for the follow-up, and launch that from the client app.
  5. daipayan New Member

    Sir,
    Am only 3 weeks old for MS SQL 2000, am still unknown to write this trigger/stored procedure. Please help me to write this trigger!
  6. Adriaan New Member

    That is pretty complex for someone with 3 weeks of experience ...
    You need to know how to program the basics before attempting a more complex task. So you have to look up "triggers" in Books Online and get the basics under your belt.
  7. daipayan New Member

    Sir, atleast write me the insert/update/delete trigger for students part, rest I'll do by taking the reference of student part, hope you will help me, THANK YOU!
  8. Adriaan New Member

    Please refer to Books Online for the CREATE TRIGGER syntax, and some nice examples to sink your teeth into.
    Because of the three tables involved, I'm not convinced a trigger is the way to go, and a stored procedure is a much better option. This sproc would insert the base data into those three tables, and then combine it into the fourth table.
    We cannot do your (school) work for you, we can only help you overcome problems with the work.
  9. daipayan New Member

    Ok! SirI'll try to solve it by myself only and then show you!

Share This Page