-
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.
-
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...
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.
-
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"
-
@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
-
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
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.
-
Originally Posted by PAVB
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.
-
Originally Posted by GuinnM07
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|