#################################TESTS##################
/* rule2 */
insert into emp (empno, ename, job, deptno)
values (7800,'HARRIS','PRESIDENT',20);
/* rule3 */
insert into emp (empno, ename, job, mgr, deptno)
values (7800,'HARRIS','CLERK',7698,20);
/* rule4*/
update emp
set sal = 1100
where empno = 7934;
/* rule5*/
select ename, sal, deptno
from emp
where deptno = 30;
update dept
set loc = 'HOUSTON'
where deptno = 30;
select ename, sal, deptno
from emp
where deptno = 30;
MORE TRIGGER CONCEPTS
-
Редица бизнес правила се прилагат към таблиците EMP и DEPT.
Във файла mgr_constraints_pkg.sql се съдържа кода на недовършен пакет, към който трява да добавите необходимите процедури/функции и те да бъдат извикани от подходящо създадени от вас тригери.
-
Решете как да реализирате всяко правило: като създадете ограничение или чрез използване на тригер.
-
Какви ограничения и тригери са необходими?
-
Реализирайте тригерите за онези бизнес правила, за които сте преценили че това е необходимо.
##########################################
CREATE OR REPLACE PACKAGE mgr_constraints_pkg
IS
PROCEDURE check_president;
PROCEDURE check_mgr;
PROCEDURE new_location (v_deptno IN dept.deptno%TYPE);
new_mgr EMP.MGR%TYPE := NULL;
END mgr_constraints_pkg;
/
CREATE OR REPLACE PACKAGE BODY mgr_constraints_pkg
IS
PROCEDURE check_president
IS
v_dummy NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_dummy
FROM emp
WHERE job = 'PRESIDENT';
IF v_dummy > 1 THEN
RAISE_APPLICATION_ERROR(-20001,'President title already exists');
END IF;
END check_president;
PROCEDURE new_location(v_deptno IN dept.deptno%TYPE)
IS
v_sal emp.sal%TYPE;
BEGIN
UPDATE emp
SET sal = sal*1.02
WHERE deptno = v_deptno;
END new_location;
PROCEDURE check_mgr
IS
count_emps NUMBER := 0;
BEGIN
IF new_mgr IS NOT NULL
THEN
-- count the number of people
-- working for the mgr
SELECT count(*)
INTO count_emps
FROM emp
WHERE mgr = new_mgr;
END IF;
-- if there are now more than 5,
-- raise an error
IF count_emps > 5
THEN RAISE_APPLICATION_ERROR (-20202,
'Max number of emps exceeded for '
||TO_CHAR(new_mgr));
END IF;
END check_mgr;
END mgr_constraints_pkg;
/
Бизнес правила: Правило 1.
Продавачите трябва винаги да получават комисионна. Работник, които не е продавач не трябва никога да получава комисионна.
/*UPDATE emp
SET comm=NULL
WHERE job!='SALESMAN'; */
ALTER TABLE emp
ADD CONSTRAINT emp_comm_chk CHECK ((job = 'SALESMAN' and
comm IS NOT NULL) OR (job != 'SALESMAN' and comm IS NULL))
/
Правило 2.
Таблицата EMP, трябва да съдържа точно един президент (работик с длъжност PRESIDENT). Тествайте вашето решение.
/* This is the solution trigger for practice 7, step 4b,
implementing business rule #2. This trigger calls a
procedure called CHECK_PRESIDENT in the MGR_CONSTRAINTS_PKG
package */
CREATE OR REPLACE TRIGGER check_pres_title
AFTER INSERT OR UPDATE OF job ON emp
BEGIN
mgr_constraints_pkg.check_president;
END check_pres_title;
/
Правило 3.
Работник никога не трябва да е мениджър на повече от 5 работника. Тествайте вашето решение.
Забележка: Приложеното решение работи само, ако операторът запалили тригера, обработва само 1 ред от таблицата. Как бихте пренаписали решението, така че то да работи и за оператори засягащи повече редове?
CREATE OR REPLACE TRIGGER set_mgr
AFTER INSERT or UPDATE of mgr on emp
FOR EACH ROW
BEGIN
-- To get round MUTATING TABLE ERROR
mgr_constraints_pkg.new_mgr := :NEW.mgr;
END set_mgr;
/
CREATE OR REPLACE TRIGGER chk_emps
AFTER INSERT or UPDATE of mgr on emp
BEGIN
mgr_constraints_pkg.check_mgr;
-- if for some reason, SET_MGR is disabled,
-- the global variable is set to null
-- to stop the SELECT COUNT running
mgr_constraints_pkg.new_mgr := NULL;
END chk_emps;
/
Правило 4.
Заплатите могат само да се увеличават, но никога не намаляват. Тествайте вашето решение.
/* This trigger checks for salary decreases and fails
if this is the case. This solution is for practice
7, part 4b, and implements business rule #4. */
CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal < old.sal)
BEGIN
RAISE_APPLICATION_ERROR(-20002,'Salary may not be reduced');
END check_sal;
/
Правило 5.
Ако един отдел се премести на друго място, на всеки работник от този отдел заплатата автоматично му се увеличава с 2 процента. Тествайте вашето решение.
/* This file implements business rule #5 for practice 7,
step 4. */
CREATE OR REPLACE TRIGGER change_location
BEFORE UPDATE OF loc ON dept
FOR EACH ROW
BEGIN
mgr_constraints_pkg.new_location(:old.deptno);
END change_location;
/
Сподели с приятели: |