error handling inside triggers

They have: 5,633 posts

Joined: Jan 1970

Hello.
I'm trying to use a "raise_application_error" inside a trigger to halt all execution and return an error. It's not working though, here is what i got:

CREATE OR REPLACE TRIGGER iva_trigger
BEFORE
INSERT OR UPDATE OF cat_iva,descrição,imposto ON Iva FOR EACH ROW

DECLARE

...
...
IF (cat_aux=:new.cat_iva) THEN
RAISE categoria_existente;
END IF;
EXCEPTION
WHEN categoria_existente THEN
RAISE_APPLICATION_ERROR(-20001,'....');

This trigger is being called by a function i defined in a package, just before an INSERT command. The problem is, instead of halting execution, it returns to the aforementioned function, and tries to execute the INSERT command.
Obviously, i don't want to execute this INSERT, since it will violate a PRIMARY KEY constraint.
Why isn't this EXCEPTION working?
How do i do it?

Thanks all!

Peter J. Boettcher's picture

They have: 812 posts

Joined: Feb 2000

King_Elves,

I can't really help you with this one since it looks like you're using Oracle, Microsoft SQL Server handles triggers a little different than Oracle.

Your code structure looks sound, maybe it's a problem with data type? (NULL's?)

Sorry I couldn't help you more, maybe someone with some Oracle experience can pipe in? (hint, hint)

Regards,
Peter J. Boettcher

PJ | Are we there yet?
pjboettcher.com

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.