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;
/