Re: Enforce business rule with before insert trigger?
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.