Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: Trigger preventing any data entry

    Hey i've got a problem inserting anything into my table, my trigger
    seems to be stopping any data entry at all and I don't know why.
    What the trigger is supposed to do is only allow NULL entries for a students
    grade to be entered if it is the current semester. The SELECT query
    will ONLY ever produce ONE record, which is the record of the current semester.


    Code:
    CREATE OR REPLACE TRIGGER checkValidEntry
    BEFORE INSERT OR UPDATE ON Course_Enrollment
    
    FOR EACH ROW
    
    DECLARE
    	year_sem char(6)  := '';
    	
    BEGIN
    	SELECT Year_Semester INTO year_sem
    		FROM Control C
    			WHERE C.Current_Sem = 'True';
    				
    	IF(:NEW.Year_Semester != year_sem) THEN 
    		IF(:NEW.Grade = NULL) THEN
    			raise_application_error(-20006,'The student can only have a NULL grade for the current event period.');
    		END IF;
    	END IF;
    END;
    /

    After executing my FIRST tuple like this:

    Code:
    INSERT INTO Course_Enrollment VALUES ('s4122289','DARK4102','2009 2',NULL);
    I get the following error.

    Error starting at line 1 in command:
    INSERT INTO Course_Enrollment VALUES ('s4122289','DARK4102','2009 2',NULL)
    Error report:
    SQL Error: ORA-01403: no data found
    ORA-06512: at "S4121289.CHECKVALIDENTRY", line 5
    ORA-04088: error during execution of trigger 'S4121289.CHECKVALIDENTRY'
    01403. 00000 - "no data found"
    *Cause:
    *Action:


    Thanx

  2. #2
    Join Date
    May 2006
    Posts
    132
    You should use IS NULL to test for NULL strings.

    Code:
    IF(:NEW.Grade IS NULL) THEN

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ebrian
    You should use IS NULL to test for NULL strings.

    Code:
    IF(:NEW.Grade IS NULL) THEN
    Although this is right suggestion, this bug is not the cause of
    Quote Originally Posted by Castiel
    SQL Error: ORA-01403: no data found
    ORA-06512: at "S4121289.CHECKVALIDENTRY", line 5
    Although the line does not match in the posted code, I would bet, that the first and only SELECT resides there. Then, hey is not the error self-descriptive? There is no row with given WHERE condition in CONTROL table. Just run it in any tool (TOAD, sqlplus, ...; without the INTO part, of course) and check the result. Note, that case matters and, in case that CURRENT_SEM has CHAR(6) data type (as the variable you are assigning it to), also length matters - the stored string it is right-padded with blanks to 6 characters.

    Maybe I am repeating myself in vain, however I have to post it again:
    please consult SQL Reference book. It is part of Oracle documentation, available e.g. on http://tahiti.oracle.com/
    You do not need to read it up and down, just search there for every part of SQL language you use. It will not take longer than waiting for answer in any forum you ask.

  4. #4
    Join Date
    Sep 2009
    Posts
    16

    All good :)

    Thanx guys, the select clause needed fixing, i didn't see that
    in the the line

    Code:
    From Control C
    the C is actually a special keyword.


  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A "special keyword"? What is that?

    I *think* that you got something wrong, because "CONTROL" (nor "C") is not special in any way.
    Code:
    SQL> select * From v$reserved_words where keyword like '%CONTROL%' order by keyword;
    
    KEYWORD                            LENGTH R R R R D
    ------------------------------ ---------- - - - - -
    CONTROLFILE                            11 N N N N N
    
    SQL>
    Code:
    SQL> select c.dname from dept c;
    
    DNAME
    --------------
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    
    SQL> create synonym control for dept;
    
    Synonym created.
    
    SQL> select c.dname from control c;
    
    DNAME
    --------------
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    
    SQL>

  6. #6
    Join Date
    Dec 2009
    Posts
    2
    hi to all......
    I too had the same problem
    Thanks for your posting and all your replies......
    Can anyone explain about Lacerte W-2 data entry via SCANNER...
    Thanks....
    ......................

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    doesn't your trigger force a grade if its not the current semester, rather than ensuring the current semester is null? Also, your trigger would allow me to put in grades for the next 4 years for someone if the price were right. Me thinks you need to rethink what you are trying to accomplish.

    Dave

Posting Permissions

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