Re: Re: Trigger HELP

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" <[email protected]> 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 protected].