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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/4cbaf579f1cfc1e10958211fb2912ec1?s=64&d=mm&r=g) |
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 #627739 is a reply to message #627714] |
Sat, 15 November 2014 09:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/1229663c86eb1a441385fe710cd9116e?s=64&d=mm&r=g) |
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 #627759 is a reply to message #627754] |
Sat, 15 November 2014 14:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/1229663c86eb1a441385fe710cd9116e?s=64&d=mm&r=g) |
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 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 #627800 is a reply to message #627739] |
Mon, 17 November 2014 03:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/1229663c86eb1a441385fe710cd9116e?s=64&d=mm&r=g) |
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
|
|
|
|
|
|
|
|
|