SQL & PL/SQL

Hi - in application, we have a concept called integrator and promoter. INT and PROMTR can't be the same person. I have written a small trigger to do this but although it's firing the update's still happening.

The table's H_RULE, where LASTCHECKER column records the promoter and LASTMAKER column records INTEGRATOR. These 2 columns can't have the same value. When there's an attempt to update the LASTCHECKER column and it happens to match the LASTMAKER column value, the update SHOULD NOT HAPPEN.
However, note that the calling program (Java) should not rollback the transaction, but just exit gracefully.

CREATE OR REPLACE TRIGGER TRGPROTECTMAKER 
BEFORE UPDATE OF LASTCHECKER
ON H_RULE 
FOR EACH ROW
DECLARE
       doNothing EXCEPTION ;
BEGIN
         IF (:new.LASTCHECKER = :old.LASTMAKER )
         THEN
              RAISE doNothing ;            
         END IF ;
EXCEPTION
         WHEN doNothing
		 THEN
		 	 dbms_output.put_line('The same maker is trying to act as the checker' 
                                     || DBMS_Utility.Format_Error_Stack) ;
            
		 WHEN OTHERS THEN
		 	  RAISE_APPLICATION_ERROR (-20001, 'Unexpected error: '
                               || DBMS_Utility.Format_Error_Stack);		 
END;

Edited by: baivab on Mar 28, 2012 2:35 PM

Edited by: baivab on Mar 28, 2012 2:36 PM