PL / SQL - Trình kích hoạt

Trong chương này, chúng ta sẽ thảo luận về các Trigger trong PL / SQL. Trình kích hoạt là các chương trình được lưu trữ, được tự động thực thi hoặc kích hoạt khi một số sự kiện xảy ra. Trên thực tế, các trình kích hoạt được viết ra để thực thi phản ứng với bất kỳ sự kiện nào sau đây:

A database manipulation (DML) câu lệnh (DELETE, INSERT hoặc UPDATE)
A database definition (DDL) câu lệnh (CREATE, ALTER hoặc DROP).
A database operation (MÁY CHỦ, LOGON, LOGOFF, STARTUP hoặc SHUTDOWN).

Trình kích hoạt có thể được xác định trên bảng, dạng xem, lược đồ hoặc cơ sở dữ liệu mà sự kiện được liên kết với.

Lợi ích của Trigger

Kích hoạt có thể được viết cho các mục đích sau:

Tự động tạo một số giá trị cột dẫn xuất
Thực thi tính toàn vẹn tham chiếu
Ghi nhật ký sự kiện và lưu trữ thông tin về quyền truy cập bảng
Auditing
Sao chép đồng bộ các bảng
Áp đặt ủy quyền bảo mật
Ngăn chặn các giao dịch không hợp lệ

Tạo trình kích hoạt

Cú pháp để tạo trình kích hoạt là:

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;

Ở đâu,

TẠO [HOẶC THAY THẾ] TRIGGER trigger_name - Tạo hoặc thay thế một trình kích hoạt hiện có bằng trigger_name .
{TRƯỚC | SAU | INSTEAD OF} - Điều này chỉ định thời điểm trình kích hoạt sẽ được thực thi. Mệnh đề INSTEAD OF được sử dụng để tạo trình kích hoạt trên một khung nhìn.
{CHÈN [HOẶC] | CẬP NHẬT [HOẶC] | DELETE} - Điều này chỉ định hoạt động DML.
[OF col_name] - Điều này chỉ định tên cột sẽ được cập nhật.
[ON table_name] - Tên này chỉ định tên của bảng được liên kết với trình kích hoạt.
[THAM KHẢO CŨ NHƯ o MỚI NHƯ n] - Điều này cho phép bạn tham khảo các giá trị mới và cũ cho các câu lệnh DML khác nhau, chẳng hạn như INSERT, UPDATE và DELETE.
[FOR EACH ROW] - Điều này chỉ định trình kích hoạt cấp hàng, tức là trình kích hoạt sẽ được thực thi cho mỗi hàng bị ảnh hưởng. Nếu không, trình kích hoạt sẽ thực thi chỉ một lần khi câu lệnh SQL được thực thi, được gọi là trình kích hoạt cấp bảng.
WHEN (điều kiện) - Điều này cung cấp điều kiện cho các hàng mà trình kích hoạt sẽ kích hoạt. Mệnh đề này chỉ hợp lệ đối với trình kích hoạt cấp hàng.

Thí dụ

Để bắt đầu, chúng ta sẽ sử dụng bảng CUSTOMERS mà chúng ta đã tạo và sử dụng trong các chương trước -

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 | 
+----+----------+-----+-----------+----------+

Chương trình sau tạo ra một row-levelkích hoạt cho bảng khách hàng sẽ kích hoạt các hoạt động CHÈN hoặc CẬP NHẬT hoặc XÓA được thực hiện trên bảng KHÁCH HÀNG. Trình kích hoạt này sẽ hiển thị chênh lệch lương giữa giá trị cũ và giá trị mới -

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

Khi mã trên được thực thi tại dấu nhắc SQL, nó tạo ra kết quả sau:

Trigger created.

Những điểm sau đây cần được xem xét ở đây:

Tham chiếu CŨ và MỚI không có sẵn cho trình kích hoạt cấp bảng, thay vào đó bạn có thể sử dụng chúng cho trình kích hoạt cấp bản ghi.
Nếu bạn muốn truy vấn bảng trong cùng một trình kích hoạt, thì bạn nên sử dụng từ khóa SAU vì trình kích hoạt có thể truy vấn bảng hoặc thay đổi lại bảng chỉ sau khi áp dụng các thay đổi ban đầu và bảng trở lại trạng thái nhất quán.
Trình kích hoạt ở trên đã được viết theo cách mà nó sẽ kích hoạt trước bất kỳ thao tác XÓA hoặc CHÈN hoặc CẬP NHẬT nào trên bảng, nhưng bạn có thể viết trình kích hoạt của mình trên một hoặc nhiều thao tác, ví dụ TRƯỚC KHI XÓA, thao tác này sẽ kích hoạt bất cứ khi nào một bản ghi sẽ bị xóa bằng thao tác DELETE trên bảng.

Kích hoạt một Trigger

Hãy để chúng tôi thực hiện một số thao tác DML trên bảng CUSTOMERS. Đây là một câu lệnh INSERT, sẽ tạo một bản ghi mới trong bảng -

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

Khi một bản ghi được tạo trong bảng CUSTOMERS, trình kích hoạt tạo ở trên, display_salary_changes sẽ được kích hoạt và nó sẽ hiển thị kết quả sau:

Old salary: 
New salary: 7500 
Salary difference:

Bởi vì đây là một kỷ lục mới, lương cũ không có sẵn và kết quả trên là vô hiệu. Bây giờ chúng ta hãy thực hiện một thao tác DML nữa trên bảng CUSTOMERS. Câu lệnh UPDATE sẽ cập nhật một bản ghi hiện có trong bảng -

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

Khi bản ghi được cập nhật trong bảng CUSTOMERS, trình kích hoạt tạo ở trên, display_salary_changes sẽ được kích hoạt và nó sẽ hiển thị kết quả sau:

Old salary: 1500 
New salary: 2000 
Salary difference: 500