Re: Trigger HELP

Hi Xavier thanks. You refreshed my memory to thinks I suppose to know. Thanks. I already have what am asking for.
Thanks again
Leonard
As water reflects a face so do a man's heart reflects the man 



From: "[email protected]" 
Reply-To: "Oracle" 
To: "Oracle" 
Subject: [oracle] Re: Re: Trigger HELP 
Date: Thu 24 Apr 2003 14:56:36 +0200 

I think the whole solution has been given several times in the past replies. So let me try to clarify it. 

:new.date_created := to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'); 

1) A date in oracle is always a date with a time. 
2) if you use to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') (format mask does not matter) you convert the date/time in a string with the given format mask. 
3) if you assign this string to a date - oracle will implicitly try to convert this string to date. If an error occurs here it will be a run-time error because it is no syntax error (the string may be good). For convertion of the string to a date oracle uses the default date notation (associated with you database settings). 

Solutions : 
1) Make the date_created a string where you can put the date/time in. 
:new.date_created := to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'); 
or 
2) date_created stays a date (remember-datetime) but in all cases you try to display it you use the to_char(); 
:new.date_created := sysdate; 

select to_char(date_created,'dd/mm/yyyy hh24:mi:ss') from [table] .. 

hope this helps 

X 

------------------------ 
"Oracle" wrote: 
------------------------ 

>you are right, but getting to insert like this by the trigger is not happening to_char(mydate,'dd mon yyyy hh24:mi'), because the trigger will accept it in this form 
>:new.date_created := to_char(sysdate. 'dd mon yyyy hh24:mi); 
>Trigger created. But the procedure fails when you input the form. 
>As water reflects a face so do a man's heart reflects the man 
> 
> 
> 
>From: [email protected] 
>Reply-To: "Oracle" 
>To: "Oracle" 
>Subject: [oracle] Re: Trigger HELP 
>Date: Thu, 24 Apr 2003 10:19:30 +0100 
> 
> 
>Inserting sysdate into a date field will automatically insert the time too. 
> 
>create table mydates ( mydate date ) 
>/ 
>insert into mydates values ( sysdate ) 
>/ 
> 
>Just selecting the mydate will produce the following result ( depending on 
>the default date format of your instance ) 
> 
>select mydate from mydates 
>/ 
>24-APR-2003 
> 
>But if you explicitly define the required format on your select you can get 
>this 
> 
>select to_char(mydate,'dd mon yyyy hh24:mi') from mydates 
>/ 
>24 apr 2003 10:15 
> 
> 
> 
> 
>Regards 
>Chris Farmer 
> 
> 
> 
> 
>"Leonard Anukam" 
> 
>tmail.com> cc: 
>Subject: [oracle] Re: Trigger HELP 
>24/04/2003 10:04 
>Please respond to 
>"Oracle" 
> 
> 
> 
> 
> 
> 
> 
>Yes it works like this, but want it  like to_char(sysdate, 'DD-MON-YYYY 
>HH24:MI:SS') 
>This also compiles, but fails to connect to the procedure page that has the 
>link to the website, when i try to access the page. 
>but when I revise to :new.date_created:=sysdate; the it connects again to 
>the procedure page and inserts into the database. 
>HERE IS MY PROCEDURE 
>CREATE OR REPLACE PROCEDURE contactus(  p_name varchar2 DEFAULT NULL, 
>p_email varchar2 DEFAULT NULL) AS  personal_infor varchar2(1000); BEGINif 
>p_name IS NOT NULL and p_email IS NOT NULL and p_email LIKE '%@%.%' and 
>length(p_email)>5then 
> 
>     insert into visitors(name, email)     values(p_name, p_email); 
>  htp.prn(' 
>Hello ' || p_name ||''|| ' Your email address is ' || 
>p_email || '.' ||''||' You visited today ' || to_char(sysdate, 
>'DD-MON-YYYY HH24:MI:SS') ||''|| 'Click here to Continue'); 
> 
>RETURN;END IF; 
>if (p_name IS NULL and p_email IS NOT NULL)      OR (p_name IS NOT NULL and 
>p_email IS NULL )            OR (p_email NOT LIKE '%@%.%' and 
>length(p_email) color="red">*Required 
>               
> 
> 
>Enter Your Email Address: 
>                
> 
>color="red">*Required'); 
> 
> 
> 
>   htp.formSubmit(NULL, 'Submit');  htp.formClose;END; 
>put i will like to see also the time of the visit by the visitor. 
> 
>As water reflects a face so do a man's heart reflects the man 
> 
> 
> 
>From: [email protected] 
>Reply-To: "Oracle" 
>To: "Oracle" 
>Subject: [oracle] Re: Trigger HELP 
>Date: Thu, 24 Apr 2003 09:44:39 +0100 
> 
> 
>If you have defined date_created as a date then you should be able to set 
> 
>:new.date_created = sysdate; 
> 
>Regards 
>Chris Farmer 
> 
> 
> 
> 
>"leonard Anukam" 
> 
>tmail.com> cc: 
>Subject: [oracle] Trigger HELP 
>23/04/2003 20:40 
>Please respond to 
>"Oracle" 
> 
> 
> 
> 
> 
> 
>I have a table in which I will want a date and time to be stored when a 
>visitor visits my site and fills the form. I have a trigger that 
>automatically inserts the date, but the time. 
>http://www.netikka.net/leo 
>Here is my code 
> 
>create or replace trigger visitor_date_created 
>before insert on visitors 
>for each row 
>begin 
>:new.date_created := to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'); 
>end; 
> 
>But this returns an error. 
> 
>Please give you solution 
> 
>thanks 
> 
>Leonard 
> 
>--- 
>Change your mail options at http://p2p.wrox.com/manager.asp or 
>to unsubscribe send a blank email to %%email.unsub%%. 
> 
> 
> 
> 
> 
> 
>--- 
>Change your mail options at http://p2p.wrox.com/manager.asp or 
>to unsubscribe send a blank email to %%email.unsub%%. 
>STOP MORE SPAM with the new MSN 8 and get 2 months FREE* 
> 
>--- 
>Change your mail options at http://p2p.wrox.com/manager.asp or 
>to unsubscribe send a blank email to %%email.unsub%%. 
> 
> 
> 
> 
> 
> 
>--- 
>Change your mail options at http://p2p.wrox.com/manager.asp or 
>to unsubscribe send a blank email to %%email.unsub%%. 
>Add photos to your messages with MSN 8. Get 2 months FREE*. 
> 
>--- 
>Change your mail options at http://p2p.wrox.com/manager.asp or 
>to unsubscribe send a blank email to %%email.unsub%%. 
> 



--- 
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%. 
MSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*.

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].