Simple oracle Trigger works but gives me errors

Question

I have this simple trigger, it work but it gives me a few errors. the syntax seems right.

This is my code:

create or replace 
TRIGGER max_raise
BEFORE UPDATE ON empcopy
FOR EACH ROW
DECLARE
v_max NUMBER := 3000;
BEGIN
 IF :new.sal > v_max THEN
  raise_application_error(-20002, 'Cannot exceed max value of ' || v_max);
 END IF;
END;

And this is my errors:

DECLARE

*

ERROR at line 1:

ORA-20002: Cannot exceed max value of 3000

ORA-06512: at "SYSTEM.MAX_RAISE", line 5

ORA-04088: error during execution of trigger 'SYSTEM.MAX_RAISE'

ORA-06512: at line 11

This is this the pl/sql code that i am using in conjunction.

DECLARE
  v_raise   NUMBER;
  CURSOR cur1 IS
   SELECT a.deptno, a.sal, a.empno, a.ename 
   FROM empcopy a;

BEGIN
  FOR emp IN cur1 LOOP  

    v_raise := external_raise(emp.deptno, emp.sal);

    DBMS_OUTPUT.PUT_LINE('The Employee: ' || emp.ename || ' new salary: ' 
    || v_raise || ' the previous one was: ' || emp.sal);

    UPDATE empcopy SET sal = v_raise WHERE empno = emp.empno;
      END LOOP;  
END;

I'm not a plsql expert but isn't your code throwing the error? — Kevin Bowersox
It's your exception being raised. Catch it and print a message in the calling block. Or wrap the update statement with a begin exception block if you want to continue on through other records. — Glenn
Do you really want to be checking that the new salary isn't greater than 3000; or the the amount of the raise (i.e. :new.sal - :old.sal) isn't? Also, you really shouldn't be creating your own objects in the SYSTEM schema, you should create a new user and do all of this as that user, if you don't want to pollute the existing (HR?) schema. — Alex Poole

Rachcha Rachcha · Accepted Answer · 2013-12-12T01:32:49

The problem in your code is not your trigger but the business logic behind it. As I can see , from your question, all your code is working fine as per the expectations. The problem here is that you don't know what the expectations are.

It is a simple case of an advanced audit enforced using triggers. What your business analyst or the coder who worked before you on this code thought is that the salary of an employee must not exceed a certain amount. They wanted to make sure that no one enters a wrong value even by skipping the front end and querying the database directly. Hence this trigger has been programmed.

What you can do now is modify your front-end to show this exception to the user. Or, talk to your business analyst and if he thinks you do not need to force this logic, you can simply drop the trigger.

Simple oracle Trigger works but gives me errors

1 Answers