database - How to raise an Exception inside a Trigger? Is there a way to do this?

Question

There's a situation like: If the Salary column in updated with a value lesser than it's original value, print an error message and let the update NOT happen. This is what I've written so far:

CREATE OR REPLACE TRIGGER TRIG1
BEFORE UPDATE OF SAL ON EMP
for each row
  USER_XCEP EXCEPTION
  WHEN (NEW.SAL<OLD.SAL)
BEGIN
  RAISE USER_XCEP

EXCEPTION
  WHEN USER_XCEP THEN
       DBMS_OUTPUT.PUT_LINE('UPDATION NOT ALLOWED - ILLEGAL VALUES');
END;

And I get the error - Incorrect Trigger Specification

Is there any other way to achieve this?

Answer 1

You're almost there; you need a DECLARE block in a trigger if you want to declare anything; this means that your WHEN clause is in the wrong place.

create or replace trigger trig1
 before update
 of sal
 on emp
 for each row
 when (new.sal < old.sal)

declare    
   user_xcep EXCEPTION;
   PRAGMA EXCEPTION_INIT( user_xcep, -20001 );
begin
   raise user_xcep;
end;

SQL Fiddle

A few points:

1. Never catch an exception and then call DBMS_OUTPUT.PUT_LINE; it's pointless. Someone has to be there to view the result for each and every record. If you don't want something to happen raise the exception and then catch it. I've added an error code to your exception so that you can catch this outside the trigger and handle it how you wish (don't print anything to stdout).
2. It's a minor point but I've added a little whitespace; not much. I couldn't initially see where the problem was with your code because you didn't have any.
3. You were missing semi-colons after the exception declaration and RAISE.

Read more about internally defined exceptions in the documentation