; DATABASE TRIGGERS
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

DATABASE TRIGGERS

VIEWS: 9 PAGES: 2

oracle, sql

More Info
  • pg 1
									                                        DATABASE TRIGGERS
1.Create a trigger for employee table to check the salary range, while
inserting a value for salary column or updating it in the table.

Solution
SQL> select * from employe;

   EMPNO ENAME JOB MANAGERNO JOINDATE PAY                                                    DEPTNO
 --------- ------------------------- ------------------------------ --------- --------- ---- -----------
   1001        Munwor teacher                    1             14-JAN-98         7500         30

   1002       Alexander       engineer           2             12-JUN-95         4500          32

   1003       Holley          analyst             3             03-FEB-99         6500         33

   1004       Jeorge          programmer          4             01-MAR-01         4800         34

   1005         jinu           teacher            1             01-OCT-90          5000         34

SQL>create or replace trigger t1 before insert or update on employe
for each row when ( (new.pay<5000) or (new.pay>9000))
begin
raise_application_error(-20001,’NOT VALID RANGE’);
end;

SQL> update employe set pay= 3000 where ename='jinu';
update employe set pay= 3000 where ename='jinu'
               *
ERROR at line 1:
ORA-20001: NOT VALID RANGE
ORA-06512: at "DIVYATM.T1", line 2
ORA-04088: error during execution of trigger 'DIVYATM.T1'

SQL> update employe set pay= 6000 where ename='jinu';

1 row updated.

SQL> SELECT * FROM EMPLOYE;
   EMPNO ENAME JOB MANAGERNO JOINDATE PAY                                                     DEPTNO
--------- ------------------------- ------------------------------ --------- --------- ----   -----------
    1001       Munwor teacher                     1            14-JAN-98          7500         30

   1002       Alexander       engineer           2             12-JUN-95         4500          32

   1003       Holley          analyst             3             03-FEB-99         6500         33

   1004       Jeorge          programmer          4             01-MAR-01         4800         34

   1005         jinu           teacher            1             01-OCT-90          6000        34

								
To top
;