INSERT_DATE

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
andrejaTJAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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
 
SurranoConnect With a Mentor System EngineerCommented:
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
 
andrejaTJAuthor Commented:
Thank you for your reply, but I'm getting error ORA-24344: success with compilation error :(
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
andrejaTJAuthor Commented:
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
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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
 
SurranoSystem EngineerCommented:
what an oversight indeed, I should've noticed it was TIMESTAMP instead of SYSTIMESTAMP ;)
0
 
andrejaTJAuthor Commented:
Thank you both... All the best!
0
All Courses

From novice to tech pro — start learning today.