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
--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;
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.
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?
I think problems go beyond a trigger issue... look...
... do you see the difference?
SQL> -- This is your code...
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> -- How about this?
SQL> select to_char(sysdate, 'Mon DD, YYYY') from dual;
Apr 23, 2010
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"
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
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.
Originally Posted by GuinnM07
sorry i meant to_char(...)
Originally Posted by 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.
Originally Posted by GuinnM07
Would you mind in copy/pasting whole Oracle error stack during compilation?