General
cancel
Showing results for 
Search instead for 
Did you mean: 

Avoid the execution of an insert from an trigger

Raquel Gómez Somarriba
Occasional Contributor

Avoid the execution of an insert from an trigger

I'm writing a trigger that checks the values of same columns, before the insert.

If the values are wrong I don't want the insert to be executed.

How can I do?

Thanks in advance.

Raquel
6 REPLIES
Justo Exposito
Esteemed Contributor

Re: Avoid the execution of an insert from an trigger

Hi Raquel,

How about a rollback command?

Regards,

Justo.
Help is a Beatiful word
Christian Gebhardt
Honored Contributor

Re: Avoid the execution of an insert from an trigger

This is not a typical question for a HPUX-Forum ;-)

Do you searching something like this:

CREATE OR REPLACE TRIGGER
AFTER INSERT ON
FOR EACH ROW
BEGIN
IF THEN

END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (???error???);
END;
/

Chris
Christian Gebhardt
Honored Contributor

Re: Avoid the execution of an insert from an trigger

Excuse me
Please replace AFTER INSERT with BEFORE INSERT

Chris
Christian Gebhardt
Honored Contributor

Re: Avoid the execution of an insert from an trigger

Better way:

CREATE OR REPLACE TRIGGER
BEFORE INSERT on ON
FOR EACH ROW
BEGIN
IF THEN
:NEW. := :OLD.;
END IF;
END;
/

This means that if condition is wrong the new inserted value is set to the old value, so the value of the field is unchanged.

Chris
Brian Crabtree
Honored Contributor

Re: Avoid the execution of an insert from an trigger

Possibly the easiest way that I have found to do it, is to avoid triggers entirely. You can pass the information into a procedure just as easily, and process the information there, which saves you alot of energy in dealing with triggers.

Otherwise, please post a better idea of what you are looking to do, and we might be able to give you a better idea of a way to do it.

Brian
F. X. de Montgolfier
Valued Contributor

Re: Avoid the execution of an insert from an trigger

I assume you're using Oracle V>=7...
Have you tried using WHEN ?

CREATE OR REPLACE TRIGGER
BEFORE INSERT on ON
FOR EACH ROW
WHEN ()
BEGIN
[treatment]
END;

If is satisfied, the trigger will be executed, otherwise it won't be...

Cheers,

Fran??ois-Xavier