Home » SQL & PL/SQL » SQL & PL/SQL » Create new Trigger
Create new Trigger [message #206101] Tue, 28 November 2006 14:43 Go to next message
frosti
Messages: 2
Registered: November 2006
Junior Member
Hi, i'm totally new to triggers and stuff like that and I'm having a bit of trouble getting this right.

What I'm trying to do is to check if there are to many students registered to some course (maxStudents), and if there is so, then the student is putted on a waiting list.

When I try this code I get this:
Warning: Trigger created with compilation errors.
and of course the trigger doesn't work..

Any help would be so great.

CREATE TRIGGER DefaultScheduling
Before INSERT ON Register
REFERENCING NEW AS newRegister
FOR EACH ROW
BEGIN
 IF(
 (SELECT COUNT(*)
 FROM REGISTER
 WHERE course = :newRegister.course) > 
  (SELECT maxStudents
	FROM RestrictedCourses
	WHERE :newRegister.course = course))
 THEN (
 INSERT INTO WaitingList(course, student, registeringsTime)
 VALUES (:newRegister.course, :newRegister.student, null));
END IF;
END;
Re: Create new Trigger [message #206102 is a reply to message #206101] Tue, 28 November 2006 14:57 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Do a show err to tell you where the error lies.
Re: Create new Trigger [message #206106 is a reply to message #206102] Tue, 28 November 2006 15:20 Go to previous messageGo to next message
frosti
Messages: 2
Registered: November 2006
Junior Member
show err:
No errors.

But when I try to add rows to Register this error message appears:
Quote:
INSERT INTO Register VALUES (
*

ERROR at line 1:
ORA-04098: trigger 'xxxxxx.DEFAULTSCHEDULING' is invalid and failed re-validation
Re: Create new Trigger [message #206179 is a reply to message #206106] Wed, 29 November 2006 01:32 Go to previous messageGo to next message
rasikeg@gmail.com
Messages: 4
Registered: November 2006
Location: LK
Junior Member
its because your trigger code is still not functioning properly,
use this command and see what exactly is wrong with trigger code.

show errors trigger <you trigger name>;

show errors trigger DefaultScheduling;
Re: Create new Trigger [message #206186 is a reply to message #206101] Wed, 29 November 2006 02:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to do 'SHOW ERRORS' immediately after trying to compile the trigger. Alternatively, you could do
SELECT * FROM USER_ERRORS WHERE name = <trigger name>


Either of these will reveal a minor problem with the trigger, namely that the syntax
IF (SELECT....)
is invalid. You'll need to perform the selects and then compare the results.

Also, inside a trigger, you cannot perform DML on the table that the trigger is firing on - you'll get a mutating table error.
The exception to this is if your trigger is firing in response to an INSERT ....VALUES... statement, as that guarantees to only inset a single row.
Previous Topic: excel will read the vachar 1.10 as number when export from plsql
Next Topic: How to use run dos to connect ftp
Goto Forum:
  


Current Time: Fri Dec 09 00:07:30 CST 2016

Total time taken to generate the page: 0.09651 seconds