Trigger to Concatenate

[GuinnM07]

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.

[stecal]

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?

[PAVB]

I think problems go beyond a trigger issue... look...

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?

[GuinnM07]

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"

[GuinnM07]

@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

[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

to_string()? pl/sql?

[GuinnM07]

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.

[PAVB]

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?

[GuinnM07]

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.