Oracle Hướng dẫn kích hoạt PL/SQL: Thay vì, Hợp chất [Ví dụ]

Bởi: Fiona Nâu Fiona Nâu
Hours cập nhật

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

GÂY NÊN là các chương trình được lưu trữ được kích hoạt bởi Oracle tự động động cơ khi các câu lệnh DML như chèn, cập nhật, xóa được thực thi trên bảng hoặc một số sự kiện xảy ra. Mã được thực thi trong trường hợp kích hoạt có thể được xác định theo yêu cầu. Bạn có thể chọn sự kiện mà trình kích hoạt cần được kích hoạt và thời gian thực hiện. Mục đích của trigger là duy trì tính toàn vẹn của thông tin trên 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 Trigger: Nó kích hoạt trước khi sự kiện được chỉ định xảy ra.
  • SAU Trigger: Nó kích hoạt sau khi sự kiện được chỉ định xảy ra.
  • INSTEAD OF Trigger: Một loại đặc biệt. Bạn sẽ tìm hiểu thêm về các chủ đề tiếp theo. (chỉ dành cho DML)
  • Phân loại dựa trên cấp
  • Cấp độ STATEMENT Trình kích hoạt: Nó kích hoạt một lần cho câu lệnh sự kiện được chỉ định.
  • Trình kích hoạt cấp độ ROW: Nó kích hoạt cho từng bản ghi bị ảnh hưởng trong sự kiện được chỉ định. (chỉ dành cho DML)
  • Phân loại dựa trên Sự kiện
  • Trình kích hoạt DML: Nó kích hoạt khi sự kiện DML được chỉ định (CHÈN/CẬP NHẬT/XÓA)
  • Trình kích hoạt DDL: Nó kích hoạt khi sự kiện DDL được chỉ định (CREATE/ALTER)
  • Trình kích hoạt CƠ SỞ DỮ LIỆU: Nó kích hoạt khi sự kiện cơ sở dữ liệu được chỉ định (LOGON/LOGOFF/StartUP/SHUTDOWN)

Vì vậy, mỗi trigger là sự kết hợp của các tham số trên.

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

Dưới đây là cú pháp để tạo trigger.

Tạo Trigger

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.
  • TRƯỚC/SAU sẽ chỉ định thời gian 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 nào mà sự kiện nêu trên là hợp lệ. Ví dụ: đây sẽ là tên bảng mà sự kiện DML có thể xảy ra trong trường hợp Trình kích hoạt DML.
  • Lệnh “FOR EACH ROW” sẽ chỉ định kích hoạt cấp độ ROW.
  • Mệnh đề WHEN sẽ chỉ định điều kiện bổ sung mà trình kích hoạt cần kích hoạt.
  • Phần khai báo, phần thực thi, phần xử lý ngoại lệ giống như các phần còn lại Khối PL/SQL. Phần khai báo và phần xử lý ngoại lệ là tùy chọn.

NHỮNG BÀI VIẾT LIÊN QUAN

: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 RECORD để 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 trigger.

  • :MỚI - Nó giữ một giá trị mới cho các cột của bảng cơ sở/khung nhìn trong quá trình thực thi trình kích hoạt
  • :OLD – Nó giữ giá trị cũ của các cột của bảng cơ sở/khung nhìn trong quá trình thực thi trình kích hoạt

Điều khoản này nên được sử dụng dựa trên sự kiện DML. Bảng dưới đây sẽ chỉ định mệnh đề nào hợp lệ cho câu lệnh DML nào (CHÈN/CẬP NHẬT/XÓA).

CHÈN CẬP NHẬT DELETE
:MỚI CÓ HIỆU LỰC CÓ HIỆU LỰC KHÔNG HỢP LỆ. Không có giá trị mới trong trường hợp xóa.
:CŨ KHÔNG HỢP LỆ. Không có giá trị cũ trong trường hợp chèn CÓ HIỆU LỰC CÓ HIỆU LỰC

THAY VÌ Kích hoạt

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

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

Ví dụ 1:Trong ví dụ này, chúng ta sẽ tạo một chế độ xem phức tạp từ hai bảng cơ sở.

  • Bảng_1 là bảng trống và
  • Bảng_2 là bảng khoa.

Sau đó, chúng ta sẽ xem cách sử dụng trigger INSTEAD OF để phát hành lệnh UPDATE chi tiết vị trí trên chế độ xem phức tạp này. Chúng ta cũng sẽ xem cách :NEW và :OLD hữu ích trong trigger.

  • Bước 1: Tạo bảng 'emp' và 'dept' với các cột thích hợp
  • Bước 2: Điền vào bảng các giá trị mẫu
  • Bước 3: Tạo view cho bảng đã tạo ở trên
  • Bước 4: Cập nhật chế độ xem trước trình kích hoạt thay thế
  • Bước 5: Tạo trigger thay thế
  • Bước 6: Cập nhật chế độ xem sau khi kích hoạt thay thế

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

THAY VÌ Kích hoạt

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

Giải thích mã

  • Dòng mã 1-7: Tạo bảng 'emp'.
  • Dòng mã 8-12: Tạo bảng 'phòng'.

