storedproce_trans_trigger1.htm
Steps :
  • Main Table
    table mystaff exists
  • Create a log table :
    create table mystaff_log(owner varchar2(30), time date)
  • Create a  trigger using  custom-php-ddl tool or SQL Developer Tool
    create or replace trigger mystaff_biud before insert or
    update or delete on mystaff
    begin
    insert into mystaff_log (owner, time) values(user,sysdate);
    end;
  • Create a stored procure, use ddl tool or SQL Developer Tool
    create or replace procedure mystaffinsert
    (pid in number, pfname in varchar2, plname in varchar2) as
    pragma autonomous_transaction;
    begin
    insert into mystaff(id, fname, lname) values(pid,pfname, plname);
    commit;
    end;
Mystaff table:
Screen shot from SQL Developer Tool:

Creating a procedure: mystaffinsert

create or replace procedure mystaffinsert
(pid in number, pfname in varchar2, plname in varchar2) as
begin
insert into mystaff(id, fname, lname) values(pid,pfname, plname);
commit;
end;

Screenshot from SQL-developer : procedure mystaffinsert
Create a log table

create table mystaff_log(owner varchar2(30), time date)

Screenshot of SQL developer, confirming the tables

 
Creating a trigger:

create or replace trigger mystaff_biud before insert or
update or delete on mystaff
begin
insert into mystaff_log (owner, time) values(user,sysdate);
end;

SQL-Developer:screen shot for the trigger
Now insert some data through the stored procedure:

begin mystaffinsert(14,'Thor','Hero');end;

Query Mystaff table

Now query Mystaff_Log table : The user name was "scott",