​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
A sample database trigger 


Here's a sample trigger showing you how can use them to audit database table changes. First of all you have to create your audit table - something like

create table tablechange
(
    table_name varchar2(40),
    change_type varchar2(1),   /* (I)nsert, (D)elete) or (U)pdate */
    change_user varchar2(20),
    change_time date
)


Now on the table or tables you wish to audit you need to create a trigger that will fire
every time its inserted into, deleted from or updated etc ...
Suppose we wish to track changes to our employee table. The trigger would look something like:-

create or replace trigger audit_emp
after  insert or delete or update on employee
declare
chtype varchar2(1);
begin
    if inserting then
        chtype = 'I';
    elsif updating then
    chtype = 'U';
    elsif deleting then
        chtype = 'D';
    end if;  

    insert into tablechange
    select 'employee',chtype,user,sysdate from dual;
end;


Note the use of the special identifiers inserting,updating and deleting. These are built-in and available to all triggers. The above trigger fires only once even though the underlying insert etc may affect many rows of the tables. Sometimes you want the trigger to fire on every insert or delete etc ... For this you can use the FOR EACH ROW condition. If you do this the built-in correlation names old and new become available to reference old updated or deleted records and new inserted or updated records. As another example suppose you want to record whenever an employee gets a salary raise greater that 10%. Assuming the audit table is set up as:-

create table tablechange
(
    emp_name varchar2(50),
    old_salary number,
    new_salary number,
    pct_increase number
)


the trigger could look like:-

create or replace trigger audit_emp
after  update of salary on employee
for each row
begin
    if new.salary/old.salary >1.1 then
        insert into tablechange(emp_name,old_salary,new_salary,pct_increase)
        values(new.emp_name,old.salary,new.salary,((new.salary/old.salary) -1)*100);
    end if;
end;