Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Question Unanswered: creating a insert trigger

    i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.

    The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated.
    Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.


    My Two tables DDL are like down below

    create table student(
    studentId char(4) not null primary key,
    fName varchar(25) not null,
    mI char(1) null,
    lName varchar(25) not null,
    insCheck char(1) not null,
    );

    create table instructor(
    instructorId char(4) not null primary key,
    fName varchar(25) not null,
    mI char(1) null,
    lName varchar(25) not null,
    instructorQual varchar(100) not null,
    );

    thanks for your help in advance!
    gazawaymy

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You don't need a trigger for this. In the proc that populates your student, if they have the insCheck = 'y' then execute the proc to populate the instructor with the appropriate parameters.

    Also, why are you populating the studentId and instructorId manually? Can you explain this process? It sounds like a design issue. Is there a formula to determine this so you can automate it?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2004
    Posts
    19

    My db

    I didn't know I can do it that way with procedure. I still have to figure out the actual procedure code. Do you have the code in mine?

    I made student, instructor table's primary key to be manual because of relationshion between contract and these tables. Since i already populate a lot of records and these are the primary keys, I am not sure I should change the table structure. What do you think?

    If I should change to automatic, how I do that?

    thanks
    michelle

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are you storing Students and Instructors in separate tables? Store them in a single table ("People", for instance).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2004
    Posts
    19

    trigger

    because

    student and instructor table are connected with class table.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's not a reason to separate the two:

    Table PEOPLE (PersonID, PersonName, InstructorFlag)

    Table CLASSES(ClassID, InstructorPersonID, ClassName)

    Table CLASSMEMBERS(ClassID, PersonID)

    Strictly speaking, the instructor flag is not necessary but makes interface coding easier.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You're doing this crap on a Saturday?

    I thought I had an addiction....ok I do...

    And yes the flag is bs....the relationship identifies if the person is or isn't an instructor...


    what they can't be both?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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