Oracle Trigger PL/SQL: Thay vì các kiểu dữ liệu phức hợp &

Trình kích hoạt trong PL/SQL là gì?

Các yếu tố kích hoạt được lưu trữ PL / SQL các chương trình được kích hoạt bởi Oracle động cơ tự động khi Các câu lệnh DML Các thao tác như chèn, cập nhật và xóa được thực thi trên bảng, hoặc khi một số sự kiện xảy ra. Mã cần thực thi trong trường hợp sử dụng trigger có thể được định nghĩa theo yêu cầu. Bạn có thể chọn sự kiện mà trigger cần được kích hoạt và thời điểm thực thi. Mục đích của trigger là duy trì tính toàn vẹn của thông tin trong cơ sở dữ liệu.

Lợi ích của trigger

Sau đây là những lợi ích của việc kích hoạt.

• 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ề truy cập bảng
• Kiểm toán
• Syncsao chép các bảng một cách đồng bộ
• Áp đặt ủy quyền bảo mật
• Ngăn chặn giao dịch không hợp lệ

Các loại trigger trong Oracle

Các yếu tố kích hoạt có thể được phân loại dựa trên các thông số sau.

Phân loại dựa trên thời gian

• TRƯỚC KHI kích hoạt: Nó được kích hoạt trước khi sự kiện được chỉ định xảy ra.
• SAU khi kích hoạt: Nó được kích hoạt sau khi sự kiện được chỉ định đã xảy ra.
• THAY VÌ Trigger: Một kiểu dữ liệu đặc biệt. Bạn sẽ tìm hiểu thêm trong các chủ đề tiếp theo. (chỉ dành cho DML)

Phân loại dựa trên cấp độ

• Kích hoạt ở cấp độ câu lệnh: Nó chỉ được kích hoạt một lần cho câu lệnh sự kiện được chỉ định.
• Kích hoạt ở cấp độ HÀNG: Sự kiện này được kích hoạt cho mỗi bản ghi bị ảnh hưởng trong sự kiện được chỉ định. (chỉ áp dụng cho thao tác DML)

Phân loại dựa trên sự kiện

• Kích hoạt DML: Sự kiện này được kích hoạt khi sự kiện DML được chỉ định (INSERT/UPDATE/DELETE).
• Kích hoạt DDL: Sự kiện này được kích hoạt khi sự kiện DDL được chỉ định (CREATE/ALTER).
• Kích hoạt cơ sở dữ liệu: Hàm này được kích hoạt khi sự kiện cơ sở dữ liệu được chỉ định (ĐĂNG NHẬP/ĐĂNG XUẤT/KHỞI ĐỘNG/TẮT MÁY).

Như vậy, mỗi yếu tố kích hoạt là sự kết hợp của các tham số nêu trên.

Cách tạo trình kích hoạt

Dưới đây là cú pháp để tạo một trình kích hoạt. Ảnh chụp màn hình bên dưới hiển thị cú pháp tạo trình kích hoạt này. Oracle.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

Giải thích cú pháp:

• Cú pháp trên hiển thị các câu lệnh tùy chọn khác nhau có trong quá trình tạo trình kích hoạt.
• BEFORE/AFTER sẽ chỉ định thời điểm diễn ra sự kiện.
• CHÈN/CẬP NHẬT/ĐĂNG NHẬP/TẠO/v.v. sẽ chỉ định sự kiện mà trình kích hoạt cần được kích hoạt.
• Mệnh đề ON sẽ chỉ định đối tượng mà sự kiện nêu trên có hiệu lực. Ví dụ, đó sẽ là tên bảng mà sự kiện DML có thể xảy ra trong trường hợp trigger DML.
• Lệnh “FOR EACH ROW” sẽ chỉ định trình kích hoạt ở cấp độ HÀNG.
• Mệnh đề WHEN sẽ chỉ định điều kiện bổ sung mà trong đó trình kích hoạt cần phải hoạt động.
• Phần khai báo, phần thực thi và phần xử lý ngoại lệ đều giống như các phần khác. Khối PL/SQLPhần khai báo và xử lý ngoại lệ Một số phần là tùy chọn.

:MỚI và :CŨ Điều khoản

Trong trình kích hoạt cấp hàng, trình kích hoạt sẽ kích hoạt cho từng hàng liên quan. Và đôi khi cần phải biết giá trị trước và sau câu lệnh DML.

Oracle Đã cung cấp hai mệnh đề trong trình kích hoạt cấp hàng để lưu giữ các giá trị này. Chúng ta có thể sử dụng các mệnh đề này để tham chiếu đến các giá trị cũ và mới bên trong phần thân của trình kích hoạt.

