1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Whats wrong with my trigger

Discussion in 'SQL PL/SQL' started by mukulverma2408, Aug 1, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi,

    is it possible to define a variable in the trigger? Below is the trigger which is used to restrict insertion of emp on weekday but it's not working
    Code (Text):

    CREATE OR REPLACE TRIGGER SECURE_EMP BEFORE INSERT ON EMP
    DAY VARCHAR2;
     BEGIN
      SELECT TO_CHAR(SYSDATE,'DY') INTO DAY FROM DUAL;
        IF DAY IN ('SAT','SUN');
        RAISE_APPLICATION_ERROR(-20500,'INSERTION CAN BE DONE ONLY ON WEEKDAY');
        END IF;
    END SECURE_EMP;
     
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Code (SQL):
    CREATE OR REPLACE TRIGGER SECURE_EMP BEFORE INSERT ON EMP
    DECLARE
    DAY VARCHAR2(500);
    BEGIN
      SELECT TO_CHAR(SYSDATE,'DY') INTO DAY FROM DUAL;
        IF DAY IN ('SAT','SUN');
        RAISE_APPLICATION_ERROR(-20500,'INSERTION CAN BE DONE ONLY ON WEEKDAY');
        END IF;
    END SECURE_EMP;
    Before defining Variable, Just use Declare statement.
     
    mukulverma2408 likes this.
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thanks Bharatt :) it's working perfectly as expected.

    Can you help me with one more trigger, i am not able to identify problem with this trigger which is giving compilation error :
    Code (Text):

    create or replace
    TRIGGER AUDIT_ARPU AFTER INSERT OR UPDATE OR DELETE ON ARPU FOR EACH ROW
    BEGIN
    IF INSERTING THEN
    INSERT INTO AUDIT_EMP (MDATE,MODIFIED_BY,OPERATION,OLD_VALUE,NEW_VALUE) VALUES (SYSDATE,USER,INSERT,:OLD.EMPLOYEE_ID,:NEW.EMPLOYEE_ID);
    ELSIF DELETING THEN
    INSERT INTO AUDIT_EMP (MDATE,MODIFIED_BY,OPERATION,OLD_VALUE,NEW_VALUE) VALUES (SYSDATE,USER,DELETE,:OLD.EMPLOYEE_ID,:NEW.EMPLOYEE_ID);
    ELSIF UPDATING THEN
    INSERT INTO AUDIT_EMP (MDATE,MODIFIED_BY,OPERATION,OLD_VALUE,NEW_VALUE) VALUES (SYSDATE,USER,UPDATE,:OLD.EMPLOYEE_ID,:NEW.EMPLOYEE_ID);
    END IF;
    END AUDIT_ARPU;

     
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    As has been noted before to you -- providing the error message makes it easier for people to locate the problems. In this case, you had no quotes around the INSERT/UPDATE/DELETE strings in your VALUES clause. There may be other problems, but those will certainly cause errors.

    Code (Text):

    create or replace
    TRIGGER AUDIT_ARPU AFTER INSERT OR UPDATE OR DELETE ON ARPU FOR EACH ROW
    BEGIN
      IF INSERTING THEN
        INSERT INTO AUDIT_EMP (MDATE,MODIFIED_BY,OPERATION,OLD_VALUE,NEW_VALUE)
           VALUES (SYSDATE,USER,'INSERT',:OLD.EMPLOYEE_ID,:NEW.EMPLOYEE_ID);
      ELSIF DELETING THEN
        INSERT INTO AUDIT_EMP (MDATE,MODIFIED_BY,OPERATION,OLD_VALUE,NEW_VALUE)
          VALUES (SYSDATE,USER,'DELETE',:OLD.EMPLOYEE_ID,:NEW.EMPLOYEE_ID);
      ELSIF UPDATING THEN
        INSERT INTO AUDIT_EMP (MDATE,MODIFIED_BY,OPERATION,OLD_VALUE,NEW_VALUE)
          VALUES (SYSDATE,USER,'UPDATE',:OLD.EMPLOYEE_ID,:NEW.EMPLOYEE_ID);
      END IF;
    END AUDIT_ARPU;
     
  5. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    The only reason for not sharing the exact error message is that i am not getting any, the only problem is while writing trigger on command line it just gave a message
    with no details of lines having the problem.

    Am i doing the things wrongly??
     
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Sounds like you're using SQL*Developer. The compilation errors are available -- you just have to look for them. If you Google "sql*developer compilation errors", you'll get a range of hits on how to do this. If you aren't using SQL*Developer, then modify the search for the compiler you are using.
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Check like this...

    select * from all_errors where name='YOUR_TRIGGER'
     
  8. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thanks Matthew and Jagadekara, this was really helpful
     
    jagadekara likes this.
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    When you get this message:

    trigger created with compilation error

    type this at the command line:

    show errors trigger audit_arpu

    and you will see all of the errors and problems SQL*Plus encountered when compiling your code. You can then take action and fix the problem areas.
     
    mukulverma2408 likes this.
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Matthew got the issue correct, you're missing single quotes around your string values:

    Code (SQL):
    SQL> CREATE OR REPLACE
      2   TRIGGER AUDIT_ARPU AFTER INSERT OR UPDATE OR DELETE ON ARPU FOR EACH ROW
      3   BEGIN
      4   IF INSERTING THEN
      5   INSERT INTO AUDIT_EMP (MDATE,MODIFIED_BY,OPERATION,OLD_VALUE,NEW_VALUE) VALUES (SYSDATE,USER,INSERT,:OLD.EMPLOYEE_ID,:NEW.EMPLOYEE_ID);
      6   ELSIF DELETING THEN
      7   INSERT INTO AUDIT_EMP (MDATE,MODIFIED_BY,OPERATION,OLD_VALUE,NEW_VALUE) VALUES (SYSDATE,USER,DELETE,:OLD.EMPLOYEE_ID,:NEW.EMPLOYEE_ID);
      8   ELSIF UPDATING THEN
      9   INSERT INTO AUDIT_EMP (MDATE,MODIFIED_BY,OPERATION,OLD_VALUE,NEW_VALUE) VALUES (SYSDATE,USER,UPDATE,:OLD.EMPLOYEE_ID,:NEW.EMPLOYEE_ID);
    10   END IF;
    11   END AUDIT_ARPU;
    12  /

    Warning: TRIGGER created WITH compilation errors.

    SQL>
    SQL> SHOW errors TRIGGER audit_arpu
    Errors FOR TRIGGER AUDIT_ARPU:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/2      PL/SQL: SQL Statement ignored
    3/95     PL/SQL: ORA-00936: missing expression
    5/2      PL/SQL: SQL Statement ignored
    5/95     PL/SQL: ORA-00936: missing expression
    7/2      PL/SQL: SQL Statement ignored
    7/95     PL/SQL: ORA-00936: missing expression
    SQL>
    You need to use the version he posted some time ago and you should be free of errors.
     
    Sadik likes this.
  11. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thanks Matthew and Zargon, it was working fine after making the required changes :)