April 30, 2011 at 9:30 pm
Could someone please tell me what I'm doing wrong in this trigger?
Create Trigger BRR
on Registration
for insert,Update
as
Declare @sid varchar (50)=Null,
@Semester varchar (50)=Null,
@courseID varchar (50)=Null,
@course_name Varchar(50) =null,
@EnrollmentID varchar (50)=null,
@SemesterID varchar (50)=null,
@CourseID varchar (50)=null,
@InstructorID VARCHARV(50)=NULL,
@DID varchar (50)=null,
@department_name varchar (50)=null
IF (SELECT Due_Amount
FROM dbo.Student_Bill_Account
WHERE SID = @sid AND semesterID = @semester) > 0
BEGIN
Print 'You can not Enroll IN class without making payment'
END
ELSE
IF EXISTS (SELECT courseId
FROM dbo.registration
WHERE SID = @sid AND courseID = @courseID)
BEGIN
Print 'You already Registered this course'
END
Else IF
(SELECT COUNT( courseId)
FROM dbo.registration
WHERE SID = @sid AND course_name = @course_name ) = 0
BEGIN
INSERT INTO dbo.Registration (SemesterID, CourseID, SID)
VALUES (@EnrollmentID, @SemesterID , @CourseID, @sid,@InstructorID)
END
ELSE IF
(SELECT COUNT( courseId)
FROM dbo.registration
WHERE SID = @sid ) >= 1
BEGIN
-- Check whether he is enrolled in to this Program
IF EXISTS ( SELECT @sid
FROM Dept_Admission
WHERE department_name = @department_name AND SID = @sid)
BEGIN
INSERT INTO dbo.Registration (SemesterID, CourseID, SID)
VALUES (@EnrollmentID, @SemesterID , @CourseID, @sid,@InstructorID)
END
ELSE
BEGIN
PRINT 'You have to apply for Addmission in ISTC'
END
END
May 1, 2011 at 3:31 am
You might want to provide more information.
What is wrong with the following statement?
INSERT INTO dbo.Registration (SemesterID, CourseID, SID)
VALUES (@EnrollmentID, @SemesterID , @CourseID, @sid,@InstructorID)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 1, 2011 at 4:58 am
Lots of things wrong with the trigger...
To start with...
zayidka (4/30/2011)
IF (SELECT Due_AmountFROM dbo.Student_Bill_Account
WHERE SID = @sid AND semesterID = @semester) > 0
BEGIN
Print 'You can not Enroll IN class without making payment'
END
That's going to check a row from the table. No guarantee what row, just the first SQL finds. Hence, what it returns is very likely to be completely unrelated to the update/insert just done.
Or at least it would if @sid had a value. Since it's declared NULL and never assigned a value, that (and every other select) will return no rows.
Maybe something more like...
IF EXISTS (SELECT 1 FROM inserted WHERE Due_Amount <=0)
BEGIN
RAISERROR ('You can not Enroll IN class without making payment',16,4)
ROLLBACK
RETURN
END
Similar for all the other checks.
And no, you shouldn't be assigning variables from the inserted table, there can be more than one row in it.
Also, you should never print or select from a trigger.
As Corgi pointed out, your insert has a different number of columns in the column list than the values.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 6:19 am
I see this (SemesterID, CourseID, SID) shouldn't be there.
and the system that I was going to get the detail from is down for the next few hours.
I'm basicly trying to create a trigger with variables.
what kind of iinformation wouldd like me to provide?
thank You.
May 1, 2011 at 6:51 am
zayidka (5/1/2011)
I'm basicly trying to create a trigger with variables.
Triggers shouldn't in general use variables. The modified rows can be found in the inserted pseudo-table. Think about what you want to do to/with a set of modified rows (there may well be more than one)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 7:57 am
I'm creating School Database with few rules
Student who has unpaid balance cannot register for any more classes,
Students who are not registerd can only take one Trail class,and three credits only,(so there is credit resitriction even if they take one class which is 4 credit it should not be allowed)
students who are admitted to that department can self enroll up to 5 classes and up to 15 credits(basicly 1 class = 3 credits)
with approval they can take up to 20 credits
all students should have the option to pay online with Credit card,or if Financial Aid is approved,Financial aid may pay some or all, if there is a balance after Financial Aid students should again be able to use credit card or choose aoption to mail in their payments.
all students ust Score on 50% or higher on SAT 1600 and 2400 Tests to be admitted into the collage
these are some of the rules.
My tables
Students
SID
FirstName
Middle
LastName
Type
Phone
Address
City
Zip
State
Type
Course
CourseID
Semesterid
Subject
Days
Start_Time
End_Time
Location
Room
Max
Remaining
InstructorID
section
Course_credit
Cost
Course_description
Instructor
INstructorID
FirstName
LastName
Midle
Phone
Semester
SemesterID
Semester_Name
Semester_Start_Date
Semester_End_Date
Dept_Admission
DeptID
DeptName
SID
Registration
SID
courseID
SemesterID
RegistrationID
Student_Bill_Account
SID
semester_id
Bill_Amount
Due_Amount
Payment_method
Payment_Amount
Payment_date
Course_id
Financial_Aid
SID
SemesterID
Financial_Aid_Amount
Student_Test_Scores
SID
Test_name
Test_Date
Test_Score
Percentalie
Perfect_Score
Thank you very much for you help.
May 1, 2011 at 8:05 am
When you say "all students ust Score on 50% or higher on SAT 1600 and 2400 Tests to be admitted into the collage"
What do you mean by 2400 Tests?
What course are you taking?
What have you done so far?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 1, 2011 at 8:14 am
What do you mean by 2400 Tests?
It is Basically a SAT Test Name
Please see it here http://en.wikipedia.org/wiki/SAT
What course are you taking?
Databases management
What have you done so far?
Created those Table and tried to create trigger to enforce they rules as seen in my First Post above.
Thanks
May 1, 2011 at 8:18 am
We're not going to do your homework for you. Help and advise, sure.
Did you read any of my comments? Have you tried to fix the trigger based on those?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 8:31 am
Yes I did make few changes based on your first response thank you. But it seems I ‘ still stack.
I’m not asking you to do my homework, but Please provide me example like your first response, which worked very well.
May 1, 2011 at 8:39 am
I would suggest the you go through each requirement and start to write some code.
For example have you tried to write code that addresses the SAT requirement?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 1, 2011 at 8:53 am
zayidka (5/1/2011)
Yes I did make few changes based on your first response thank you. But it seems I ‘ still stack.I’m not asking you to do my homework, but Please provide me example like your first response, which worked very well.
Post your revised code, explain what doesn't work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 9:00 am
Did not get to the SAT yet, but
I have changed
IF (SELECT Due_Amount
FROM dbo. Student_Bill_Account
WHERE Due Amount) > 0
BEGIN
RAISERROR (‘You can not Enroll IN class without making payment',16,4)
ROLLBACK
RETURN
END
ELSE
IF EXISTS (SELECT coursed
FROM dbo.registration
WHERE dbo.registration .SID = INSERTED.SID)
BEGIN
RAISERROR (‘You already Registered this course',16,4)
ROLLBACK
RETURN
END
And is there anything wrong with
Create Trigger BRR
on Registration
for insert,Update
as
Begin
IF (SELECT Due_Amount
FROM dbo. Student_Bill_Account
WHERE Due Amount) > 0
BEGIN
RAISERROR (‘You can not Enroll IN class without making payment',16,4)
ROLLBACK
RETURN
END
It says incorrect syntax near ‘END’?
May 1, 2011 at 9:06 am
Yes there is. You have 2 BEGINs and one END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 9:09 am
OK, so try and write the code for the SAT, Number of credits, etc.
Use Gail's code as an example.
Give it a shot, one step at a time.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply