Home » Developer & Programmer » Forms » Saving a record in a table before saving an updated version
Saving a record in a table before saving an updated version [message #581416] Sat, 06 April 2013 01:03 Go to next message
somegirl
Messages: 19
Registered: March 2013
Junior Member
Hello,

I have a multi record block . I need to implement a functionality where the user updates a field in the record but the form saves the previous version of the record in another table .. Can anyone help please ? Thanks.

[Updated on: Sat, 06 April 2013 06:01]

Report message to a moderator

Re: Saving a record in a table before saving an updated version [message #581441 is a reply to message #581416] Sat, 06 April 2013 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 19320
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The simplest way I can think of is to use a database trigger. Here's an example - I'd want to log salary changes. Therefore, I'll create a table which will contain EMPNO, old and new salary. Of course, here's the trigger as well.
SQL> create table log_sal
  2    (empno     number,
  3     old_sal   number,
  4     new_sal   number
  5    );

Table created.

SQL> create or replace trigger trg_bu_sal
  2    before update of sal on emp
  3    for each row
  4  begin
  5    insert into log_sal
  6      (empno, old_sal, new_sal)
  7      values
  8     (:old.empno, :old.sal, :new.sal);
  9  end;
 10  /

Trigger created.

Let's test it:
SQL> select empno, sal from emp where ename = 'SCOTT';

     EMPNO        SAL
---------- ----------
      7788       3800

SQL> update emp set sal = 38 where ename = 'SCOTT';

1 row updated.

SQL> select empno, sal from emp where ename = 'SCOTT';

     EMPNO        SAL
---------- ----------
      7788         38

SQL> select * from log_sal;

     EMPNO    OLD_SAL    NEW_SAL
---------- ---------- ----------
      7788       3800         38

SQL>
Re: Saving a record in a table before saving an updated version [message #581673 is a reply to message #581441] Tue, 09 April 2013 13:24 Go to previous messageGo to next message
somegirl
Messages: 19
Registered: March 2013
Junior Member
Thanks this works Smile But I also need to check if the value of sal has changed actually before inserting the record. It would still be an update if
update emp set sal = 38 where ename = 'SCOTT';
How to check & compare old value with new value before doing any insert please in oracle forms ?

Thanks so much Smile
Re: Saving a record in a table before saving an updated version [message #581683 is a reply to message #581673] Tue, 09 April 2013 18:31 Go to previous message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're going to write triggers then you should read the chapter in the documentation on coding triggers

It answers your question.
Previous Topic: next_record executes When-Validate-Triggers of all items in the block
Next Topic: text_items are not visible in runtime
Goto Forum:
  


Current Time: Mon Jul 28 05:56:40 CDT 2014

Total time taken to generate the page: 0.12422 seconds