Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    11

    Unanswered: Trigger fails to execute

    Hi,
    I have working on this for God alone knows how many days. Can anyone please help me with how to correct this. My triggers are getting created successfully. However, when I try to insert/update I get the following errors. Please help!! I have a deadline in 24 hours!!
    Thanks.

    CREATE OR REPLACE TRIGGER trig1
    BEFORE INSERT OR UPDATE OF hours ON Works_On
    FOR EACH ROW
    DECLARE
    total_hrs Number(6,2);
    lesshrs EXCEPTION;
    morehrs EXCEPTION;
    nullhrs EXCEPTION;
    BEGIN
    select sum(W.hours) into total_hrs
    from Works_On W
    group by W.essn;
    IF (total_hrs) = null THEN
    RAISE nullhrs;
    ELSIF (total_hrs < 25 ) THEN
    RAISE lesshrs;
    ELSIF (total_hrs > 45) THEN
    RAISE morehrs;
    ELSE
    dbms_output.put_line('Hours worked complies with company policy');
    END IF;
    EXCEPTION
    WHEN lesshrs THEN
    RAISE_APPLICATION_ERROR(-20001,'Total hours is less than 25 hours');
    WHEN morehrs THEN
    RAISE_APPLICATION_ERROR(-20002,'Total hours is more than 45 hours');
    WHEN nullhrs THEN
    RAISE_APPLICATION_ERROR(-20003,'Total hours is null');
    END workd_on;
    /

    ERROR at line 1:
    ORA-04098: trigger 'WORKD_ON1' is invalid and failed re-validation


    CREATE OR REPLACE TRIGGER trig2
    AFTER UPDATE OF salary ON Employee
    FOR EACH ROW
    DECLARE
    less_sal Number;
    more_sal Number
    BEGIN

    if(:new.salary < ld.salary) then
    dbms_output.put_line('old salary is: '|| ' ' || ld.salary);
    dbms_output.put_line('new salary is: '|| ' ' || :new.salary);
    dbms_output.put_line('difference in salary is: '|| ' ' || ld.salary- :new.salary);
    elsif(:new.salary > ld.salary) then
    dbms_output.put_line('old salary is: '|| ' ' || ld.salary);
    dbms_output.put_line('new salary is: '|| ' ' || :new.salary);
    dbms_output.put_line('difference in salary is: '|| ' ' || :new.salary- ld.salary);
    else
    dbms_output.put_line('No change in salary');
    end if;
    END sal;
    /
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "username.trig2", line 10
    ORA-04088: error during execution of trigger 'username.trig2'

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Trigger fails to execute

    Your first error message (ORA-04098: trigger 'WORKD_ON1' is invalid and failed re-validation) appears to have nothing to do with the preceding CREATE TRIGGER command. Use "SHOW ERRORS TRIGGER WORKD_ON1" to see what the problem is.

    This line in your first trigger will compile OK, but is wrong:

    IF (total_hrs) = null THEN

    It must be:

    IF total_hrs IS null THEN

    Also, it will not work as a FOR EACH ROW trigger because is references the trigger table ("mutating" table). However, as it doesn't use :NEW or :OLD you can remove the FOR EACH ROW anyway.

    Other than that the trigger seems OK, if a little verbose. It could be abbreviated to:

    Code:
    CREATE OR REPLACE TRIGGER trig1
    BEFORE INSERT OR UPDATE OF hours ON Works_On
    DECLARE
       total_hrs Number(6,2);
    BEGIN
       select sum(W.hours) into total_hrs
       from Works_On W
       group by W.essn;
       IF total_hrs IS null THEN
           RAISE_APPLICATION_ERROR(-20003,'Total hours is null');
       ELSIF (total_hrs < 25 ) THEN 
          RAISE_APPLICATION_ERROR(-20001,'Total hours is less than 25 hours');
       ELSIF (total_hrs > 45) THEN
           RAISE_APPLICATION_ERROR(-20002,'Total hours is more than 45 hours');
       END IF;
    END workd_on;
    /
    There is little point in having DBMS_OUTPUT messages in your triggers, other than as an aid to unit testing it. In a real application, users will never see the output anyway.

    The problem with your second trigger is here:

    dbms_output.put_line('difference in salary is: '|| ' ' || :old.salary- :new.salary);

    You need to use parentheses to get the concatenation and subtraction done in the right order:

    dbms_output.put_line('difference in salary is: '|| ' ' || (:old.salary- :new.salary));

    Ditto the other similar statement.

  3. #3
    Join Date
    Oct 2003
    Posts
    11
    SQL> SHOW ERRORS TRIGGER WORKD_ON1;
    Errors for TRIGGER WORKD_ON1:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/4 PLS-00103: Encountered the symbol "GROUP" when expecting one of
    the following:
    * & = - + < / > in mod not rem then an exponent (**)
    <> or != or ~= >= <= <> and or like between is null is not ||
    is dangling

    Hi,
    This is what it says. But the query does need a group by clause.
    What should I do?

  4. #4
    Join Date
    Oct 2003
    Posts
    11

    Desperate, please help!!

    Regarding the sal trigger, it updates the salary in the Employee table but doesnot show me the messages, ie, less salary, more salary and the difference.

    You were mentioning about dbms_output not being very helpful in triggers except when debugging. Can I not use it to display the old and new salaries after salary has been updated?

    Thanks

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Desperate, please help!!

    Well, you can get the messages displayed in SQL Plus by issuing the command SET SERVEROUT ON SIZE 1000000 before doing the updates.

    But in a real system, your users would not be typing UPDATE commands into SQL Plus would they? They would probably be updating fields on some kind of screen (web form or whatever) and pressing a Save button. In this case, the output from DBMS_OUTPUT.PUT_LINE will disappear unseen into the void. If you wanted to display such information messages, you would probably do this in the client code:

    1) select the row and save the current salary into a variable like v_old_sal

    2) allow the user to update salary field and press Save.

    3) perform the update

    4) (if update successful) compare the new salary with v_old_sal and issue approptiate message.

  6. #6
    Join Date
    Oct 2003
    Posts
    11

    Thumbs up

    Thanks very much.
    I put in the paranthesis and also tried it using set serverout on and also the varaiable v_old_sal.
    That helped.
    Thanks once again.

  7. #7
    Join Date
    Oct 2003
    Location
    Missouri, USA
    Posts
    18

    Question Re: Desperate, please help!!

    Originally posted by andrewst
    Well, you can get the messages displayed in SQL Plus by issuing the command SET SERVEROUT ON SIZE 1000000 before doing the updates.

    But in a real system, your users would not be typing UPDATE commands into SQL Plus would they? They would probably be updating fields on some kind of screen (web form or whatever) and pressing a Save button. In this case, the output from DBMS_OUTPUT.PUT_LINE will disappear unseen into the void. If you wanted to display such information messages, you would probably do this in the client code:

    1) select the row and save the current salary into a variable like v_old_sal

    2) allow the user to update salary field and press Save.

    3) perform the update

    4) (if update successful) compare the new salary with v_old_sal and issue approptiate message.

    This is questioning #4 above. How do you get the message to display? When I put:

    message('Something for the user's information');

    into a procedure I have written, the procedure will not compile. If I put:

    dbms_output.put_line ('Something for the user's info');

    the procedure will compile, but the users never see the message, as previously mentioned.

    How do I code and be able to compile the procedure with 'message'?
    Thanks, MH

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Desperate, please help!!

    Stored procedures run on the server, and they cannot display messages on the client at all. All they can do to interact with the client is pass back data via parameters and return values. So the stored procedure might have an OUT parameter to pass back the text string 'Something for the user'; it is then up to the client program to decide what to do with this, such as display an alert window with the text in it, or write the text onto a new web page and display it.

Posting Permissions

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