Solved

INSERT_DATE

Posted on 2013-12-17
7
474 Views
Last Modified: 2013-12-17
Hi,
I have a trigger as follow:
BEGIN
    INSERT INTO BCP_ENTRY (BCP_ENTRY_UNIQUE_NO, BCP_ENTRY_SAD_HEADER_ID, SAD_REPL_COU) VALUES ( BCP_ENTRY_SEQ.nextval, :new.SAD_ID, :new.SAD_REPL_COU);
END;

In the table BCP_ENTRY I need to update field INSERT_DATE with timestamp (when inserting was performed)

BEGIN
    INSERT INTO BCP_ENTRY (BCP_ENTRY_UNIQUE_NO, BCP_ENTRY_SAD_HEADER_ID, SAD_REPL_COU, INSERT_DATE) VALUES ( BCP_ENTRY_SEQ.nextval, :new.SAD_ID, :new.SAD_REPL_COU, :new.INSERT_DATE :=TIMESTAMP);
END;

I'm receiving error...
Thank you
0
Comment
Question by:andrejaTJ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Assisted Solution

by:Surrano
Surrano earned 50 total points
ID: 39723856
Can you please elaborate on what error you receive?
What is the exact trigger definition and table definition?
Have you tried like this?

BEGIN
    new.INSERT_DATE := TIMESTAMP;
    INSERT INTO BCP_ENTRY (BCP_ENTRY_UNIQUE_NO, BCP_ENTRY_SAD_HEADER_ID, SAD_REPL_COU, INSERT_DATE) 
    VALUES ( BCP_ENTRY_SEQ.nextval, :new.SAD_ID, :new.SAD_REPL_COU, :new.INSERT_DATE);
END;

Open in new window


or simply

BEGIN
    INSERT INTO BCP_ENTRY (BCP_ENTRY_UNIQUE_NO, BCP_ENTRY_SAD_HEADER_ID, SAD_REPL_COU, INSERT_DATE) 
    VALUES ( BCP_ENTRY_SEQ.nextval, :new.SAD_ID, :new.SAD_REPL_COU, TIMESTAMP);
END;

Open in new window

0
 

Author Comment

by:andrejaTJ
ID: 39723865
Thank you for your reply, but I'm getting error ORA-24344: success with compilation error :(
0
 
LVL 35

Accepted Solution

by:
johnsone earned 100 total points
ID: 39723874
What are you trying to put into the INSERT_DATE field?  If you are looking for the current time, then it would be:

BEGIN
    INSERT INTO BCP_ENTRY (BCP_ENTRY_UNIQUE_NO, BCP_ENTRY_SAD_HEADER_ID, SAD_REPL_COU, INSERT_DATE) VALUES ( BCP_ENTRY_SEQ.nextval, :new.SAD_ID, :new.SAD_REPL_COU, SYSTIMESTAMP);
END;

The full code of the trigger and what you are trying to accomplish would be a lot more helpful than one line.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:andrejaTJ
ID: 39723895
Johnsone, Surrano,
This trigger is working without problem:

create or replace
TRIGGER "TADEC_AF"."SAD_HEADER_TRIGGER" AFTER INSERT ON "TADEC_AF"."SAD_HEADER" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW BEGIN
    INSERT INTO BCP_ENTRY (BCP_ENTRY_UNIQUE_NO, BCP_ENTRY_SAD_HEADER_ID, SAD_REPL_COU)
    VALUES ( BCP_ENTRY_SEQ.nextval, :new.SAD_ID, :new.SAD_REPL_COU);
END;

When I'm trying to add SYSDATE to the fiels INSERT_DATE... Error.
Simply, I have DATE field (7) in the table BCP_ENTRY where is required to add TIMESTAMP or SYSDATE.... Like now()
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 100 total points
ID: 39723912
Then what I posted should work.  Have you tried it?  That would put the current timestamp in there.  Oracle should automatically convert it to DATE if that is the data type of INSERT_DATE (or you can change SYSTIMESTAMP to SYSDATE.
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39724021
what an oversight indeed, I should've noticed it was TIMESTAMP instead of SYSTIMESTAMP ;)
0
 

Author Comment

by:andrejaTJ
ID: 39724036
Thank you both... All the best!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question