MySQL Forums
Forum List  »  Triggers

Re: Enforce business rule with before insert trigger?
Posted by: riccardo bongiovanni
Date: April 27, 2005 04:01AM

I had the same problem.
How can I stop processing of the sql query from inside a trigger when data validation fails?

I found this solution: when a validation fails I put the error message inside the environment variable called @insert_failed and then I erase value of primary fields in that query, so the primary constraint will raise the exception and will stop execution of query.
The error raised will be allways for Primary key null but application must read all error messages from environment variable @insert_failed.

I tried and it works fine ;-)

Sorry for my english.

this is my trigger:
--------------------------
create trigger TR_INS_Header before insert on HEADER for each row

begin

-- checking if 'doc_data' field contains a date in this format 'YYYYMMDD'...

-- variables declaration
declare day int;
declare month int;
declare year int;

-- initializing environment variable containing
-- possible error messages
set @insert_failed := "";


-- check date format of the 'doc_data' field...
set day := day(str_to_date(new.doc_data, '%Y%m%d'));
set month := month(str_to_date(new.doc_data, '%Y%m%d'));
set year := year(str_to_date(new.doc_data, '%Y%m%d'));

-- if date convertion is impossible...
if(day = null and day = 0) then
-- adding error message to environment variable...
set @insert_failed := concat(@insert_failed,
"Table Header -> field 'doc_data' ", new.doc_data,
" must contain a date in this format 'YYYYMMDD'\n");
else if(month > 12 or month < 1) then
-- adding error message to environment variable...
set @insert_failed := concat(@insert_failed,
"Table Header -> field 'doc_data' ", new.doc_data,
" must contain a date in this format 'YYYYMMDD'\n");
else if(year<1000 or year>9999) then
-- adding error message to environment variable...
set @insert_failed := concat(@insert_failed,
"Table Header -> field 'doc_data' ", new.doc_data,
" must contain a date in this format 'YYYYMMDD'\n");

end if;
end//
---------------------------------------
Riccardo Bongiovanni.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Enforce business rule with before insert trigger?
9477
April 27, 2005 04:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.