• :MỚI – Nó giữ giá trị mới cho các cột của bảng/chế độ xem cơ sở trong quá trình thực thi trình kích hoạt.
• :CŨ – Nó lưu giữ giá trị cũ của các cột trong bảng/chế độ xem cơ sở trong suốt quá trình thực thi trình kích hoạt.

Mệnh đề này nên được sử dụng dựa trên sự kiện DML. Bảng dưới đây chỉ rõ mệnh đề nào hợp lệ cho câu lệnh DML nào (INSERT/UPDATE/DELETE).

THAY VÌ Kích hoạt

Trigger “INSTEAD OF” là một loại trigger đặc biệt. Nó chỉ được sử dụng trong các trigger DML. Trigger này được sử dụng khi bất kỳ sự kiện DML nào sắp xảy ra trên một view phức tạp.

Hãy xem xét một ví dụ trong đó một view được tạo từ ba bảng cơ sở. Khi bất kỳ sự kiện DML nào được thực thi trên view này, nó sẽ trở nên không hợp lệ vì dữ liệu được lấy từ ba bảng khác nhau. Vì vậy, trong trường hợp này, một trigger INSTEAD OF được sử dụng. Trigger INSTEAD OF được sử dụng để sửa đổi trực tiếp các bảng cơ sở thay vì sửa đổi view cho sự kiện được chỉ định.

Ví dụ 1: Trong ví dụ này, chúng ta sẽ tạo một khung nhìn phức tạp từ hai bảng cơ sở, trong đó Table_1 là bảng nhân viên và Table_2 là bảng phòng ban.

Tiếp theo, chúng ta sẽ xem cách sử dụng trigger INSTEAD OF để cập nhật chi tiết vị trí trên chế độ xem phức tạp này. Chúng ta cũng sẽ xem cách sử dụng :NEW và :OLD trong trigger. Ví dụ được thực hiện theo các bước sau:

• Bước 1: Tạo bảng 'emp' và 'dept' với các cột phù hợp.
• Bước 2: Điền các giá trị mẫu vào bảng.
• Bước 3: Tạo khung nhìn cho các bảng đã tạo ở trên
• Bước 4: Cập nhật giao diện trước khi kích hoạt INSTEAD OF
• Bước 5: Tạo trình kích hoạt INSTEAD OF
• Bước 6: Cập nhật giao diện sau khi kích hoạt INSTEAD OF

Bước 1) Tạo các bảng 'emp' và 'dept' với các cột phù hợp.

Ảnh chụp màn hình bên dưới hiển thị quá trình tạo các bảng cơ sở 'emp' và 'dept' trong Oracle.

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept(
Dept_no NUMBER,
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Code Giải thích

• Code dòng 1-7: Tạo bảng 'emp'.
• Code dòng 8-12: Tạo bảng 'phòng ban'.

Đầu ra:

Table Created

Bước 2) Bây giờ, sau khi đã tạo các bảng, chúng ta sẽ điền các giá trị mẫu vào đó.

Ảnh chụp màn hình bên dưới hiển thị các hàng mẫu được chèn vào bảng 'dept' và 'emp'.

BEGIN
INSERT INTO DEPT VALUES(10,'HR','USA');
INSERT INTO DEPT VALUES(20,'SALES','UK');
INSERT INTO DEPT VALUES(30,'FINANCIAL','JAPAN');
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Giải thích

• Code dòng 13-19: Chèn dữ liệu vào bảng 'dept'.
• Code dòng 20-26: Chèn dữ liệu vào bảng 'emp'.

Đầu ra:

PL/SQL procedure completed

Bước 3) Tạo khung nhìn cho các bảng đã tạo ở trên.

Ảnh chụp màn hình bên dưới cho thấy quá trình tạo và truy vấn chế độ xem phức tạp.

CREATE VIEW guru99_emp_view(
Employee_name,dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Code Giải thích

• Code dòng 27-32: Tạo view 'guru99_emp_view'.
• Code dòng 33: Truy vấn guru99_emp_view.

Đầu ra:

View created

Bước 4) Cập nhật giao diện trước khi kích hoạt sự kiện INSTEAD OF.

Ảnh chụp màn hình bên dưới hiển thị quá trình cập nhật trên chế độ xem phức hợp và lỗi phát sinh.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

Code Giải thích

• Code dòng 34-38: Cập nhật vị trí của “XXX” thành 'PHÁP'. Điều này gây ra lỗi ngoại lệ vì các câu lệnh DML không được phép thực hiện trực tiếp trên chế độ xem phức hợp.

Đầu ra:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Bước 5) Để tránh lỗi gặp phải khi cập nhật giao diện ở bước trước, ở bước này chúng ta sẽ sử dụng "trigger INSTEAD OF".

