Re: Enforce business rule with before insert trigger?

[riccardo bongiovanni]

SOrry I forgot the end of 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;

if(@insert_failed is not null) then
-- if there was at least an error then cancelling
-- the primary key value will cause stop
-- of execution of the query.
set new.tipo_rec := null;
end if;
end//
-----------------------------