Results 1 to 2 of 2
  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
    Aug 2001
    Posts
    66

    Re: Trigger fails to execute

    At a glance I would say you need to parenthesize the expression (:old.salary - :new.salary) otherwise PL/SQL will perform the string concatenation first and try to subtract :new.salary from the result, which would of course be a string.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

Posting Permissions

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