| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-12-06, 15:45
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 47
|
|
|
Before insert Trigger
|
|
I have a table known as Registration in which there are columns StudentID, ModuleId, year, semester
semester = 1 or 2
I want to write a trigger so that student can register on a maximum of 4 modules per semester
i.e.
select count(*), semester from Registration where studentid='241234' group by semester
I tried the following trigger but I am stuck
If I dont use "for each row", I am not allowed to use :new and ld and when I use "for each row", count would be for each row which would be 1 or 0
How can I solve this problem?
Code:
create or replace trigger checkmodulecount
before insert on Registration
DECLARE
noCourse NUMBER;
nCount NUMBER;
FAILED EXCEPTION;
BEGIN
Select count(*) into nCount
from Registration
where studentid=:old.studentid;
if noCourse > 3 then
RAISE FAILED;
END IF;
EXCEPTION
WHEN FAILED THEN
raise_application_error(-20000,'Cannot reigster for more than four courses');
END;
|
|
11-12-06, 16:52
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,870
|
|
Something like this could do the job. Semester is not included into the SELECT statement; you could, perhaps, use only MAX(semester) because, well, I guess you can register courses for the last semester and not previous one, which is already over. Right?
Code:
CREATE OR REPLACE TRIGGER trg_check
BEFORE INSERT ON registration
FOR EACH ROW
DECLARE
no_count NUMBER;
BEGIN
SELECT COUNT(*) INTO no_count
FROM registration
WHERE studentid = :NEW.studentid;
IF no_count = 4 THEN
RAISE_APPLICATION_ERROR(-20101, 'Can not register for more than 4 courses');
END IF;
END;
/
|
|
11-12-06, 17:37
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 47
|
|
|
|
Thanks one more question:
why this wont work?
select M.MODULEID, M.MODULETITLE, R.YEAR, R.SEMESTER, avg(R.CWMARK), AVG(R.EXAMMARK)
from Registration R, MODULE M WHERE M.MODULEID like 'AR%'
and R.MODULEID=M.MODULEID group by M.MODULEID;
It gives the errror as
not a GROUP BY EXPRESSION
|
|
11-13-06, 01:14
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,870
|
|
It is because you didn't read manual about the GROUP BY clause carefully enough.
Quote:
|
Originally Posted by Oracle
ORA-00979 not a GROUP BY expression
Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.
Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.
|
In other words, include all columns that are not included into AVG function into the GROUP BY (those would be "M.MODULEID, M.MODULETITLE, R.YEAR, R.SEMESTER").
|
|
11-13-06, 08:58
|
|
Lead Application Develope
|
|
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,354
|
|
Quote:
|
Originally Posted by Littlefoot
Something like this could do the job. Semester is not included into the SELECT statement; you could, perhaps, use only MAX(semester) because, well, I guess you can register courses for the last semester and not previous one, which is already over. Right?
Code:
CREATE OR REPLACE TRIGGER trg_check
BEFORE INSERT ON registration
FOR EACH ROW
DECLARE
no_count NUMBER;
BEGIN
SELECT COUNT(*) INTO no_count
FROM registration
WHERE studentid = :NEW.studentid;
IF no_count = 4 THEN
RAISE_APPLICATION_ERROR(-20101, 'Can not register for more than 4 courses');
END IF;
END;
/
|
This will not work either, You will get the dreaded ORA-04091(Mutating table). See the following link from asktom on a technique to avoid the error.
http://asktom.oracle.com/~tkyte/Mutate/index.html
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice. 
|
|
11-14-06, 01:08
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,870
|
|
Sorry, Bill, but it wouldn't raise murating table error and, actually, would work:
Code:
SQL> CREATE TABLE registration (studentid NUMBER, course_id NUMBER);
Table created.
SQL> CREATE OR REPLACE TRIGGER trg_check
2 BEFORE INSERT ON registration
3 FOR EACH ROW
4 DECLARE
5 no_count NUMBER;
6 BEGIN
7 SELECT COUNT(*) INTO no_count
8 FROM registration
9 WHERE studentid = :NEW.studentid;
10
11 IF no_count = 4 THEN
12 RAISE_APPLICATION_ERROR(-20101, 'Can not register for more than 4 cour
ses');
13 END IF;
14 END;
15 /
Trigger created.
SQL>
Now, try to enter more than 4 courses:
Code:
SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 101);
1 row created.
SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 102);
1 row created.
SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 103);
1 row created.
SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 104);
1 row created.
SQL> INSERT INTO registration (studentid, course_Id) VALUES (1, 105);
INSERT INTO registration (studentid, course_Id) VALUES (1, 105)
*
ERROR at line 1:
ORA-20101: Can not register for more than 4 courses
ORA-06512: at "SCOTT.TRG_CHECK", line 9
ORA-04088: error during execution of trigger 'SCOTT.TRG_CHECK'
SQL>
|
|
11-15-06, 08:55
|
|
Lead Application Develope
|
|
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,354
|
|
Littlefoot,
What version of Oracle are you running. I had heard that they were making changes to allows selects on the trigger table in oracle 10
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
|
|
11-15-06, 08:58
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,870
|
|
This one:
Code:
SQL*Plus: Release 9.0.1.4.0 - Production on Sri Stu 15 14:58:28 2006
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
|
|
11-15-06, 11:09
|
|
Lead Application Develope
|
|
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,354
|
|
I was wrong (the shame of it all), it isn't select that will cause the error. It is update or delete on the triggering table. Sorry.
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
|
|
11-19-06, 09:55
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
That trigger can cause the "table is mutating error", but not with an INSERT...VALUES statement. Suppose we try to copy the 4 rows for student_id=1 to student_id=2:
Code:
SQL> INSERT INTO registration (studentid, course_Id)
2 SELECT 2, course_id from registration where studentid=1;
ORA-04091: table TACL.REGISTRATION is mutating, trigger/function may not see it
ORA-06512: at "TACL.TRG_CHECK", line 4
ORA-04088: error during execution of trigger 'TACL.TRG_CHECK'
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Before insert Trigger
