Home » Developer & Programmer » Forms » Want to save create and last updated date and time in a table
Want to save create and last updated date and time in a table [message #627714] Fri, 14 November 2014 23:41 Go to next message
tara_260985
Messages: 46
Registered: November 2014
Member
My block has two columns companyid and xrefnumber with a add and delete button ,when I press the add button to add a new record in the block and then press OK button to commit the changes done, functionality is done in the form, I want to save the createdate and last_updated_datetime in the database,my table consists of createdate and last_updated_datetime columns,but it doesnt save any datetime.
Re: Want to save create and last updated date and time in a table [message #627733 is a reply to message #627714] Sat, 15 November 2014 08:29 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
These two columns won't be populated automatically (just because you'd want them to). You have to instruct Oracle to do it. How? Either include items into the data block and set their value in the PRE-INSERT and PRE-UPDATE triggers, or create a database trigger which will take care about it.
Re: Want to save create and last updated date and time in a table [message #627739 is a reply to message #627714] Sat, 15 November 2014 09:16 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
are you trying to create delete history if yes thn history table will be different or you simply want to save the current date & time.

if you want to keep the delete history thn Trigger will be

BLOCK-LEVEL-->
       -PRE-DELETE

>>>and some stuff HERE 

------------------------Save current datetime example----

Create table xyz(udatetime date);

BLOCK-LEVEL
  >>>>>PRE-INSERT 

Insert into xyz (udatetime) select Sysdate from dual;

SQL>  Select to_Char(udatetime,'DD-MM-YYYY HH24:MI:SS') from xyz;

TO_CHAR(UDATETIME,'
-------------------

15-11-2014 07:30:55

15-11-2014 07:31:23

or 

:udatetime:= to_date(to_char(SYSDATE,'hh24:mi:ss'),'hh24:mi:ss');




may be your scenario could be different in that case let me know. I given you just example.

Hope i will help you.

[Updated on: Sat, 15 November 2014 09:33]

Report message to a moderator

Re: Want to save create and last updated date and time in a table [message #627753 is a reply to message #627739] Sat, 15 November 2014 12:10 Go to previous messageGo to next message
tara_260985
Messages: 46
Registered: November 2014
Member
Thank you for the your reply.
I have multiple blocks in my form.
but for one specific block I need to update date and time.That table has 5 columns in total along with createdatetime and last_update datetime columns...so if there is an add/edit happening in col1 and col2...createdatetime and last_update datetime should be recorded..
basically its for auditing purpose

My database table my_table already consists of these two columns ,but I don't want to display in the layout editor.
whenever any add or edit happens in the block through add push button ,I want to save the createdatetime and last_update_datetime in my database table xyz
I am new to oracle forms ,can you please help me which trigger should fire?
Re: Want to save create and last updated date and time in a table [message #627754 is a reply to message #627733] Sat, 15 November 2014 12:12 Go to previous messageGo to next message
tara_260985
Messages: 46
Registered: November 2014
Member
its for one of the specific block..can you please help me with database block trigger
Re: Want to save create and last updated date and time in a table [message #627759 is a reply to message #627754] Sat, 15 November 2014 14:43 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
i got you r point whoever is editing the data last update data should be saved with date & time and also you can save username who edited the data.


you need.Try Smile giving you some idea

declare 
a number := 1 ;

begin

    if :SYSTEM.BLOCK_STATUS = 'CHANGED' THEN
       a:=a+1;
    loop---if multiple record
           Insert into xyz
EXIT WHEN :system.last_record='TRUE';
end loop ;
end;


Just try your self You need to do some more work if failed thn let me know its easy.

Regards
Mughal

[Updated on: Sat, 15 November 2014 15:10]

Report message to a moderator

Re: Want to save create and last updated date and time in a table [message #627764 is a reply to message #627739] Sat, 15 November 2014 16:08 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mughals_king

:udatetime:= to_date(to_char(SYSDATE,'hh24:mi:ss'),'hh24:mi:ss');


This is wrong. SYSDATE returns DATE datatype; there's no point in converting it to a string and then back to date.
Re: Want to save create and last updated date and time in a table [message #627778 is a reply to message #627764] Sun, 16 November 2014 06:10 Go to previous messageGo to next message
tara_260985
Messages: 46
Registered: November 2014
Member
from which trigger should I call this query??
Re: Want to save create and last updated date and time in a table [message #627781 is a reply to message #627778] Sun, 16 November 2014 09:02 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
Dear first show us atleast screen shots where you are willing to apply this this logic is there a tabular fields in block? atleast we should know what we have to do please take screen shots and upload even we don't have idea what we gonna do atleat I have no idea if someone has idea that i don't know.

[Updated on: Sun, 16 November 2014 09:06]

Report message to a moderator

Re: Want to save create and last updated date and time in a table [message #627785 is a reply to message #627781] Sun, 16 November 2014 16:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Littlefoot has already said which triggers to use
Re: Want to save create and last updated date and time in a table [message #627786 is a reply to message #627785] Sun, 16 November 2014 17:33 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
i don't think sir @littlefoot asked regarding trigger but you are also senior and important member of this forum and of course for me as well anyway as far as concern his requirement which i understand that he is willing to make a history_table so i worked on that therefore i have decided to create database trigger and i have successfully worked on that may be above code will not fulfilled his/her requirement i did not tested it but i had created one example .fmb and i used in that WHEN-BUTTON-PRESSED and coding was below and later i have changed my mind and decided to CREATE DATABASE TRIGGER and i have tested successfully.

---------------This was before-WHEN-BUTTON-PRESSED-----
SQL> desc owner
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ID                                                 VARCHAR2(5)
 NAME                                               VARCHAR2(25)
 TR_DATE                                            DATE
 TRANSFER_NO                                        VARCHAR2(25)

------------------------------------------------------------------
SQL> desc prev_own
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 ID                                                 VARCHAR2(5)
 NAME                                               VARCHAR2(25)
 REG_DATE                                           DATE
 TRANSFER_NO                                        VARCHAR2(25)

--------WHEN-BUTTON-PRESSED--------

DECLARE
   ABC VARCHAR2(25);
BEGIN
  IF :SYSTEM.BLOCK_STATUS = 'CHANGED' THEN
   SELECT NAME INTO ABC FROM OWNER WHERE ID = :ID;
   INSERT INTO PREV_OWN VALUES(:ID,ABC,:TR_DATE,:TRANSFER_NO);
  END IF;
  COMMIT;
END;


Re: Want to save create and last updated date and time in a table [message #627795 is a reply to message #627786] Mon, 17 November 2014 03:04 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's still highly unclear whether the OP wants to save this data in the table the block is based on or in a different one. Either way the code should go in pre-insert and pre-update, no buttons needed.
Re: Want to save create and last updated date and time in a table [message #627799 is a reply to message #627739] Mon, 17 November 2014 03:36 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
mughals_king wrote on Sat, 15 November 2014 09:16


Yes Sir you are right ! You can see my code which i posted on 15-NOV i used in pre-insert trigger which is successfully working but @tara_260985 requirement seems little different that's why i have had decided to make one database trigger.



------------------------Save current datetime example----

Create table xyz(udatetime date);

BLOCK-LEVEL
  >>>>>PRE-INSERT 

Insert into xyz (udatetime) select Sysdate from dual;

SQL>  Select to_Char(udatetime,'DD-MM-YYYY HH24:MI:SS') from xyz;

TO_CHAR(UDATETIME,'
-------------------

15-11-2014 07:30:55

15-11-2014 07:31:23

or 

:udatetime:= to_date(to_char(SYSDATE,'hh24:mi:ss'),'hh24:mi:ss');




may be your scenario could be different in that case let me know. I given you just example.

Hope i will help you.



Regards
Mughals
Re: Want to save create and last updated date and time in a table [message #627800 is a reply to message #627739] Mon, 17 November 2014 03:41 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
mughals_king wrote on Sat, 15 November 2014 09:16


Yes Sir you are right ! You can see my code which i posted on 15-NOV i used in pre-insert trigger which is successfully working but @tara_260985 requirement seems little different that's why i have had decided to make one database trigger. Trigger syntax something like that

------------------------Save current datetime example----
Create table xyz(udatetime date);

BLOCK-LEVEL
  >>>>>PRE-INSERT 

Insert into xyz (udatetime) select Sysdate from dual;

SQL>  Select to_Char(udatetime,'DD-MM-YYYY HH24:MI:SS') from xyz;

TO_CHAR(UDATETIME,'
-------------------

15-11-2014 07:30:55

15-11-2014 07:31:23

---------Syntax---Trigger Detail-------

CREATE [OR REPLACE ] TRIGGER trigger_name
  {BEFORE | AFTER | INSTEAD OF } 
  {INSERT [OR] | UPDATE [OR] | DELETE} 
  [OF col_name]  
  ON table_name
  [REFERENCING OLD AS o NEW AS n]
  [FOR EACH ROW]  WHEN (condition)  
 BEGIN 
   --- sql statements 
 END; 





Regards
Mughal

[Updated on: Mon, 17 November 2014 03:45]

Report message to a moderator

Re: Want to save create and last updated date and time in a table [message #627838 is a reply to message #627800] Mon, 17 November 2014 07:24 Go to previous messageGo to next message
tara_260985
Messages: 46
Registered: November 2014
Member
@cookiemonster..The block is based on the table and I want to save create_datetime and last_update_datetime in my database.I havent defined these two columns in the form layout.
Below is my table structure -

ITEM VARCHAR2 (25 Byte)
ITEM_XREF_NUM VARCHAR2 (25 Byte)
COMPANY_ID VARCHAR2 (6 Byte)
CREATE_DATETIME DATE
LAST_UPDATE_DATETIME DATE
SUPPLIER NUMBER (10)
Re: Want to save create and last updated date and time in a table [message #627839 is a reply to message #627753] Mon, 17 November 2014 07:34 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
tara_260985 wrote on Sat, 15 November 2014

My database table my_table already consists of these two columns ,but I don't want to display in the layout editor.


The simplest option is to include them into a form. You do NOT have to display them - there's the Item Property which handles that (set it to "no").
Re: Want to save create and last updated date and time in a table [message #627842 is a reply to message #627839] Mon, 17 November 2014 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And having done that, assign the appropriate values to them in pre-insert and pre-update.
Re: Want to save create and last updated date and time in a table [message #627844 is a reply to message #627842] Mon, 17 November 2014 07:56 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Certainly; now it's been said for the 3rd time, I hope tara_260985 will finally get the idea.
Re: Want to save create and last updated date and time in a table [message #627847 is a reply to message #627844] Mon, 17 November 2014 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I know I'm repeating both of us there but since most of the code samples in this thread suggest something else I thought it best to restate.
Re: Want to save create and last updated date and time in a table [message #628146 is a reply to message #627847] Thu, 20 November 2014 07:00 Go to previous message
tara_260985
Messages: 46
Registered: November 2014
Member
@Littlefoot :Thanks your resolution worked, I included those two columns in database but set its visible property to "No" and included insert statements in Pre-Insert block level trigger Smile))

Thanks to @Mughal king and @ Cookie monster!!!Smile
Thanks a lot for your valuable help !!!!!

[Updated on: Thu, 20 November 2014 07:01]

Report message to a moderator

Previous Topic: The runtime process has terminated abnormally.
Next Topic: Client Side Open a document type in Form Builder 10g
Goto Forum:
  


Current Time: Thu Mar 28 09:35:30 CDT 2024