| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 10/26/2005 : 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 table
For online report cards
*/
CREATE TRIGGER tr_online_student_section_insert
ON dbo.sas_student_section
FOR Insert
AS
BEGIN 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')
BEGIN
INSERT 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 e
WHERE 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
;
END
ELSE
BEGIN
RAISERROR ('The online_student_section transaction failed. Changes were not saved .', 16, 1)
ROLLBACK TRANSACTION updateComment
;
END
END" |
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 10/26/2005 : 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'
Begin
do Sotmhing..
End
Complicated things can be done by simple thinking |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/27/2005 : 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 clause
Kristen |
|
|
| |
Topic |
|
|
|
Using IF in trigger
