CODEWITHSUNDEEP
sqloracle-databaseplsql
Daniel Sega
Daniel Sega

Reputation: 333

Simple oracle Trigger works but gives me errors

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;

Upvotes: 0

Views: 128

Answers (1)

Rachcha
Rachcha

Reputation: 8816

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.

Upvotes: 2

Related Questions