123

123

qwe

/*<TOAD_FILE_CHUNK>*/

create or replace trigger secure_emp

before insert on employees begin

 if (to_char(sysdate,'DY') in ('SAT','SUN')) or

   (to_char(sysdate,'HH24:MI')   not between '08:00' and '18:00') then

 raise_application_error(-20500, 'You may insert' ||' into EMPLOYEES table only during ' ||' business hours.');

 end if;

end;

/



/*<TOAD_FILE_CHUNK>*/

--создадим таблицу audit_emp


create table audit_emp(

 user_name varchar2(4000),

 time_stamp date,

 id number(6),

 old_last_name varchar2(25),

 new_last_name varchar2(25),

 old_title varchar2(10),

 new_title varchar(10),

 old_salary number(8,2),

 new_salary number(8,2)

 );


/*ALTER TABLE audit_emp

 ADD id number(6);*/

 

--вставка некоей случайной записи

insert into employees

  (employee_id, first_name, last_name, email, phone_number, 

  hire_date, job_id, salary, commission_pct, manager_id, 

  department_id, dn)

 values

  (employees_seq.nextval , 'King', 'Kong', 'KKONG', '515.123.4567', 

  to_date('06/17/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'AD_PRES', 999999, null, null, 

  100, '"cn=Steven King, ou=Executive, o=IMC, c=us"');


--слайд 13


create or replace trigger secure_emp before

insert or update or delete on employees begin

 if (to_char(sysdate,'DY') in ('SAT','SUN')) or

  (to_char(sysdate,'HH24') 

    not between '08' and '18') then

  if deleting then raise_application_error(

  -20502,'You may delete from EMPLOYEES table'||'only during business hours.');

  elsif inserting then raise_application_error(

  -20500,'You may insert into EMPLOYEES table'||'only during business hours.');

  elsif updating('SALARY') then

  raise_application_error(-20503, 'You may '||'update SALARY only during business hours.');

  else raise_application_error(-20504,'You may'||' update EMPLOYEES table only during'||' normal hours.');

  end if;

 end if;

end;


--слайд 14


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 more than $15,000.');

 END IF;

END;

/


/*<TOAD_FILE_CHUNK>*/

--слайд 15


create or replace trigger audit_emp_values

after delete or insert or update on employees

for each row

begin

 insert into audit_emp (user_name, time_stamp, id,

  old_last_name, new_last_name, old_title,

  new_title, old_salary, new_salary)

 values (nvl(user,'hr'), to_date(current_date, 'dd.mm.yyy hh24:mi:ss'), :old.employee_id,

  :old.last_name, :new.last_name, :old.job_id,

  :new.job_id, :old.salary, :new.salary);

end;

/

/*<TOAD_FILE_CHUNK>*/

--слайд 16


insert into employees

  (employee_id, first_name, last_name, email, phone_number, 

  hire_date, job_id, salary, commission_pct, manager_id, 

  department_id)

 values

  (999, 'Steven', 'King', 'SKING_01', '515.123.4567', 

  to_date('06/17/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'AD_PRES', 24000, null, null, 

  90);




UPDATE employees

 SET salary = 7000, last_name = 'Smith'

 WHERE employee_id = 100;




--слайд 17

create or replace trigger derive_commission_pct

before insert or update of salary on employees

for each row

when (new.job_id = 'SA_REP')

begin

 if inserting then

  :new.commission_pct := 0;

 elsif :old.commission_pct is null then

  :new.commission_pct := 0;

 else 

  :new.commission_pct := :old.commission_pct+0.05;

 end if;

end;

/


CREATE OR REPLACE TRIGGER PR_PROC_REQ_DATE_INS 

BEFORE INSERT

ON EXCELLENT.PR_PROC_REQUESTS

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

CALL proc_support.proc_req_check_date(:new.REQUEST_START, :new.REQUEST_STOP)

/



/*<TOAD_FILE_CHUNK>*/

--слайд 19

update employees set department_id = 999 

 where employee_id = 170; 


CREATE OR REPLACE TRIGGER employee_dept_fk_trg

AFTER UPDATE OF department_id ON employees FOR EACH ROW

BEGIN

 INSERT INTO departments VALUES(:new.department_id,     'Dept '||:new.department_id, NULL, NULL);

EXCEPTION

  WHEN DUP_VAL_ON_INDEX THEN

  NULL; -- mask exception if department exists

END;

/


create or replace trigger secure_emp

before insert on employees begin

 if (to_char(sysdate,'DY') in ('SAT','SUN')) or

   (to_char(sysdate,'HH24:MI')   not between '08:00' and '20:00') then

 raise_application_error(-20500, 'You may insert' ||' into EMPLOYEES table only during ' ||' business hours.');

 end if;

end;

/



--создадим таблицу audit_emp


create table audit_emp(

 user_name varchar2(4000),

 time_stamp date,

 id number(6),

 old_last_name varchar2(25),

 new_last_name varchar2(25),

 old_title varchar2(10),

 new_title varchar(10),

 old_salary number(8,2),

 new_salary number(8,2)

 );


/*ALTER TABLE audit_emp

 ADD id number(6);*/

 

--вставка некоей случайной записи

insert into employees

  (employee_id, first_name, last_name, email, phone_number, 

  hire_date, job_id, salary, commission_pct, manager_id, 

  department_id, dn)

 values

  (employees_seq.nextval , 'King', 'Kong', 'KKONG', '515.123.4567', 

  to_date('06/17/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'AD_PRES', 999999, null, null, 

  100, '"cn=Steven King, ou=Executive, o=IMC, c=us"');


--слайд 13


create or replace trigger secure_emp before

insert or update or delete on employees begin

 if (to_char(sysdate,'DY') in ('SAT','SUN')) or

  (to_char(sysdate,'HH24') 

    not between '08' and '18') then

  if deleting then raise_application_error(

  -20502,'You may delete from EMPLOYEES table'||'only during business hours.');

  elsif inserting then raise_application_error(

  -20500,'You may insert into EMPLOYEES table'||'only during business hours.');

  elsif updating('SALARY') then

  raise_application_error(-20503, 'You may '||'update SALARY only during business hours.');

  else raise_application_error(-20504,'You may'||' update EMPLOYEES table only during'||' normal hours.');

  end if;

 end if;

end;


--слайд 14


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','IT_PROG'))

   AND :NEW.salary > 15000 THEN

  RAISE_APPLICATION_ERROR (-20202,

   'Employee cannot earn more than $15,000.');

 END IF;

END;

/


--слайд 15


create or replace trigger audit_emp_values

after delete or insert or update on employees

for each row

begin

 insert into audit_emp (user_name, time_stamp, id,

  old_last_name, new_last_name, old_title,

  new_title, old_salary, new_salary)

 values (nvl(user,'hr'), to_date(current_date, 'dd.mm.yyy hh24:mi:ss'), :old.employee_id,

  :old.last_name, :new.last_name, :old.job_id,

  :new.job_id, :old.salary, :new.salary);

end;

/

--слайд 16


insert into employees

  (employee_id, first_name, last_name, email, phone_number, 

  hire_date, job_id, salary, commission_pct, manager_id, 

  department_id)

 values

  (999, 'Steven', 'King', 'SKING_01', '515.123.4567', 

  to_date('06/17/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'AD_PRES', 24000, null, null, 

  90);




UPDATE employees

 SET salary = 7000, last_name = 'Smith'

 WHERE employee_id = 100;




--слайд 17

create or replace trigger derive_commission_pct

before insert or update of salary on employees

for each row

when (new.job_id = 'SA_REP')

begin

 if inserting then

  :new.commission_pct := 0;

 elsif :old.commission_pct is null then

  :new.commission_pct := 0;

 else 

  :new.commission_pct := :old.commission_pct+0.05;

 end if;

end;

/


--слайд 19

update employees set department_id = 999 

 where employee_id = 170; 


CREATE OR REPLACE TRIGGER employee_dept_fk_trg

AFTER UPDATE OF department_id ON employees FOR EACH ROW

BEGIN

 INSERT INTO departments VALUES(:new.department_id,     'Dept '||:new.department_id, NULL, NULL);

EXCEPTION

  WHEN DUP_VAL_ON_INDEX THEN

  NULL; -- mask exception if department exists

END;

/


UPDATE employees SET department_id = 999 

 WHERE employee_id = 170;


--слайд 20 схема oe


CREATE OR REPLACE VIEW order_info AS

  SELECT c.customer_id, c.cust_last_name, c.cust_first_name,

     o.order_id, o.order_date, o.order_status

  FROM customers c, orders o

  WHERE c.customer_id = o.customer_id;


CREATE OR REPLACE TRIGGER order_info_insert

  INSTEAD OF INSERT ON order_info

  DECLARE

   duplicate_info EXCEPTION;

   PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

  BEGIN

   INSERT INTO customers

    (customer_id, cust_last_name, cust_first_name)

   VALUES (

   :new.customer_id,

   :new.cust_last_name,

   :new.cust_first_name);

  INSERT INTO orders (order_id, order_date, customer_id)

  VALUES (

   :new.order_id,

   :new.order_date,

   :new.customer_id);

  EXCEPTION

   WHEN duplicate_info THEN

    RAISE_APPLICATION_ERROR (

     num=> -20107,

     msg=> 'Duplicate customer or order ID');

  END order_info_insert;

  

  --слайд 20 для схемы hr

  INSERT INTO emp_details

VALUES (9001,'ABBOTT',3000, 10, 'Administration');


CREATE TABLE new_emps AS

 SELECT employee_id,last_name,salary,department_id FROM employees;


CREATE TABLE new_depts AS

 SELECT d.department_id,d.department_name,

    sum(e.salary) dept_sal FROM employees e, departments d

 WHERE e.department_id = d.department_id;


CREATE VIEW emp_details AS

 SELECT e.employee_id, e.last_name, e.salary,

    e.department_id, d.department_name

 FROM employees e, departments d

 WHERE e.department_id = d.department_id

GROUP BY d.department_id,d.department_name; 


---------------



CREATE TABLE SYSTEM.AUDTBASE

(

  NZAP NUMBER,

  POLZ VARCHAR2(20),

  TMIN DATE,

  OPER VARCHAR2(50)

)


CREATE OR REPLACE TRIGGER FIXUSERIN

  AFTER LOGON ON DATABASE


BEGIN


INSERT INTO SYSTEM.AUDTBASE(NZAP, POLZ, TMIN, OPER)

    VALUES(1, USER, SYSDATE, 'UserIsLog(off)');


END FIXUSERIN;

/