MySQL Forums
Forum List  »  Triggers

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

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//
-----------------------------

Options: ReplyQuote


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


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.