Hướng dẫn kích hoạt Oracle PL / SQL: Thay vì, Compound (Ví dụ)

Trigger trong PL / SQL là gì?

TRIGGERS là các chương trình được lưu trữ do công cụ Oracle tự động kích hoạt 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 xuất ra 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.

Trong hướng dẫn này, bạn sẽ học-

Lợi ích của Trigger
Các loại trình kích hoạt trong Oracle
Cách tạo trình kích hoạt
: Mệnh đề MỚI và: CŨ
INSTEAD OF Trigger
Trình kích hoạt hợp chất

Lợi ích của Trigger

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

Tự động tạo một số giá trị cột có nguồn gốc
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
Kiểm toán
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ệ

Các loại trình kích hoạt trong Oracle

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
- BEFORE Trigger: Nó kích hoạt trước khi sự kiện được chỉ định xảy ra.
- SAU Kích hoạt: 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ủ đề khác. (chỉ dành cho DML)
Phân loại dựa trên mức độ
- Trình kích hoạt cấp STATEMENT: 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
- DML Trigger: Nó kích hoạt khi sự kiện DML được chỉ định (CHÈN / CẬP NHẬT / XÓA)
- DDL Trigger: Nó kích hoạt khi sự kiện DDL được chỉ định (CREATE / ALTER)
- 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 kích hoạt 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 trình kích hoạt.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

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 gian của 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 trình kích hoạt mức 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ư phần của các khối PL / SQL khác. Phần khai báo và phần xử lý ngoại lệ là tùy chọn.

: Mệnh đề MỚI và: CŨ

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 có 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 thân trình kích hoạt.

: MỚI - Nó giữ một 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
: OLD - Nó giữ giá trị cũ của 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

Mệnh đề 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	XÓA BỎ
: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

INSTEAD OF Trigger

"INSTEAD OF trigger" là loại trình kích hoạt đặc biệt. Nó chỉ được sử dụng trong các trình kích hoạt DML. Nó được sử dụng khi bất kỳ sự kiện DML nào sẽ xảy ra trên dạng xem phức hợ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 trên chế độ xem này, sự kiện đó sẽ trở nên không hợp lệ vì dữ liệu được lấy từ 3 bảng khác nhau. Vì vậy, trong 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 khung nhìn phức hợp từ hai bảng cơ sở.

Bảng_1 là bảng trống và

Bảng_2 là bảng phòng ban.

Sau đó, chúng ta sẽ xem trình kích hoạt INSTEAD OF được sử dụng như thế nào để đưa ra CẬP NHẬT câu lệnh chi tiết vị trí trên dạng xem phức tạp này. Chúng ta cũng sẽ xem: MỚI và: CŨ hữu ích như thế nào trong việc kích hoạt.

Bước 1: Tạo bảng 'emp' và 'dept' với các cột thích hợp
Bước 2: Đưa vào bảng các giá trị mẫu
Bước 3: Tạo chế độ xem 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 vì
Bước 5: Tạo trình kích hoạt thay vì
Bước 6: Cập nhật chế độ xem sau thay vì kích hoạt

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

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 'ghi nợ'.

Đầu ra

Đã tạo bảng

Bước 2) Bây giờ vì chúng ta đã tạo bảng, chúng ta 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.

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT 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 'dept'.
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 khung nhìn cho bảng đã tạo ở trên.

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE 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

Chế độ xem đã tạo

TÊN NHÂN VIÊN	DEPT_NAME	VỊ TRÍ
ZZZ	Nhân sự	Hoa Kỳ
YYY	BÁN HÀNG	Vương quốc Anh
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.

BEGINUPDATE 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 'FRANCE'. Nó đã nêu ra ngoại lệ vì các câu lệnh DML không được phép trong dạng xem phức hợp.

Đầu ra

ORA-01779: không thể sửa đổi cột ánh xạ tới một bảng không được lưu giữ khóa

ORA-06512: tại dòng 2

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

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE 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ở 'dept'.
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 thay vì kích hoạt. Bây giờ lỗi sẽ không xảy ra vì "thay vì kích hoạt" 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."

BEGINUPDATE 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 "FRANCE". 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 dạng xem và thực hiện cập nhật bảng cơ sở.
Dòng mã 55: Xác minh bản ghi đã 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	VỊ TRÍ
ZZZ	Nhân sự	Hoa Kỳ
YYY	BÁN HÀNG	Vương quốc Anh
XXX	TÀI CHÍNH	PHÁP

Trình 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 các hành động cho từng trong bốn điểm thời gian trong nội dung trình kích hoạt duy nhất. Bốn thời điểm khác nhau mà nó hỗ trợ như bên dưới.

BÁO CÁO TRƯỚC - cấp độ
TRƯỚC ROW - cấp độ
SAU ROW - cấp
SAU KHI THỐNG KÊ - cấp độ

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

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;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à chung cho tất cả khối thực thi trong thân trình kích hoạt.
4 khối định thời này có thể nằm trong một chuỗi bất kỳ. Không bắt buộc phải có tất 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 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 cột lương với giá trị mặc định là 5000.

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/

BEGININSERT 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 hợp chất. Nó được tạo để định thời gian TRƯỚC ROW- để đ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 đã 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	TIỀN LƯƠNG	GIÁM ĐỐC	DEPT_NO
CCC	1004	5000	AAA	30

Bật và tắt trình kích hoạt

Kích hoạt có thể được bật hoặc tắt. Để bật hoặc tắt trình kích hoạt, cần đưa ra câu lệnh ALTER (DDL) cho trình kích hoạt tắt hoặc bật nó.

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

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [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 trình kích hoạt duy nhất.
Câu lệnh thứ hai chỉ ra 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óm lược

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