Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2005-10-26 : 07:32:28
|
cthang writes "Hi,I am having some problems figuring out how to use an if statement in my insert trigger.When a user inserts a new course I want the trigger to set a default value only for specific courses.Here is my trigger./*This trigger is to insert student sections into the online_student_section_marks tableFor online report cards*/CREATE TRIGGER tr_online_student_section_insertON dbo.sas_student_sectionFOR Insert ASBEGIN TRANSACTION insertSection BEGIN/* Variable Declarations */DECLARE @errorSave int, @studentID int, @sectionCode int, @course varchar(16)/* Test for course and Insert ME as necessary */IF @course IN ('art1', 'art2', 'art3', 'art4', 'art5', 'art6', 'art7', 'art8','ba5','ba6','ba7','ba8','bfr1','bfr2','bfr3','bfr4','bfr5','bfr6','bfr7','bfr8','xart1', 'xart2', 'xart3', 'xart4', 'xart5', 'xart6', 'xart7', 'xart8')BEGININSERT INTO online_student_section_marks (student_id,section_code,course,section,school,semester,year,LO1_term1,LO2_term1,LO3_term1,LO1_term2,LO2_term2,LO3_term2,LO1_term3,LO2_term3,LO3_term3,LO1_term4,LO2_term4,LO3_term4)SELECT s.student_number,s.section_code,a.course,a.section,a.school,a.semester,a.year,'ME','ME','ME','ME','ME','ME','ME','ME','ME','ME','ME','ME'FROM sas_section a,sas_student_section s, Inserted i,myt_student m, myt_student_enrolment eWHERE s.student_number = i.student_number AND s.section_code = i.section_code AND a.code = i.section_code AND m.student_number = i.student_number AND m.student_number = e.student_id AND m.enrolment_sequence = e.sequence_number AND e.grade < 'K' IF (@@ERROR <> 0) BEGIN SET @ErrorSave = @ErrorSave + @@ERROR END END /* Error Trap - Test Transaction and Commit or Rollback as Necessary */IF @ErrorSave = 0 BEGIN COMMIT TRANSACTION updateComment ; ENDELSE BEGIN RAISERROR ('The online_student_section transaction failed. Changes were not saved .', 16, 1) ROLLBACK TRANSACTION updateComment ; ENDEND" |
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-10-26 : 08:27:41
|
I need didnt understood extactly what you want....but here is what i thought you required .. Declare @course Varchar(100)Select @Course = @course From Inserted where... and then IF @course == 'somthing' Begindo Sotmhing.. EndComplicated things can be done by simple thinking |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-27 : 01:08:48
|
Don't you just need to add to the WHERE clause? For example:INSERT INTO online_student_section_marks (...)SELECT s.student_number, ...FROM sas_section a, ...WHERE s.student_number = i.student_number ... AND course IN ('art1', 'art2', ...) By the by, you would be better off using the JOIN syntax rather than a list of tables and equality tests in the WHERE clauseKristen |
|
|
|
|
|