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.



Example:-  2

A trigger if specified for each row ,it is ired for each of the table being affected by the triggering statement.
If a trigger need to fire before insert or update of salary on employees table ,it will be fired as many times the rows are added or updated from a table.
This trigger will fired once for each row in a table affected by the statement.

Example:-
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP')) AND :NEW.salary > 15000 THEN
     RAISE_APPLICATION_ERROR (-20202,'Employee cannot earn this amount');
  END IF;
END;
/

Trigger created.


SQL> update employees set salary=16000
  2  where employee_id=197;
update employees set salary=16000
       *
ERROR at line 1:
ORA-20202: Employee cannot earn this amount
ORA-06512: at "HR.RESTRICT_SALARY", line 3
ORA-04088: error during execution of trigger 'HR.RESTRICT_SALARY'
Hence we will get this message because only AD_VP and AD_PRES  can earn salary more than 15000