Results 1 to 4 of 4

Thread: Trigger problem

  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: Trigger problem

    hey i'm trying to get this triggger work but for some reason when i run it
    in Oracle SQL Plus, nothing happens, like it doesn't execute it. I've seen
    a few examples of triggers and taken bits and pieces from each of the examples
    to try and write my own but it's not working. What have i done wrong? Basically
    this trigger is supposed to ensure that a course can only have ONE and ONLY one lecturer assigned for that semester and year.



    Code:
    CREATE TRIGGER checkCountLect
    AFTER INSERT OR UPDATE ON TeachingAssignment
    REFERENCING 
    	NEW ROW AS newEntry
    
    FOR EACH ROW
    	WHEN (EXISTS (SELECT count(*)
    	              FROM TeachingAssignment
    				  GROUP BY Course_ID, Year_Taught, Semester
    				  HAVING count(*) > 1
    				  ))
    				  
    	BEGIN 
    		DELETE FROM TeachingAssignment
    		WHERE newEntry.Staff_ID = Staff_ID
    		AND newEntry.Course_ID = Course_ID 
    		AND newEntry.YearTaught = Year_Taught
    		AND newEntry.Semeseter = Semester
    	
    	raise_application_error(-20002,'Lecture already assigned to course for that semester and year.');
    		
    	END;

    Thanx heaps

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Castiel
    hey i'm trying to get this triggger work but for some reason when i run it
    in Oracle SQL Plus, nothing happens, like it doesn't execute it. I've seen
    a few examples of triggers and taken bits and pieces from each of the examples
    to try and write my own but it's not working. What have i done wrong?
    The code you posted is trigger definition code. It is "run" when the triggering condition is fulfilled (INSERT or UPDATE of TeachingAssignment is executed). Of course, the trigger has to be valid in the first place, which I doubt for the one you posted. Anyway, as 'not working' is not known Oracle message, so I wonder what you really execute and what is exact Oracle response to that.
    Quote Originally Posted by Castiel
    Basically
    this trigger is supposed to ensure that a course can only have ONE and ONLY one lecturer assigned for that semester and year.
    What about creating unique constraint on (Course_ID, Year_Taught, Semester) columns?

  3. #3
    Join Date
    Sep 2009
    Posts
    16

    Thanx but...still syntax help?

    hey thanx flyboy i'll try that, but i was kinda hoping someone could
    tell me where i was syntactically wrong cos my other triggers have been
    written in much the same way except for different check condtions
    and none of them seem to execute, they just go the the next line (i don't get a message saying errror) the response is much like when you type in a select
    statement and you press enter, it jst goes to the next line, nothin is actually run/executed until it see the ; operator. But in this trigger i HAVE the ; operator
    but it just goes to the next line.


    Cheers

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by flyboy
    I wonder what you really execute and what is exact Oracle response to that.
    And after your post, it is still the same (although I have some guesses).
    Seems you are using SQL*Plus (guess). Then, you need to type slash (/) after that definition; the same way you run anonymous PL/SQL block.
    I wonder, where you found that 'a few examples of triggers'. Anyway, nice coding examples are in the documentation, available e.g. online on http://tahiti.oracle.com/. Just search for CREATE TRIGGER statement in the SQL Reference book. As you did not provide your Oracle version, I will let it on you.

    [Edit: typo]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •