Avoid the execution of an insert from an trigger

[Raquel Gómez Somarriba]

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

[Justo Exposito]

Hi Raquel,

How about a rollback command?

Regards,

Justo.

[Christian Gebhardt]

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]

Excuse me
Please replace AFTER INSERT with BEFORE INSERT

Chris

[Christian Gebhardt]

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]

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]

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