Row Level Trigger In Oracle

create table emp(id number primary key,fname varchar2(50),age number,

address varchar2(50),salary  number)

The following is the program creates a row level trigger

For the EMP table that would fire for insert or update or delete data manipulation performed on the table.

create or replace trigger disp_sal

before delete or insert or update on emp

for each row

when(new.id > 0)

declare

diff_in_sal  number;

begin

diff_in_sal := :new.salary - :old.salary;

dbms_output.put_line('Old Salary :' || :old.salary);

dbms_output.put_line('New salary :' ||:new.salary);

dbms_output.put_line('Differance in salary ' || diff_in_sal);

end;

/

in this example  trigger fire for before insert/delete/update :OLD.salary  will contain the old salary before trigger fire.

: New.salary will contain new value after trigger fire. It will just keep a track of an employee’s previous salary.

Now Let us perform some DML operation on emp table.

SQL> insert into emp values(10,'Rutika',21,'Delhi',5000);

Old salary:

New salary: 5000

Salary difference:

Old Salary :

New salary :5000

Differance in salary

1 row created.

SQL> insert into emp values(20,'Sneha',22,'Mumbai',9000);

Old salary:

New salary: 9000

Salary difference:

Old Salary :

New salary :9000

Differance in salary

1 row created.

select * from emp;

Now let us perform update operation on emp table.

SQL> update emp set salary=salary+1000

  2  where id=10;

Old salary: 5000

New salary: 6000

Salary difference: 1000

Old Salary :5000

New salary :6000

Differance in salary 1000

1 row updated.