How to create a trigger to call the procedure with an update statement

Question

I need to write a procedure and update trigger. When any update is done on the table, the trigger should make a call to the procedure. Procedure should update the changes in another table. In that another table old value, updated value should be there.

Answer 1

What you described sounds like an ordinary logging; you don't really need a procedure, trigger does it all. Here's an example:

SQL> create table emp_log (empno number, sal_old number, sal_new number);

Table created.

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

Trigger created.

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

     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5000

SQL> update emp set sal = 7000 where ename = 'KING';

1 row updated.

SQL> select * from emp_log;

     EMPNO    SAL_OLD    SAL_NEW
---------- ---------- ----------
      7839       5000       7000

SQL>

[EDIT, after reading a comment]

Homework, eh? So - create a procedure:

SQL> rollback;

Rollback complete.

SQL> create or replace procedure p_emp_sal_log
  2    (par_empno in emp.empno%type, par_sal_old in emp.sal%type,
  3                                  par_sal_new in emp.sal%type)
  4  is
  5  begin
  6    insert into emp_log (empno, sal_old, sal_new)
  7    values
  8    (par_empno, par_sal_old, par_sal_new);
  9  end;
 10  /

Procedure created.

SQL> create or replace trigger trg_bu_emp
  2    before update of sal on emp
  3    for each row
  4  begin
  5    p_emp_sal_log(:new.empno, :old.sal, :new.sal);
  6  end;
  7  /

Trigger created.

SQL> update emp set sal = 2000 where ename = 'KING';

1 row updated.

SQL> select * from emp_log;

     EMPNO    SAL_OLD    SAL_NEW
---------- ---------- ----------
      7839       5000       2000

SQL>