Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Trigger

 

A trigger prevents updates after business hours

SQL> SQL> create table employee(   2           emp_no                 integer     primary key   3          ,lastname               varchar2(20)    not null   4          ,firstname              varchar2(15)    not null   5          ,midinit                varchar2(1)   6          ,street                 varchar2(30)   7          ,city                   varchar2(20)   8          ,state                  varchar2(2)   9          ,zip                    varchar2(5)  10          ,zip_4                  varchar2(4)  11          ,area_code              varchar2(3)  12          ,phone                  varchar2(8)  13          ,salary                 number(5,2)  14          ,birthdate              date  15          ,hiredate               date  16          ,title                  varchar2(20)  17          ,dept_no                integer  18        ,mgr              integer  19        ,region           number  20        ,division         number  21        ,total_sales          number  22         ); Table created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, birthdate, title)   2  values (1,'Gardinia','Joy','R','688 Ave','New York','NY','12122','2333','212','200-3393','12-nov-1956','President'); 1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)   2  values (2,'Anderson','Lucy','J','33 Ave','New York','NY','43552','6633','212','234-4444',7.75,'21-mar-1951','1-feb-1994','Sales Manager',2,1,100,10,40000); 1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)   2  values (3,'Somers','Ingrid','E','12 Ave','New York','NY','76822','8763','212','867-6893',7.75,'14-feb-1963','15-mar-1995','Sales Clerk',2,2,100,10,10000); 1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)   2  values (4,'Washington','Georgia','J','13th Street','New York','NY','43122','4333','212','340-4365',11.50,'2-jul-1963','21-apr-1994','Designer',1,1,100,10,40000); 1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)   2  values (5,'Doright','Dudley','J','56 Langer Street','Staten Island','NY','23332','4983','718','777-4365',21.65,'15-may-1958','2-aug-1994','Designer',1,1,100,10,40000); 1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)   2  values ( 6,'Doright','Dorothy','R','56 Langer Street','Staten Island','NY','23332','4983','718','777-4365',24.65,'10-dec-1968','2-aug-1994','Designer',1,1,100,10,40000); 1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)   2  values ( 7,'Perry','Donna','R','1st Ave','New York','NY','44444','3444','212','111-6893',7.75,'14-feb-1967','15-mar-1995','Sales Clerk',2,1,100,10,40000); 1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)   2  values ( 8,'Roger','John','E','67 H Ave','New York','NY','33822','1163','212','122-6893',10.00,'14-jun-1956','15-mar-1995','Accountant',3,1,100,10,40000); 1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)   2  values ( 9,'Hall','Ted','R','1236 Lane','New York','NY','33823','1164','212','222-4393',13.00,'10-jun-1959','15-aug-1997','Sales Representative',3,1,100,10,50000); 1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)   2  values ( 10,'Barbee','Candice','L','400 Street','New York','NY','33811','2009','212','321-6873',12.00,'10-oct-1964','15-jan-1999','Sales Representative',3,1,100,10,35000); 1 row created. SQL> SQL> create or replace trigger tr_emp_maint_restrict   2  before update or insert or delete   3  on employee   4  begin   5    if to_char(sysdate, 'hh24') >= '09' AND   6       to_char(sysdate, 'hh24') <= '17' then   7       null;   8    else   9          raise_application_error (-20000, 'Employee info may not be modified at this time!') ;  10    end if ;  11  end ;  12  / Trigger created. SQL> SQL> --to test SQL> update employee set area_code = 212 where emp_no = 1; update employee set area_code = 212 where emp_no = 1        * ERROR at line 1: ORA-20000: Employee info may not be modified at this time! ORA-06512: at "RNTSOFT.TR_EMP_MAINT_RESTRICT", line 6 ORA-04088: error during execution of trigger 'RNTSOFT.TR_EMP_MAINT_RESTRICT' SQL> SQL> drop table employee; Table dropped. SQL>