Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Red face Unanswered: set field = current date

    I am creating the following trigger. I want to set the last 4 fields to current date and current time. how do i do this?

    create trigger ttdpur947
    insert on ttdpur045101
    referencing new as new
    for each row
    (insert into ttdpur947101 values
    (new.t_orno,
    new.t_pono,
    new.t_srnb,
    new.t_reno,
    new.t_dqua,
    new.t_quap,
    new.t_quad,
    t_crdt,
    t_crtm,
    t_chdt,
    t_chtm ))

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Current Date and Current Time

    I am not sure about the procedures and functions, but I will try to answer your query to the best of my knowledge.
    use to_char(SYSDATE,'YYYY/MM/DD') and to_char(SYSDATE,'HH:MIS')
    as the parameters.

    Let me know if it helps.
    Thanx and Regards
    Aruneesh

  3. #3
    Join Date
    Jul 2003
    Posts
    4

    Question

    Not sure what you mean.........

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Corrections

    Hi
    If you want to get the current date and time, the values i mentioned earlier could be used.

    Like you have the following fields (t_crdt, t_crtm, t_chdt and t_chtm).
    Just use the my values in place of the variables.

    If that doesnt explain what I am talking about, could be a more specific as to what u r trying to accomplish.

    Thanx and Regards
    Aruneesh

  5. #5
    Join Date
    Jul 2003
    Posts
    4
    It gives me a syntax error

    new.t_quad,
    to_char(SYSDATE,'YYYY/MM/DD')
    t_crtm,
    t_chdt,
    t_chtm ))

    201: A syntax error has occurred.


    I have a table already defined with these fields in it to be used elsewhere. I want them to default to the creation date and time that the record is inserted into my table.

  6. #6
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    More questions

    I have not work on procedures, but I would try my best to get the thing resolved for you.
    First could you let me know the datatype for the following fields. Are they all DATE fields or varchar2.

    t_crdt, t_crtm, t_chdt, t_chtm

    If they are varchar2, then the solution i provided would work fine, else just use SYSDATE, at every step.
    That would surely work for you.

    Thanx and Regards
    Aruneesh

  7. #7
    Join Date
    Jul 2003
    Posts
    4
    thank you for your suggestions, I have written a lot of queries but we don't normally write triggers or updates.

    they are defined as data and integer.

    I just found the function TODAY that works for the date.

    still looking for something to handle the time.

    CURRENT gives date and time....

  8. #8
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    CURRENT

    Hi
    With my experience with queries, dont have stuff like to_char and to_date in case of triggers. I am pretty sure that there would be stuff like that to filter out the required information.

    Do let me know if it gets you anywhere.
    Thanx and Regards
    Aruneesh

  9. #9
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Try GETDATE()... It'll return the current date and time... now, this works in MS SQL Server... not sure what platform you're on though

    SELECT GETDATE() AS datetime

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •