Пример ЛР 7

7. Триггеры

Триггер: при зачислении контролируется превышение числа штатных единиц и фонда зарплаты.

Oracle

В среде Oracle Navigator создается триггер на таблицу work.

Имя триггера - T1
Условие активизации - INSERT
Время активизации - BEFORE
Область действия - FOR EACH ROW
Ссылка на старую таблицу именуется oldw, на новую - neww
Условие WHEN - отсутствует

Тело триггера:

DECLARE val NUMBER;
BEGIN
/* Выбирается разность между штатным количеством ставок
   по данной должности (staff.rate) и реальным количеством 
   ставок (выбирается в подзапросе) */
SELECT rate-s1 INTO val
   FROM staff,
      /* Количество ставок по данной должности */
      (SELECT :neww.staff_id AS st,SUM(rate) AS s1
         FROM work
         WHERE staff_id=:neww.staff_id) t1
   WHERE staff_id=st;
IF val-:neww.rate<0 
   THEN raise_application_error( -20777, 'Rate limitation'); 
END IF;

/* Выбирается разность между лимитом зарплаты
   данного отдела и реальной суммой зарплат 
   сотрудников отдела */
SELECT s2-s1 INTO val
  FROM
   /* сумма зарплат по отделу */
   (SELECT SUM(salary) AS s1
     FROM work
     WHERE staff_id IN
       (SELECT staff_id
          FROM staff
          WHERE dep_id=(SELECT dep_id
                          FROM staff
                          WHERE staff_id=:neww.staff_id))) t1,
   /* лимит зарплаты отдела */
   (SELECT salary_limit AS s2
     FROM staff, department
     WHERE staff.dep_id=department.dep_id
     AND staff.staff_id=:neww.staff_id) t2;
IF val-:neww.salary<0 
   THEN raise_application_error( -20777, 'Salary limitation'); 
END IF;
END;

DB2

В среде Control Center создается триггер:

Имя триггера - T1
На таблицу - work
Условие активизации - INSERT
Время активизации - BEFORE
Область действия - FOR EACH ROW
Ссылка на новую таблицу именуется neww

Действия триггера:

WHEN (
-- Сравнивается штатное количеством ставок по данной 
-- должности staff.rate) и реальное количество ставок 
-- (выбирается в подзапросе) 
 (SELECT rate 
  FROM staff 
  WHERE staff_id=neww.staff_id ) + neww.rate
<
(SELECT SUM(rate)
  FROM work
  WHERE staff_id=neww.staff_id) 
OR
-- Сравнивается лимит зарплаты данного отдела 
-- и реальная сумма зарплат сотрудников отдела 
 (SELECT SUM(salary)
  FROM work
  WHERE staff_id IN
    (SELECT staff_id
       FROM staff
       WHERE dep_id=(SELECT dep_id
                       FROM staff
                       WHERE staff_id=neww.staff_id))) + neww.salary
>
(SELECT salary_limit
  FROM staff, department
  WHERE staff.dep_id=department.dep_id
  AND staff.staff_id=neww.staff_id)
)
SIGNAL SQLSTATE '75000' ('Rate/salary limitation')

Подготовим нового сотрудника, на зачислении которого мы будем проверять действие триггера:

INSERT INTO employee VALUES (7777777777, 'Тестовый','Тест', 'Тестович', '01.09.2001', 'ул.Контрольная,1,кв.1', 777777);

Проведем предварительное исследование нашей БД для того, чтобы выяснить, в какой отдел и на какую должность нам нужно зачислять нового сотрудника, чтобы проверить бизнес-правила, задаваемые триггером.
Для этого:

Выберем по каждому отделу лимит зарплаты отдела и сумму реальных зарплат сотрудников отдела:
```
SELECT department.dep_id, salary_limit, s1
  FROM department,
     (SELECT dep_id, SUM(salary) AS s1
         FROM staff, work
         where staff.staff_id=work.staff_id
         GROUP BY dep_id) t1
  WHERE department.dep_id=t1.dep_id
  ORDER BY 1;

   DEP_ID SALARY_LIMIT        S1
--------- ------------ ---------
       10        28500     28900
       20        10500      8300
       30        22000     17900
       40        16000     10400
       50        26500     16200
       60        18500     17200
       70         2200     16500
       80        13000     10300
       90        12500     12600
```
(Приведена выборка, полученная в Oracle, результаты того же запроса для DB2 аналогичны)
Из полученной выборки мы видим, что в отделе 60 имеется небольшой "недорасход" фонда зарплаты. Если ввести в этот отдел нового сотрудника с зарплатой не более 1300, то условие ограничения по зарплате, задаваемое триггером, выполняться не будет; если же зарплата нового сотрудника будет больше 1300, то триггер не позволит ввести такое назначение.
Выберем по каждой должности штатного расписания для отдела 60 количество ставок по штатному расписанию и реальное количество ставок для данной должности:
```
SELECT staff.staff_id, staff.rate, s1
   FROM staff,
      (SELECT staff_id, SUM(rate) AS s1
          FROM work
          GROUP BY staff_id) t1
   WHERE staff.staff_id=t1.staff_id
   AND dep_id=60
   ORDER BY 1;
STAFF_ID      RATE        S1
--------- --------- ---------
      601         1         1
      602         1         1
      603         6         4
```
В отделе 60 имеется резерв ставок по должности 603. Если новый сотрудник будет получать одну ставку по должности 603, то условие ограничения по штатному расписанию, задаваемое триггером, выполняться не будет; если же нового сотрудника назначить на должности 601 или 602, то триггер не позволит ввести такое назначение.

Итак, тестовые запросы:

в следующем запросе должно сработать ограничение по штатному расписанию:
```
INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
   VALUES (7777777777, 602, 10000, '01-SEP-2001', 1);
```
в следующем запросе должно сработать ограничение по лимиту зарплаты:
```
INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
   VALUES (7777777777, 603, 2000, '01-SEP-2001', 1);
```

следующий запрос должен выполниться:

INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
   VALUES (7777777777, 603, 1000, '01-SEP-2001', 1);

Результаты выполнения - Oracle

SQL> INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
  2     VALUES (7777777777, 602, 10000, '01-SEP-2001', 1);
INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
            *
ERROR at line 1:
ORA-20777: Rate limitation
ORA-06512: at "U1.T1", line 14
ORA-04088: error during execution of trigger 'U1.T1'

SQL> INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
  2     VALUES (7777777777, 603, 2000, '01-SEP-2001', 1);
INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
            *
ERROR at line 1:
ORA-20777: Salary limitation
ORA-06512: at "U1.T1", line 37
ORA-04088: error during execution of trigger 'U1.T1'

SQL> INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
  2     VALUES (7777777777, 603, 1000, '01-SEP-2001', 1);

1 row created.

Результаты выполнения - DB2

------------------------------ Command Entered ------------------------------
INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
   VALUES (7777777777, 602, 10000, '01.09.2001', 1) ;
-----------------------------------------------------------------------------
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0438N  Application raised error with diagnostic text: "Rate/salary 
limitation".  SQLSTATE=75000

------------------------------ Command Entered ------------------------------
INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
   VALUES (7777777777, 603, 2000, '01.09.2001', 1) ;
-----------------------------------------------------------------------------
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0438N  Application raised error with diagnostic text: "Rate/salary 
limitation".  SQLSTATE=75000

------------------------------ Command Entered ------------------------------
INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
   VALUES (7777777777, 603, 1000, '01.09.2001', 1) ;
-----------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.