Abort query in before update / insert trigger

[jdbolt]

I was wondering if there was a way to abort a query in a before /update/insert query.


For example, the user executes the query, the trigger is run, decides whether to abort the query or not based on some logic. For exmaple:

CREATE OR REPLACE TRIGGER trigger_check_parent_relationship
BEFORE INSERT OR UPDATE ON modifier

DECLARE
invalidStatement BOOLEAN;

BEGIN

invalidStatement = TRUE;

IF invalidRelationship THEN

ABORT QUERY HERE

END IF;


END;

 

[bourrelo]

If I understand you correctly...you could try raising an error which will in turn end the process:

Example:

IF InvalidRelationship THEN
RAISE Error_message
END IF;

EXCEPTION
WHEN Error_message THEN
Raise_application_error(-20001, 'Insert message');

END;

 

[Turkbear]

Hi,
Or you could let Oracle handle it by defining the Foreign Key > Primary key relationships in the database ...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jdbolt]

Cool thanks I will give it a go. It is for enforcing a buiseness rule rather than referential integrity so a foreign key won't really work.


Its a self-referencing table, and I need to make sure that when a row is inserted or updated, that the foreign key is not one of its children. This should help me though, thanks.

 

[jdbolt]

BTW, I just read your replies a bit more carefully, I want to raise an exception or some way to stop the wuery even if there technically nothing wrong (its a business rule that was been violated not a logical or data one).



For example invalidRelation is something I set to true or false based on the results of a select query, is there no actual way to say



ABORT

or

EXIT

or DIE

???

 

[Beilstwh]

Sure,
use something like the following. When the RAISE_APPLICATION_ERROR is called, the update or insert will be stopped and the error message "ORA-20001 You have violated the business rule" will be returned. If the business rule is not violated, the trigger ends normally and the insert or update completes. In the trigger, you reference the unmodified values (on update) using ld.column_name, the new value (on insert,update) using :new.column_name.

CREATE OR REPLACE TRIGGER modifier_t1
BEFORE INSERT OR UPDATE ON modifier
for each row

BEGIN
-- Perform your test for an invalid business rule below
if ..... then
Raise_application_error(-20001, 'You have violated the business rule');
end if;
END;

Bill
Oracle DBA/Developer
New York State, USA