Ảnh chụp màn hình bên dưới hiển thị quá trình tạo trình kích hoạt INSTEAD OF.

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Code Giải thích

• Code dòng 39: Tạo trigger INSTEAD OF cho sự kiện 'UPDATE' trên view 'guru99_emp_view' ở cấp độ ROW. Trigger này chứa câu lệnh cập nhật để cập nhật vị trí trong bảng cơ sở 'dept'.
• Code dòng 44: Câu lệnh cập nhật sử dụng ':NEW' và ':OLD' để tìm giá trị của các cột trước và sau khi cập nhật.

Đầu ra:

Trigger Created

Bước 6) Cập nhật khung nhìn sau khi kích hoạt điều kiện INSTEAD OF. Giờ đây lỗi sẽ không xuất hiện nữa, vì điều kiện “INSTEAD OF” sẽ xử lý thao tác cập nhật của khung nhìn phức tạp này. Khi mã được thực thi, vị trí của nhân viên XXX sẽ được cập nhật từ “Nhật Bản” thành “Pháp”.

Ảnh chụp màn hình bên dưới cho thấy quá trình cập nhật thành công thông qua trình kích hoạt INSTEAD OF và giao diện được làm mới.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code Giải thích:

• Code dòng 49-53: Cập nhật vị trí của “XXX” thành 'PHÁP'. Thao tác này thành công vì trình kích hoạt 'INSTEAD OF' đã ngăn chặn câu lệnh cập nhật thực tế trên khung nhìn và thực hiện cập nhật bảng cơ sở.
• Code dòng 55: Xác minh hồ sơ cập nhật.

Đầu ra:

PL/SQL procedure successfully completed

Kích hoạt hợp chất

Trigger phức hợp là một loại trigger cho phép bạn chỉ định các hành động cho mỗi trong bốn điểm thời gian trong một thân trigger duy nhất. Bốn điểm thời gian khác nhau mà nó hỗ trợ được liệt kê bên dưới.

• TRƯỚC KHI TUYÊN BỐ – cấp độ
• TRƯỚC HÀNG – cấp độ
• SAU HÀNG – cấp độ
• SAU KHI TUYÊN BỐ – cấp độ

Nó cung cấp khả năng kết hợp các hành động ở các thời điểm khác nhau vào cùng một trình kích hoạt.

Ảnh chụp màn hình bên dưới hiển thị cú pháp kích hoạt phức hợp với bốn phần định thời.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
ON <name of underlying object>
<Declarative part>
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

Giải thích cú pháp:

• Cú pháp trên thể hiện việc tạo ra một trình kích hoạt 'COMPOUND'.
• Phần khai báo là phần chung cho tất cả các khối thực thi trong phần thân của trình kích hoạt.
• Bốn khối định thời này có thể được sắp xếp theo bất kỳ trình tự nào. Không bắt buộc phải có cả bốn khối định thời. Chúng ta có thể tạo một bộ kích hoạt COMPOUND chỉ cho những khoảng thời gian cần thiết.

Ví dụ 1: Trong ví dụ này, chúng ta sẽ tạo một trigger để tự động điền cột lương với giá trị mặc định là 5000.

Ảnh chụp màn hình bên dưới hiển thị ví dụ về trình kích hoạt phức hợp và kết quả đầu ra của nó.

CREATE TRIGGER emp_trig
FOR INSERT
ON emp
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,'CCC',15000,'AAA',30);
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Code Giải thích:

• Code dòng 2-10: Tạo trigger phức hợp. Trigger này được tạo ở cấp độ TRƯỚC HÀNG để điền giá trị mặc định 5000 cho trường lương. Điều này sẽ thay đổi giá trị lương thành giá trị mặc định '5000' trước khi chèn bản ghi vào bảng.
• Code dòng 11-14: Chèn bản ghi vào bảng 'emp'.
• Code dòng 16: Đang xác minh bản ghi đã chèn.

Đầu ra:

Trigger created

PL/SQL procedure successfully completed.

Kích hoạt và vô hiệu hóa trình kích hoạt

Các trình kích hoạt có thể được bật hoặc tắt. Để bật hoặc tắt một trình kích hoạt, cần phải cung cấp câu lệnh ALTER (DDL) cho trình kích hoạt đó.

Dưới đây là cú pháp để bật/tắt các trình kích hoạt.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Giải thích cú pháp:

• Cú pháp đầu tiên cho thấy cách bật/tắt một trình kích hoạt duy nhất.
• Câu lệnh thứ hai cho biết cách bật/tắt tất cả các trình kích hoạt trên một bảng cụ thể.

Câu Hỏi Thường Gặp