Đầu ra

Bảng đã được tạo

Bước 2) Bây giờ vì chúng tôi đã tạo bảng nên chúng tôi sẽ điền vào bảng này các giá trị mẫu và Tạo chế độ xem cho các bảng trên.

THAY VÌ Kích hoạt

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,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

Giải thích mã

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

Đầu ra

Thủ tục PL/SQL hoàn thành

Bước 3) Tạo chế độ xem cho bảng đã tạo ở trên.

THAY VÌ Kích hoạt

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;

Giải thích mã

  • Dòng mã 27-32: Tạo chế độ xem 'guru99_emp_view'.
  • Dòng mã 33: Truy vấn guru99_emp_view.

Đầu ra

Xem đã tạo

TÊN NHÂN VIÊN DEPT_NAME ĐỊA ĐIỂM
Zzz HR US
YYY BÁN HÀNG UK
XXX TÀI CHÍNH NHẬT BẢN

Bước 4) Cập nhật chế độ xem trước thay vì kích hoạt.

THAY VÌ Kích hoạt

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

Giải thích mã

  • Dòng mã 34-38: Cập nhật vị trí của “XXX” thành 'PHÁP'. Nó đưa ra ngoại lệ vì Các câu lệnh DML không được phép trong chế độ xem phức tạp.

Đầu ra

ORA-01779: không thể sửa đổi cột ánh xạ tới bảng không được bảo tồn khóa

ORA-06512: ở dòng 2

Bước 5)Để tránh gặp phải lỗi trong quá trình cập nhật chế độ xem ở bước trước, ở bước này, chúng tôi sẽ sử dụng “thay vì trình kích hoạt”.

THAY VÌ Kích hoạt

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

Giải thích mã

  • Dòng mã 39: Tạo trình kích hoạt INSTEAD OF cho sự kiện 'UPDATE' trên chế độ xem 'guru99_emp_view' ở cấp ROW. Nó chứa câu lệnh cập nhật để cập nhật vị trí trong bảng cơ sở 'phòng'.
  • Dòng mã 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

Đã tạo trình kích hoạt

Bước 6) Cập nhật chế độ xem sau khi kích hoạt thay thế. Bây giờ lỗi sẽ không xảy ra vì “kích hoạt thay thế” sẽ xử lý hoạt động cập nhật của chế độ xem phức tạp này. Và khi mã đã thực thi, vị trí của nhân viên XXX sẽ được cập nhật thành “Pháp” từ “Nhật Bản”.

THAY VÌ Kích hoạt

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

SELECT * FROM guru99_emp_view;

Giải thích mã:

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

Đầu ra:

Thủ tục PL/SQL đã hoàn tất thành công

TÊN NHÂN VIÊN DEPT_NAME ĐỊA ĐIỂM
Zzz HR US
YYY BÁN HÀNG UK
XXX TÀI CHÍNH FRANCE

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

Trình kích hoạt kết hợp là trình kích hoạt cho phép bạn chỉ định hành động cho từng điểm trong số bốn điểm thời gian trong nội dung trình kích hoạt duy nhất. Bốn điểm thời gian khác nhau mà nó hỗ trợ như sau.

  • 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 phương tiện để kết hợp các hành động theo thời gian khác nhau vào cùng một trình kích hoạt.

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

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
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 cho thấy việc tạo trình kích hoạt 'COMPOUND'.
  • Phần khai báo là phần chung cho tất cả khối thực thi trong phần thân trình kích hoạt.
  • 4 khối thời gian này có thể theo bất kỳ trình tự nào. Không bắt buộc phải có đủ 4 khối thời gian này. Chúng tôi chỉ có thể tạo trình kích hoạt COMPOUND cho các khoảng thời gian được yêu cầu.

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

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

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;

Giải thích mã:

  • Dòng mã 2-10: Tạo trình kích hoạt phức hợp. Nó được tạo để định thời gian TRƯỚC HÀNG để điền mức lương với giá trị mặc định 5000. Điều này sẽ thay đổi mức lương thành giá trị mặc định '5000' trước khi chèn bản ghi vào bảng.
  • Dòng mã 11-14: Chèn bản ghi vào bảng 'emp'.
  • Dòng mã 16: Xác minh bản ghi được chèn.

Đầu ra:

Đã tạo trình kích hoạt

Thủ tục PL/SQL đã hoàn tất thành công.

EMP_NAME EMP_NO LÃNH SỰ GIÁM ĐỐC DEPT_NO
CCC 1004 5000 AAA 30

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

Kích hoạt có thể được kích hoạt hoặc vô hiệu hóa. Để bật hoặc tắ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 để tắt hoặc bật trình kích hoạt đó.

Dưới đây là cú pháp để bật/tắt trigger.

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 biết cách bật/tắt trình kích hoạt đơn lẻ.
  • 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ể.

Tổng kết

Trong chương này, chúng ta đã tìm hiểu về PL/SQL trigger và những ưu điểm của chúng. Chúng ta cũng đã tìm hiểu các cách phân loại khác nhau và thảo luận về trình kích hoạt INSTEAD OF và COMPOUND.

Bạn có thể thích: