Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Apr 2013
    Posts
    18

    Unanswered: oracle create trigger

    Hi to All,
    could someone help me resolve this problem?
    When i create the trigger it show up a compilation error.

    Code:
    Code:
    CREATE TRIGGER CHECK_DATE
    BEFORE INSERT OR UPDATE
    ON OFFERING E
    WHEN (TO_CHAR(NEW.START_DATE,'MM') = 12)
    BEGIN
    IF NEW.COURSE_ID = (SELECT E.COUSE_ID FROM COURSE E WHERE E.DURATION = 5) THEN
    RAISE_APPLICATION_ERROR (-20100, 'Cannot insert course of 5 days duration which are running in December');
    END IF;
    END;
    /
    The error message was this
    Code:
    Warning: Trigger created with compilation errors.
    when i check with
    Code:
    SHOW ERROR TRIGGER CHECK_DATE
    it show this message
    Code:
    Errors for TRIGGER CHECK_DATE:
    LINE/COL	 ERROR
    2/1	 PL/SQL: Statement ignored
    2/7	 PLS-00201: identifier 'NEW.COURSE_ID' must be declared
    Thank.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE TRIGGER check_date 
      BEFORE INSERT OR UPDATE ON offering 
      WHEN (To_char(NEW.start_date, 'MM') = 12) 
    BEGIN 
        IF :NEW.course_id = (SELECT E.couse_id 
                             FROM   course E 
                             WHERE  E.duration = 5) THEN 
          Raise_application_error (-20100, 
          'Cannot insert course of 5 days duration which are running in December' 
          ); 
        END IF; 
    END; 
    
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2013
    Posts
    18
    When i execute the code you write it show an error message

    CODE:
    Code:
    CREATE TRIGGER check_date 
      BEFORE INSERT OR UPDATE ON offering 
      WHEN (To_char(NEW.start_date, 'MM') = 12) 
    BEGIN 
        IF :NEW.course_id = (SELECT E.couse_id 
                             FROM   course E 
                             WHERE  E.duration = 5) THEN 
          Raise_application_error (-20100, 
          'Cannot insert course of 5 days duration which are running in December' 
          ); 
        END IF; 
    END; 
    
    /

    error message:
    Code:
      WHEN (To_char(NEW.start_date, 'MM') = 12)
      *
    ERROR at line 3: 
    ORA-04077: WHEN clause cannot be used with table level triggers
    Is it possible to use different table's column to create trigger?
    thank.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1) you should NEVER rely on implicit type conversion (12 is a NUMBER)
    2) you could include the test for December after BEGIN using IF statement
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by edwardczy View Post
    error message:
    Code:
      WHEN (To_char(NEW.start_date, 'MM') = 12)
      *
    ERROR at line 3: 
    ORA-04077: WHEN clause cannot be used with table level triggers
    The error message says it all: you are defining a table level trigger, but the WHEN clause can only be used for row level triggers.
    You need to add FOR EACH ROW to your trigger.

    For details read the manual.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Apr 2013
    Posts
    18
    I'm already try to include the "FOR EACH ROW" code by having error message of
    Code:
    Warning: Trigger created with compilation errors.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you could provide a test case (CREATE TABLE) and the latest version of code you wrote, we could try it.

    Now? You know how it goes - SHOW ERROR.

  8. #8
    Join Date
    Apr 2013
    Posts
    18
    Creation of table:
    COURSE table:
    SQL> CREATE TABLE COURSE (
    2 COURSE_ID VARCHAR2(6) NOT NULL,
    3 TITLE VARCHAR2(30),
    4 COST NUMBER(4),
    5 DURATION NUMBER(1));

    Table created.
    SQL> SPOOL OFF

    OFFERING table:

    SQL> CREATE TABLE OFFERING (
    2 OFFERING_ID NUMBER(4) NOT NULL,
    3 COURSE_ID VARCHAR2(6) NOT NULL,
    4 START_DATE DATE,
    5 LOCATION VARCHAR2(15),
    6 INSTRUCTOR VARCHAR2(10));

    Table created.
    SQL> SPOOL OFF

    STUDENT table:

    SQL> CREATE TABLE STUDENT (
    2 STUDENT_ID NUMBER(7) NOT NULL,
    3 SNAME VARCHAR2(10),
    4 GENDER VARCHAR2(6),
    5 HP_NO VARCHAR2(11),
    6 CNAME VARCHAR2(30),
    7 OFFERING_ID NUMBER(4)
    8 COURSE_ID VARCHAR2(6));

    Table created.
    SQL> SPOOL OFF

    ATTENDANCE table:

    SQL> CREATE TABLE ATTENDANCE (
    2 OFFERING_ID NUMBER(4) NOT NULL,
    3 STUDENT_ID NUMBER(7) NOT NULL,
    4 EVALUATION NUMBER(1),
    5 RESULT VARCHAR2(4));

    Table created.
    SQL> SPOOL OFF

    Test data in table COURSE:
    SQL> SELECT * FROM COURSE;

    COURSE TITLE COST DURATION
    ------ ------------------------------ ---------- ----------
    5CQ017 PROJECT MANAGEMENT 1500 4
    5CC098 APPLIED COMPUTING 2000 5
    5MM196 ART AND DESIGN 500 2
    3AC005 ACCOUNTING AND FINANCE 750 3
    6EE324 ACADEMIC PRACTICE 300 1
    8DC557 ADVERTISING AND MEDIA 1350 4

    6 rows selected.
    SQL> SPOOL OFF

    Test data in table OFFERING:
    SQL> SELECT * FROM OFFERING;

    OFFERING_ID COURSE START_DAT LOCATION INSTRUCTOR
    ----------- ------ --------- --------------- ----------
    1001 5CQ017 01-JAN-13 LONDON JAMES
    1002 5CQ017 01-FEB-13 ROME KING
    1003 3AC005 20-SEP-12 MANCHESTER CLARK
    1004 3AC005 09-NOV-12 LONDON FORD
    1005 3AC005 27-APR-07 ITALY LUCY
    1006 5CC098 04-MAR-13 LONDON BROOKE
    1007 8DC557 15-MAY-13 ROME KENT
    1008 6EE324 12-JUL-12 WOLVERHAMPTON ALLAN
    1009 5MM196 22-JUN-13 LIVERPOOL SMITH

    9 rows selected.
    SQL> SPOOL OFF

    Test data in table STUDENT:
    SQL> SELECT * FROM STUDENT;

    STUDENT_ID SNAME GENDER HP_NO CNAME OFFERING_ID COURSE
    ---------- ---------- ------ ----------- --------------- ----------- ------
    1132280 JOHN MALE 07711324876 INTEL 1001 5CQ017
    1132281 CELI FEMALE 07961355876 INTEL
    1132282 SERENA FEMALE 07449037864 MOTOROLA 1002 5CQ017
    1132283 WILSON MALE 07789017374 PHILIPS 1001 5CQ017
    1132284 VINCENT MALE 07688359042 INTEL 1001 5CQ017
    1132285 NARY FEMALE 07923235839 INTEL 1002 5CQ017
    1132286 ANDO MALE 07965528765 MOTOROLA
    1132287 LEO MALE 07752626003 DELL 1004 3AC005
    1132288 STEVEN MALE 07707046634 PHILIPS 1006 5CC098
    1132289 TINA FEMALE 07736727576 TTVISION 1005 3AC005

    10 rows selected.
    SQL> SPOOL OFF

    Test data in table ATTENDANCE:
    SQL> SELECT * FROM ATTENDANCE ORDER BY OFFERING_ID;

    OFFERING_ID STUDENT_ID EVALUATION RESU
    ----------- ---------- ---------- ----
    1001 1132280 3 B
    1001 1132283 2 C
    1001 1132284 1 C
    1002 1132282 4 A
    1002 1132285 0 F
    1004 1132287 2 B
    1005 1132289 3 A
    1006 1132288 4 B

    8 rows selected.
    SQL> SPOOL OFF

    There were the table creation and test data that I had inserted. Thank.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    too bad we don't have the INSERT statements to populate the tables with data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here is a trigger whose code compiles (based on the last one you posted):
    Code:
    SQL> CREATE OR REPLACE TRIGGER check_date
      2     BEFORE INSERT OR UPDATE
      3     ON offering
      4     FOR EACH ROW
      5     WHEN (TO_CHAR (NEW.start_date, 'MM') = 12)
      6  DECLARE
      7     l_course_id   course.course_id%TYPE;
      8  BEGIN
      9     SELECT e.course_id
     10       INTO l_course_id
     11       FROM course e
     12      WHERE e.duration = 5;
     13
     14     IF :NEW.course_id = l_course_id
     15     THEN
     16        Raise_application_error (
     17           -20100,
     18           'Cannot insert course of 5 days duration which are running in December');
     19     END IF;
     20  END check_date;
     21  /
    
    Trigger created.
    
    SQL>
    I didn't check whether it actually works or not (don't feel like populating tables manually); note that SELECT might fail for different reasons (such as NO-DATA-FOUND or TOO-MANY-ROWS), which are not handled.

  11. #11
    Join Date
    Apr 2013
    Posts
    18
    The trigger was created but I still haven't test it out. I will let you know I work or not as soon as I test it. Thank you very much. And may I know that what was "l_course_id course.course_id%TYPE;" means.

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    L_COURSE_ID is a variable whose datatype is equal to a column named COURSE_ID which belongs to the COURSE table.

  13. #13
    Join Date
    Apr 2013
    Posts
    18
    I know it a bit silly, but can I know what the different between the code you write with the code I write. Why I code will have compilation error when execute the command. Thank.

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't know because I didn't see the final code you wrote.

  15. #15
    Join Date
    Apr 2013
    Posts
    18
    I mean the code I post above with the code you write. As shown above my code will have trigger created with compilation error and your code let the trigger created without error.

Posting Permissions

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