DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Trigger to Concatenate

  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Trigger to Concatenate

    Hello, I'm having trouble with one of my triggers. It's supposed to add the date to the beginning of an entry before it is inserted into the database.

    My table name is UserBlog with columns blogEntryID(INTEGER), username(VARCHAR2(20)), blogEntryDate(DATE), blogEntryTitle(VARCHAR2(50)), blogEntry(VARCHAR2(500)).

    Here's what hasn't worked:

    CREATE OR REPLACE TRIGGER bfInsert_userblog
    BEFORE INSERT on UserBlog
    FOR EACH ROW
    BEGIN
    --sequence set up to auto-increment primary key
    SELECT seq_blogEntryID.NEXTVAL into :NEW.blogEntryID from DUAL;

    --append date to beginning of user's entry
    :NEW.blogEntry := to_char(sysdate, "Mon. DD, YYYY: ") || :NEW.blogEntry;

    END;
    /


    I also tried
    :NEW.blogEntry := CONCAT(to_char(sysdate, "Mon. DD, YYYY: "), :NEW.blogEntry);

    ....amongst other things like declaring the to_char(date) into a variable and likewise with :NEW.blogEntry but I keep getting compilation errors.

    This is a homework assignment so it has to be done via a before insert trigger so please keep solutions within that frame please. Thanks.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    You don't need a trigger for this, and triggers should be far and away the last resort of how to do something. Are you being told to write a trigger, or is this a self-inflicted exercise in misery?

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I think problems go beyond a trigger issue... look...

    Code:
    SQL> -- This is your code... 
    SQL> 
    SQL> select to_char(sysdate, "Mon. DD, YYYY: ") from dual;
    select to_char(sysdate, "Mon. DD, YYYY: ") from dual
                            *
    ERROR at line 1:
    ORA-00904: "Mon. DD, YYYY: ": invalid identifier
    
    SQL>
    SQL> -- How about this?
    SQL>
    SQL> select to_char(sysdate, 'Mon DD, YYYY') from dual;
    
    TO_CHAR(SYSD
    ------------
    Apr 23, 2010
    
    SQL>
    ... do you see the difference?
    Last edited by PAVB; 04-23-2010 at 07:20 PM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Apr 2010
    Posts
    5
    I'm being told to do this. The problem exactly is:

    3. Create a trigger before insertion on UserBlog that:
    a. Uses the seq_blogEntryID sequence to generate a primary key for the new row.
    b. Append the date to start of the blogEntry in the form "Apr. 20, 2010: Original Entry"

  5. #5
    Join Date
    Apr 2010
    Posts
    5
    @PAVB

    I had tested the to_string(...) statement before when i used

    :NEW.blogEntry := to_string(sysdate, "Mon. DD, YYYY: ");

    just to see if that was the problem and it worked just fine. It just added the date in that format to the blogEntry column

  6. #6
    Join Date
    Apr 2010
    Posts
    5
    Also if you have time, I'm having trouble with the other trigger I have to write. These things are a real pain...

    I have another table called NewsFeed with columns newsFeedID(INTEGER), feedMessage(VARCHAR2(100)).

    4. Create a trigger after insertion on UserBlog that:
    a. Inserts a row into NewsFeed using seq_newsFeedID to generate a primary key.
    b. The feedMessage should be the username then a day description followed by the blogEntryTitle. If the date is today or yesterday, the day description should be "Today" or "Yesterday". Otherwise it should be "X days ago". Examples:
    i. "Brad--Today--Blog Title"
    ii. "Brad--Yesterday--Blog Title"
    iii. "Brad--7 days ago--Blog Title"

    I have to go to work right now so I can't really work on it anymore tonight, so I would really appreciate any help! Thanks again.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by GuinnM07 View Post
    I had tested the to_string(...) statement before when i used
    :NEW.blogEntry := to_string(sysdate, "Mon. DD, YYYY: ");
    just to see if that was the problem and it worked just fine. It just added the date in that format to the blogEntry column
    to_string()? pl/sql?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Apr 2010
    Posts
    5
    Quote Originally Posted by PAVB View Post
    to_string()? pl/sql?
    sorry i meant to_char(...)

    as a response to your suggestion that it might be my to_char(sysdate,"Mon. DD, YYYY: ") format that might be causing problems. I tested a trigger that used that and it worked so it's not that.

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by GuinnM07 View Post
    as a response to your suggestion that it might be my to_char(sysdate,"Mon. DD, YYYY: ") format that might be causing problems. I tested a trigger that used that and it worked so it's not that.
    look harder, format mask in to_char(sysdate,"Mon. DD, YYYY: ") is syntactically invalid - it doesn't work.

    Would you mind in copy/pasting whole Oracle error stack during compilation?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width