Using IF in trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using IF in trigger

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 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
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'
Begin
do Sotmhing..
End

Complicated 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 clause

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources