Re: How to write this function trigger in oracle 9i?

From: sxbug <sxbug.12l42b_at_mail.mcse.ms>
Date: Thu, 4 Mar 2004 01:20:41 -0600
Message-ID: <sxbug.12l42b@mail.mcse.ms>

I program a 3 tier application. Now I found users always wrote some illegal data into DateTime Field, and Oracle DB all accept them into DB(include NULL). But User Application encounter the Unkonow data ,it must be halt(Display invilid time ).
So I think I should make a trigger to check datetime Data. I write a trigger,but it is wrong. Please help me . Thanks!!

///----------------------

CREATE OR REPLACE TRIGGER "JAMIS"."T_INSERT_TASK_DATE" BEFORE INSERT ON "JAMIS"."TASK"
FOR EACH ROW
declare
myYear String;
myMonth String;
myDay String;
myHour String;
myMinte String;
mySec String;
BEGIN

myYear  = SubStr(:new.DATEOFTASKLAY from 1 for 4);
myMonth = SubStr(:new.DATEOFTASKLAY form 6 for 2);
myDay   = SubStr(:new.DATEOFTASKLAY form 9 for 2);
myHour  = SubStr(:new.DATEOFTASKLAY form 12 for 2); 
myMinte = SubStr(:new.DATEOFTASKLAY form 15 for 2); 
mySec   = SubStr(:new.DATEOFTASKLAY form 18 for 2);

if myYear<'1900' or myYear>'2999' then
myYear='2004';
if myMonth<'01' or myMonth>'12' then
myMonth='12';
if myDay<'01' or myDay>'31' then
myDay='01';

if (myHour='') or (myHour<'00' or myHour>'24') then myHour='00';
if (myMinte='') or (myMinte<'00' or myMinte>'60') then myMinte='00';
if (mySec='') or (mySec<'00' or mySec>'60') then mySec='00';

:New.DATEOFTASKLAY:=myYear+'-'+myMonth+'-'+myDay+' '+myHour+':'+myMinte+':'+mySec;

END;

--
sxbug


------------------------------------------------------------------------

Posted via http://www.mcse.ms


------------------------------------------------------------------------

View this thread: http://www.mcse.ms/message447603.html
 

Received on Thu Mar 04 2004 - 01:20:41 CST