Home » SQL & PL/SQL » SQL & PL/SQL » Trigger sum constraints (Oracle 11g)
Trigger sum constraints [message #446276] Sun, 07 March 2010 07:45 Go to next message
cronin
Messages: 2
Registered: March 2010
Junior Member
First of all, I'm very new to oracle and sql in general so this question may be obvious, impossible, or anywhere in between.

Basically, I need to fulfil the constraint:
"student cannot have more than 100 points"

I'm pretty sure I need to use a trigger to do this, but I don't understand how this would work.

Written in plain english I imagine it work work something like:
AFTER UPDATE
WHERE student.lessonid = lesson.lessonid
IF SUM(lesson.points) > 100 then ROLLBACK

lessonid is the only field relating the two tables.

--

Again, I apologise if this is a daft question, I hope someone can at least point me in the right direction to being able to fulfil that criteria, whether it be using triggers or not.

Thanks!
Re: Trigger sum constraints [message #446277 is a reply to message #446276] Sun, 07 March 2010 08:03 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
why dont you write it in a plsql block.

if (your condition)
then
exit;
end if;

you can even create within a procedure or function.
Re: Trigger sum constraints [message #446280 is a reply to message #446276] Sun, 07 March 2010 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In a trigger you don't select/modify the table you are modifyin, you use the values you have in your statement the one that existed in the row you modify.

See Database Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Regards
Michel
Re: Trigger sum constraints [message #446285 is a reply to message #446276] Sun, 07 March 2010 08:53 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
If lessonid is primary/unique key in the lesson table, then you could just add a check constraint on the lesson.points column:
ALTER TABLE lesson
  ADD CONSTRAINT chk_points CHECK (points <= 100);

Smile

Re: Trigger sum constraints [message #446286 is a reply to message #446285] Sun, 07 March 2010 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But a student may have follow several lessons so it is the sum of his/her points and this can't be done with declarative constraint.

Regards
Michel
icon12.gif  Re: Trigger sum constraints [message #446287 is a reply to message #446286] Sun, 07 March 2010 09:09 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Michel Cadot wrote on Sun, 07 March 2010 09:57
But a student may have follow several lessons so it is the sum of his/her points and this can't be done with declarative constraint.

True unless the lessonid is unique.

The requirement and code posted by OP suggests the 100 points refer to ONE lesson:

Quote:
"student cannot have more than 100 points"
And...
WHERE student.lessonid = lesson.lessonid

Razz
Re: Trigger sum constraints [message #446288 is a reply to message #446276] Sun, 07 March 2010 09:11 Go to previous messageGo to next message
cronin
Messages: 2
Registered: March 2010
Junior Member
My apologies for not being clear, the student should be able to take more than one lesson (each lesson is a maximum of 20 points), summing up to a total of <= 100.
icon6.gif  Re: Trigger sum constraints [message #446289 is a reply to message #446288] Sun, 07 March 2010 09:15 Go to previous message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
cronin wrote on Sun, 07 March 2010 10:11
My apologies for not being clear, the student should be able to take more than one lesson (each lesson is a maximum of 20 points), summing up to a total of <= 100.

OK, then you need to follow Michel's posts.
Cool
Previous Topic: Fetch data based max data and rowcount
Next Topic: Query Help
Goto Forum:
  


Current Time: Tue Sep 27 14:31:25 CDT 2016

Total time taken to generate the page: 0.15218 seconds