Using a procedure within a Oracle Trigger

This is a sample how you can declare a procedure in the Declare section of the PL/SQL block.   This will reduce the number of redundant code in your program.

In the sample below, I want to log changes to value of fields within an employee master database.   I will make the assumption that the tables are already there.

Create Or Replace Trigger trEmp_AttribUpd
Before Update Of title, srv_years, status, remarks on empmaster
For Each Row
Declare
/*
 Create a history of the changes in the employee attributes
 Author    Strovek
 Date    Sep 26, 2005
 Revised
*/
    Procedure ins_attribhist(pParam IN Varchar2, pOldVal IN Varchar2, pNewVal IN Varchar2)
        IS
    Begin
        insert into test_attrib_hist
            (emp_num, step_num, date_time, modifier_id, modifier_name, param_name,
            from_content, to_content, ver) values
            (:new.emp_num, :new.step_num, sysdate, :new.modifier_id, :new.modifier_name, pParam,
            pOldVal, pNewVal, :new.ver);

    End ins_attribhist;

Begin

    If NVL(:new.ver, -1) > 0 Then
        If :old.title <> :new.title Then
            :new.step_num := nvl(:old.step_num, 0) + 1;
            ins_attribhist('title', :old.title, :new.title);
        End If;
        If :old.srv_years <> :new.srv_years Then
            :new.step_num := nvl(:old.step_num, 0) + 1;
            ins_attribhist('srv_years', :old.srv_years, :new.srv_years);
        End If;
        If :old.remarks <> :new.remarks Then
            :new.step_num := nvl(:old.step_num, 0) + 1;
            ins_attribhist('REMARKS', :old.remarks, :new.remarks);
        End If;
        If :old.status <> :new.status Then
            :new.step_num := nvl(:old.step_num, 0) + 1;
            ins_attribhist('STATUS', :old.status, :new.status);
        End If;
    End If;

End;