error handling inside triggers
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 posted this at 01:35 — 1st November 2000.
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.