How to raise an exception inside a trigger? Is there any way to do this?

Question

This situation arises: if the "Salary" column is updated with a value less than its original value, print an error message and do NOT inform about it. This is what I wrote 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 something; this means that the 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:

• Never catch the exception, and then call DBMS_OUTPUT.PUT_LINE; it's pointless. Someone must be there to view the result for each entry. If you don’t want something to happen, raise an exception and then catch it. I have added an error code to your exception so that you can catch it outside the trigger and handle it the way you want (do not print anything in stdout).
• This is a minor point, but I have added a few spaces; Little. At first I could not understand where the problem is with your code, because you did not have it.
• You didn’t have enough half-columns after declaring an exception and RAISE.

Learn more about internally defined exceptions in the documentation.

Answer 2

This is for Oracle, apparently. It would be nice to have Oracle in the title so that it doesn't appear on every search.