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.
0 comments:
Post a Comment