Учебники

PL / SQL — Триггеры

Декабрь 14, 2018

В этой главе мы обсудим триггеры в PL / SQL. Триггеры — это хранимые программы, которые автоматически выполняются или запускаются при возникновении некоторых событий. Фактически триггеры записываются для выполнения в ответ на любое из следующих событий:

  • Оператор манипулирования базой данных (DML) (DELETE, INSERT или UPDATE)

  • Оператор определения базы данных (DDL) (CREATE, ALTER или DROP).

  • Операция базы данных (SERVERERROR, LOGON, LOGOFF, STARTUP или SHUTDOWN).

Оператор манипулирования базой данных (DML) (DELETE, INSERT или UPDATE)

Оператор определения базы данных (DDL) (CREATE, ALTER или DROP).

Операция базы данных (SERVERERROR, LOGON, LOGOFF, STARTUP или SHUTDOWN).

Триггеры могут быть определены в таблице, представлении, схеме или базе данных, с которыми связано событие.

Преимущества триггеров

Триггеры могут быть написаны для следующих целей —

  • Генерация некоторых производных значений столбцов автоматически
  • Обеспечение ссылочной целостности
  • Регистрация событий и хранение информации о доступе к таблице
  • Аудиторская проверка
  • Синхронная репликация таблиц
  • Внедрение авторизации безопасности
  • Предотвращение недействительных транзакций

Создание триггеров

Синтаксис для создания триггера — 

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;

Куда,

  • CREATE [OR REPLACE] TRIGGER trigger_name — Создает или заменяет существующий триггер на trigger_name .

  • {ДО | ПОСЛЕ | INSTEAD OF} — указывает, когда будет выполнен триггер. Предложение INSTEAD OF используется для создания триггера в представлении.

  • {ВСТАВИТЬ [ИЛИ] | ОБНОВЛЕНИЕ [ИЛИ] | DELETE} — указывает операцию DML.

  • [OF col_name] — указывает имя столбца, который будет обновляться.

  • [ON table_name] — указывает имя таблицы, связанной с триггером.

  • [ССЫЛКА СТАРЫЙ КАК НОВАЯ КАК НЕТ] — Это позволяет ссылаться на новые и старые значения для различных операторов DML, таких как INSERT, UPDATE и DELETE.

  • [FOR EACH ROW] — указывает триггер на уровне строки, т. Е. Триггер будет выполняться для каждой затрагиваемой строки. В противном случае триггер будет выполнен только один раз при выполнении оператора SQL, который называется триггером уровня таблицы.

  • WHEN (условие) — обеспечивает условие для строк, для которых сработает триггер. Это предложение действительно только для триггеров уровня строки.

CREATE [OR REPLACE] TRIGGER trigger_name — Создает или заменяет существующий триггер на trigger_name .

{ДО | ПОСЛЕ | INSTEAD OF} — указывает, когда будет выполнен триггер. Предложение INSTEAD OF используется для создания триггера в представлении.

{ВСТАВИТЬ [ИЛИ] | ОБНОВЛЕНИЕ [ИЛИ] | DELETE} — указывает операцию DML.

[OF col_name] — указывает имя столбца, который будет обновляться.

[ON table_name] — указывает имя таблицы, связанной с триггером.

[ССЫЛКА СТАРЫЙ КАК НОВАЯ КАК НЕТ] — Это позволяет ссылаться на новые и старые значения для различных операторов DML, таких как INSERT, UPDATE и DELETE.

[FOR EACH ROW] — указывает триггер на уровне строки, т. Е. Триггер будет выполняться для каждой затрагиваемой строки. В противном случае триггер будет выполнен только один раз при выполнении оператора SQL, который называется триггером уровня таблицы.

WHEN (условие) — обеспечивает условие для строк, для которых сработает триггер. Это предложение действительно только для триггеров уровня строки.

пример

Для начала мы будем использовать таблицу CUSTOMERS, которую мы создали и использовали в предыдущих главах — 

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

Следующая программа создает триггер уровня строки для таблицы клиентов, который будет запускаться для операций INSERT или UPDATE или DELETE, выполняемых над таблицей CUSTOMERS. Этот триггер отобразит разницу в зарплате между старыми и новыми значениями — 

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 
/

Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат — 

Trigger created.

Следующие пункты должны быть рассмотрены здесь —

  • Ссылки OLD и NEW недоступны для триггеров на уровне таблицы, вы можете использовать их для триггеров на уровне записи.

  • Если вы хотите запросить таблицу в том же триггере, вам следует использовать ключевое слово AFTER, потому что триггеры могут запросить таблицу или изменить ее снова только после применения начальных изменений и возврата таблицы в согласованное состояние.

  • Вышеупомянутый триггер был написан таким образом, что он сработает перед любой операцией DELETE, INSERT или UPDATE в таблице, но вы можете записать свой триггер на одну или несколько операций, например, BEFORE DELETE, которая будет срабатывать при каждой записи будет удален с помощью операции УДАЛИТЬ на столе.

Ссылки OLD и NEW недоступны для триггеров на уровне таблицы, вы можете использовать их для триггеров на уровне записи.

Если вы хотите запросить таблицу в том же триггере, вам следует использовать ключевое слово AFTER, потому что триггеры могут запросить таблицу или изменить ее снова только после применения начальных изменений и возврата таблицы в согласованное состояние.

Вышеупомянутый триггер был написан таким образом, что он сработает перед любой операцией DELETE, INSERT или UPDATE в таблице, но вы можете записать свой триггер на одну или несколько операций, например, BEFORE DELETE, которая будет срабатывать при каждой записи будет удален с помощью операции УДАЛИТЬ на столе.

Запуск триггера

Давайте выполним некоторые операции DML над таблицей CUSTOMERS. Вот одна инструкция INSERT, которая создаст новую запись в таблице: 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

Когда запись создается в таблице CUSTOMERS, запускается вышеуказанный триггер create display_salary_changes , который отображает следующий результат: 

Old salary: 
New salary: 7500 
Salary difference:

Поскольку это новая запись, старая зарплата недоступна, а приведенный выше результат равен нулю. Теперь выполним еще одну операцию DML над таблицей CUSTOMERS. Оператор UPDATE обновит существующую запись в таблице — 

UPDATE customers 
SET salary = salary + 500 
WHERE id = 2;

Когда запись обновляется в таблице CUSTOMERS, запускается вышеуказанный триггер create display_salary_changes , который отображает следующий результат:

57
Share: