Hướng dẫn MySQL này giải thích cách tạo Trình kích hoạt SAU CẬP NHẬT trong MySQL với cú pháp và ví dụ
Sự miêu tả
Trình kích hoạt SAU CẬP NHẬT có nghĩa là MySQL sẽ kích hoạt trình kích hoạt này sau khi thao tác CẬP NHẬT được thực thi
cú pháp
Cú pháp để tạo Trình kích hoạt SAU CẬP NHẬT trong MySQL là
CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROW BEGIN -- variable declarations -- trigger code END;
Tham số hoặc Đối số
trigger_nameTên của trigger cần tạo. SAU CẬP NHẬTNó chỉ ra rằng trình kích hoạt sẽ kích hoạt sau khi thao tác CẬP NHẬT được thực thi. table_nameTên của bảng mà trình kích hoạt được tạo trên đóNhững hạn chế
- Bạn không thể tạo trình kích hoạt SAU trên chế độ xem
- Bạn không thể cập nhật các giá trị MỚI
- Bạn không thể cập nhật các giá trị CŨ
Ghi chú
- Xem thêm cách tạo trình kích hoạt SAU KHI XÓA, SAU KHI CHÈN, TRƯỚC XÓA, TRƯỚC CHÈN và TRƯỚC CẬP NHẬT
- Xem thêm cách thả trình kích hoạt
Thí dụ
Hãy xem một ví dụ về cách tạo trình kích hoạt SAU CẬP NHẬT bằng cách sử dụng câu lệnh CREATE TRIGGER trong MySQL
Chương này thảo luận về các thủ tục-kích hoạt cơ sở dữ liệu được lưu trữ trong cơ sở dữ liệu và được thực thi hoàn toàn ["kích hoạt"] khi một bảng được sửa đổi. Chủ đề bao gồm
Ghi chú
Nếu bạn đang sử dụng Oracle đáng tin cậy, hãy xem tài liệu Oracle đáng tin cậy để biết thêm thông tin về cách xác định và sử dụng trình kích hoạt cơ sở dữ liệu
Thiết kế kích hoạt
Sử dụng các nguyên tắc sau khi thiết kế trình kích hoạt
- Sử dụng trình kích hoạt để đảm bảo rằng khi một thao tác cụ thể được thực hiện, các hành động liên quan sẽ được thực hiện
- Chỉ sử dụng trình kích hoạt cơ sở dữ liệu cho các hoạt động toàn cầu, tập trung sẽ được kích hoạt cho câu lệnh kích hoạt, bất kể người dùng hoặc ứng dụng cơ sở dữ liệu nào đưa ra câu lệnh
- Không xác định các trình kích hoạt sao chép chức năng đã được tích hợp sẵn trong Oracle. Ví dụ: không xác định trình kích hoạt để thực thi các quy tắc toàn vẹn dữ liệu có thể dễ dàng thực thi bằng cách sử dụng các ràng buộc toàn vẹn khai báo
- Giới hạn kích thước của trình kích hoạt [60 dòng hoặc ít hơn là một hướng dẫn tốt]. Nếu logic cho trình kích hoạt của bạn yêu cầu nhiều hơn 60 dòng mã PL/SQL, thì tốt hơn là đưa hầu hết mã vào một thủ tục được lưu trữ và gọi thủ tục từ trình kích hoạt
- Hãy cẩn thận để không tạo trình kích hoạt đệ quy. Ví dụ: tạo trình kích hoạt câu lệnh SAU CẬP NHẬT trên bảng EMP mà chính nó đưa ra câu lệnh CẬP NHẬT trên EMP khiến trình kích hoạt kích hoạt đệ quy cho đến khi hết bộ nhớ
Tạo trình kích hoạt
Kích hoạt được tạo bằng lệnh CREATE TRIGGER. Lệnh này có thể được sử dụng với bất kỳ công cụ tương tác nào, chẳng hạn như SQL*Plus hoặc Enterprise Manager. Khi sử dụng một công cụ tương tác, một dấu gạch chéo [ / ] ở dòng cuối cùng được sử dụng để kích hoạt câu lệnh CREATE TRIGGER
Câu lệnh sau đây tạo trình kích hoạt cho bảng EMP
CREATE TRIGGER print_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW WHEN [new.empno > 0] DECLARE sal_diff number; BEGIN sal_diff := new.sal - old.sal; dbms_output.put['Old salary: ' || :old.sal]; dbms_output.put[' New salary: ' || :new.sal]; dbms_output.put_line[' Difference ' || sal_diff]; END; /
Nếu bạn nhập một câu lệnh SQL chẳng hạn như
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
trình kích hoạt sẽ kích hoạt một lần cho mỗi hàng được cập nhật và nó sẽ in mức lương mới và cũ cũng như sự khác biệt
Câu lệnh CREATE [hoặc CREATE OR REPLACE] không thành công nếu có bất kỳ lỗi nào tồn tại trong khối PL/SQL
Các phần sau sử dụng ví dụ này để minh họa cách chỉ định các phần của trình kích hoạt. Để biết thêm các ví dụ thực tế về câu lệnh CREATE TRIGGER, hãy xem
Điều kiện tiên quyết để tạo trình kích hoạt
Trước khi tạo bất kỳ trình kích hoạt nào, trong khi được kết nối dưới dạng SYS, hãy gửi CATPROC. tập lệnh SQL. Tập lệnh này tự động chạy tất cả các tập lệnh được yêu cầu hoặc được sử dụng trong các phần mở rộng thủ tục cho Máy chủ Oracle
Ghi chú
Vị trí của tệp này phụ thuộc vào hệ điều hành;
Kích hoạt đặt tên
Tên trình kích hoạt phải là duy nhất đối với các trình kích hoạt khác trong cùng một lược đồ. Tên trình kích hoạt không nhất thiết phải là duy nhất đối với các đối tượng lược đồ khác như bảng, dạng xem và thủ tục. Ví dụ: bảng và trình kích hoạt có thể có cùng tên [mặc dù, để tránh nhầm lẫn, điều này không được khuyến nghị]
Tùy chọn TRƯỚC và SAU
Tùy chọn TRƯỚC hoặc SAU trong câu lệnh CREATE TRIGGER chỉ định chính xác thời điểm gửi nội dung trình kích hoạt liên quan đến câu lệnh kích hoạt đang được thực thi. Trong câu lệnh TẠO KÍCH HOẠT, tùy chọn TRƯỚC hoặc SAU được chỉ định ngay trước câu lệnh kích hoạt. Ví dụ: trình kích hoạt PRINT_SALARY_CHANGES trong ví dụ trước là trình kích hoạt TRƯỚC
Ghi chú
Trình kích hoạt hàng SAU hiệu quả hơn một chút so với trình kích hoạt hàng TRƯỚC. Với các trình kích hoạt hàng TRƯỚC, các khối dữ liệu bị ảnh hưởng phải được đọc [đọc logic, không phải đọc vật lý] một lần cho trình kích hoạt và sau đó một lần nữa cho câu lệnh kích hoạt. Ngoài ra, với các trình kích hoạt hàng SAU, các khối dữ liệu chỉ cần được đọc một lần cho cả câu lệnh kích hoạt và trình kích hoạt
THAY THẾ CỦA Tùy chọn
Tùy chọn INSTEAD OF trong câu lệnh CREATE TRIGGER là tùy chọn thay thế cho tùy chọn TRƯỚC và SAU. INSTEAD OF triggers cung cấp một cách rõ ràng để sửa đổi các chế độ xem không thể sửa đổi trực tiếp thông qua các câu lệnh CẬP NHẬT, CHÈN và XÓA. Các trigger này được gọi là INSTEAD OF triggers bởi vì, không giống như các loại trigger khác, Oracle kích hoạt trigger thay vì thực thi câu lệnh trigger. Trình kích hoạt thực hiện các thao tác cập nhật, chèn hoặc xóa trực tiếp trên các bảng bên dưới
Người dùng viết các câu lệnh CẬP NHẬT, CHÈN và XÓA thông thường đối với chế độ xem và trình kích hoạt INSTEAD OF hoạt động ẩn trong nền để thực hiện đúng hành động
Theo mặc định, trình kích hoạt INSTEAD OF được kích hoạt cho mỗi hàng [xem ]
Chế độ xem không thể sửa đổi
Không thể sửa đổi chế độ xem bằng các câu lệnh CẬP NHẬT, CHÈN hoặc XÓA nếu truy vấn chế độ xem chứa bất kỳ cấu trúc nào sau đây
- thiết lập toán tử
- chức năng nhóm
- Các mệnh đề GROUP BY, CONNECT BY hoặc START WITH
- toán tử DISTINCT
- tham gia [một tập hợp con của chế độ xem tham gia có thể cập nhật]
Nếu một dạng xem chứa các cột giả hoặc biểu thức, bạn chỉ có thể cập nhật dạng xem bằng một câu lệnh CẬP NHẬT không đề cập đến bất kỳ cột giả hoặc biểu thức nào
Ví dụ về một TRigger THAY THẾ
Ví dụ sau đây cho thấy trình kích hoạt INSTEAD OF để chèn các hàng vào dạng xem MANAGER_INFO
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;
Các hành động được hiển thị cho các hàng được chèn vào dạng xem MANAGER_INFO trước tiên kiểm tra xem liệu các hàng thích hợp đã tồn tại trong các bảng cơ sở mà MANAGER_INFO được lấy từ đó chưa. Sau đó, các hành động sẽ chèn hàng mới hoặc cập nhật hàng hiện có, nếu thích hợp. Các trình kích hoạt tương tự có thể chỉ định các hành động thích hợp để CẬP NHẬT và XÓA
Chế độ xem đối tượng và THAY THẾ CỦA Trình kích hoạt
INSTEAD OF triggers cung cấp phương tiện để sửa đổi các thể hiện dạng xem đối tượng ở phía máy khách thông qua các lệnh gọi OCI. [Xem Hướng dẫn Lập trình viên Giao diện Cuộc gọi của Oracle để biết thêm thông tin. ] Để sửa đổi một đối tượng được cụ thể hóa bằng chế độ xem đối tượng trong bộ nhớ đệm đối tượng phía máy khách và xóa nó trở lại kho lưu trữ liên tục, người dùng phải chỉ định các trình kích hoạt INSTEAD OF, trừ khi chế độ xem đối tượng có thể sửa đổi được. Tuy nhiên, nếu đối tượng ở chế độ chỉ đọc, thì không cần thiết phải xác định trình kích hoạt để ghim đối tượng đó
Tuyên bố kích hoạt
Câu lệnh kích hoạt xác định
Ví dụ: trình kích hoạt PRINT_SALARY_CHANGES kích hoạt sau bất kỳ XÓA, CHÈN hoặc CẬP NHẬT nào trên bảng EMP. Bất kỳ câu lệnh nào sau đây sẽ kích hoạt trình kích hoạt PRINT_SALARY_CHANGES được đưa ra trong ví dụ trước
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
Danh sách cột để CẬP NHẬT
Nếu một câu lệnh kích hoạt chỉ định CẬP NHẬT, một danh sách các cột tùy chọn có thể được bao gồm trong câu lệnh kích hoạt. Nếu bạn bao gồm một danh sách cột, trình kích hoạt chỉ được kích hoạt trên câu lệnh CẬP NHẬT khi một trong các cột được chỉ định được cập nhật. Nếu bạn bỏ qua một danh sách cột, trình kích hoạt sẽ được kích hoạt khi bất kỳ cột nào của bảng được liên kết được cập nhật. Không thể chỉ định danh sách cột cho các câu lệnh kích hoạt INSERT hoặc DELETE
Ví dụ trước về trình kích hoạt PRINT_SALARY_CHANGES có thể bao gồm một danh sách cột trong câu lệnh kích hoạt, như trong
. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
CHO MỖI HÀNG Tùy chọn
Tùy chọn FOR EACH ROW xác định xem trình kích hoạt là trình kích hoạt hàng hay trình kích hoạt câu lệnh. Nếu bạn chỉ định CHO MỖI HÀNG, trình kích hoạt sẽ kích hoạt một lần cho mỗi hàng của bảng bị ảnh hưởng bởi câu lệnh kích hoạt. Việc không có tùy chọn CHO MỖI HÀNG có nghĩa là trình kích hoạt chỉ kích hoạt một lần cho mỗi câu lệnh áp dụng chứ không phải riêng cho từng hàng bị ảnh hưởng bởi câu lệnh
Ví dụ: bạn xác định trình kích hoạt sau
CREATE TRIGGER log_salary_increase AFTER UPDATE ON emp FOR EACH ROW WHEN [new.sal > 1000] BEGIN INSERT INTO emp_log [emp_id, log_date, new_salary, action] VALUES [:new.empno, SYSDATE, :new.sal, 'NEW SAL']; END;
và sau đó đưa ra câu lệnh SQL
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
Nếu có năm nhân viên trong bộ phận 20, trình kích hoạt sẽ kích hoạt năm lần khi câu lệnh này được đưa ra, vì năm hàng bị ảnh hưởng
Trình kích hoạt sau chỉ kích hoạt một lần cho mỗi CẬP NHẬT của bảng EMP
________số 8_______Mệnh đề KHI
Theo tùy chọn, có thể bao gồm một giới hạn trình kích hoạt trong định nghĩa của trình kích hoạt hàng bằng cách chỉ định một biểu thức Boolean SQL trong mệnh đề WHEN [mệnh đề WHEN không thể được bao gồm trong định nghĩa của trình kích hoạt câu lệnh]. Nếu được bao gồm, biểu thức trong mệnh đề KHI NÀO được đánh giá cho từng hàng mà trình kích hoạt ảnh hưởng đến. Nếu biểu thức đánh giá là TRUE cho một hàng, phần thân trình kích hoạt sẽ được kích hoạt thay mặt cho hàng đó. Tuy nhiên, nếu biểu thức đánh giá là FALSE hoặc NOT TRUE [nghĩa là không xác định, như với null] cho một hàng, phần thân trình kích hoạt sẽ không được kích hoạt cho hàng đó. Việc đánh giá mệnh đề WHEN không ảnh hưởng đến việc thực thi câu lệnh SQL kích hoạt [nghĩa là, câu lệnh kích hoạt không được khôi phục nếu biểu thức trong mệnh đề WHEN đánh giá là SAI]
Ví dụ: trong trình kích hoạt PRINT_SALARY_CHANGES, phần thân trình kích hoạt sẽ không được thực thi nếu giá trị mới của EMPNO bằng 0, NULL hoặc âm. Trong các ví dụ thực tế hơn, bạn có thể kiểm tra xem một giá trị cột có nhỏ hơn giá trị khác không
Biểu thức trong mệnh đề WHEN của trình kích hoạt hàng có thể bao gồm các tên tương quan, được giải thích bên dưới. Biểu thức trong mệnh đề WHEN phải là biểu thức SQL và không thể bao gồm truy vấn con. Bạn không thể sử dụng biểu thức PL/SQL [bao gồm các hàm do người dùng định nghĩa] trong mệnh đề WHEN
Cơ quan kích hoạt
Phần thân trình kích hoạt là một khối PL/SQL có thể bao gồm các câu lệnh SQL và PL/SQL. Các câu lệnh này được thực thi nếu câu lệnh kích hoạt được đưa ra và giới hạn kích hoạt [nếu có] đánh giá là TRUE. Thân trình kích hoạt cho trình kích hoạt hàng có một số cấu trúc đặc biệt có thể được bao gồm trong mã của khối PL/SQL. tên tương quan và tùy chọn THAM KHẢO và các vị từ có điều kiện CHÈN, XÓA và CẬP NHẬT
Truy cập các giá trị cột trong Trình kích hoạt hàng
Trong nội dung trình kích hoạt của trình kích hoạt hàng, mã PL/SQL và các câu lệnh SQL có quyền truy cập vào các giá trị cột cũ và mới của hàng hiện tại bị ảnh hưởng bởi câu lệnh kích hoạt. Hai tên tương quan tồn tại cho mọi cột của bảng đang được sửa đổi. một cho giá trị cột cũ và một cho giá trị cột mới. Tùy thuộc vào loại câu lệnh kích hoạt, một số tên tương quan nhất định có thể không có bất kỳ ý nghĩa nào
- Trình kích hoạt được kích hoạt bởi câu lệnh INSERT chỉ có quyền truy cập có ý nghĩa vào các giá trị cột mới. Bởi vì hàng đang được tạo bởi INSERT, các giá trị cũ là null
- Trình kích hoạt được kích hoạt bởi câu lệnh CẬP NHẬT có quyền truy cập vào cả giá trị cột cũ và cột mới cho cả trình kích hoạt hàng TRƯỚC và SAU
- Trình kích hoạt được kích hoạt bởi câu lệnh XÓA chỉ có quyền truy cập có ý nghĩa vào các giá trị cột cũ. Vì hàng sẽ không còn tồn tại sau khi hàng bị xóa, các giá trị mới là null
Các giá trị cột mới được tham chiếu bằng từ hạn định MỚI trước tên cột, trong khi các giá trị cột cũ được tham chiếu bằng từ hạn định OLD trước tên cột. Ví dụ: nếu câu lệnh kích hoạt được liên kết với bảng EMP [với các cột SAL, COMM, v.v. ], bạn có thể bao gồm các câu lệnh trong phần thân trình kích hoạt tương tự như
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .
Các giá trị cũ và mới có sẵn trong cả trình kích hoạt hàng TRƯỚC và SAU. Giá trị cột MỚI có thể được chỉ định trong trình kích hoạt hàng TRƯỚC, nhưng không phải trong trình kích hoạt hàng SAU [vì câu lệnh kích hoạt có hiệu lực trước khi trình kích hoạt hàng SAU được kích hoạt]. Nếu trình kích hoạt hàng TRƯỚC thay đổi giá trị của MỚI. CỘT, trình kích hoạt hàng SAU được kích hoạt bởi cùng một câu lệnh sẽ thấy thay đổi được chỉ định bởi trình kích hoạt hàng TRƯỚC
Tên tương quan cũng có thể được sử dụng trong biểu thức Boolean của mệnh đề WHEN. Dấu hai chấm phải đứng trước các từ hạn định OLD và NEW khi chúng được sử dụng trong phần thân của trình kích hoạt, nhưng dấu hai chấm không được phép khi sử dụng các từ hạn định trong mệnh đề KHI NÀO hoặc tùy chọn THAM KHẢO
Tùy chọn THAM KHẢO
Tùy chọn THAM KHẢO có thể được chỉ định trong phần thân trình kích hoạt của trình kích hoạt hàng để tránh xung đột tên giữa các tên và bảng tương quan có thể được đặt tên là "CŨ" hoặc "MỚI". Vì điều này hiếm xảy ra nên tùy chọn này ít được sử dụng
Ví dụ: giả sử bạn có một bảng có tên MỚI với các cột FIELD1 [số] và FIELD2 [ký tự]. Ví dụ CREATE TRIGGER sau đây cho thấy một trình kích hoạt được liên kết với bảng MỚI có thể sử dụng tên tương quan và tránh xung đột đặt tên giữa tên tương quan và tên bảng
CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;
Lưu ý rằng vòng loại MỚI được đổi tên thành MỚI NHẤT bằng cách sử dụng tùy chọn THAM KHẢO và sau đó được sử dụng trong phần thân trình kích hoạt
Vị ngữ có điều kiện
Nếu có nhiều loại thao tác DML có thể kích hoạt trình kích hoạt [ví dụ: "BẬT CHÈN HOẶC XÓA HOẶC CẬP NHẬT emp"], phần thân trình kích hoạt có thể sử dụng các vị từ có điều kiện CHÈN, XÓA và CẬP NHẬT để thực thi các khối mã cụ thể, tùy thuộc vào . Giả sử đây là câu lệnh kích hoạt
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
0Trong mã của phần thân trình kích hoạt, bạn có thể bao gồm các điều kiện sau
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
1Điều kiện đầu tiên được đánh giá là TRUE chỉ khi câu lệnh kích hoạt trình kích hoạt là câu lệnh INSERT;
Trong trình kích hoạt CẬP NHẬT, tên cột có thể được chỉ định bằng vị từ điều kiện CẬP NHẬT để xác định xem cột được đặt tên có đang được cập nhật hay không. Ví dụ: giả sử trình kích hoạt được định nghĩa là
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
2Mã trong mệnh đề THEN chỉ thực thi nếu câu lệnh CẬP NHẬT kích hoạt cập nhật cột SAL. Câu lệnh sau đây sẽ kích hoạt trình kích hoạt ở trên và khiến vị từ điều kiện UPDATING [sal] đánh giá là TRUE
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
3Điều kiện lỗi và ngoại lệ trong phần thân kích hoạt
Nếu một ngoại lệ hoặc điều kiện lỗi do người dùng xác định trước hoặc do người dùng xác định được phát sinh trong quá trình thực thi phần thân trình kích hoạt, thì tất cả các hiệu ứng của phần thân trình kích hoạt, cũng như câu lệnh kích hoạt, sẽ bị khôi phục [trừ khi lỗi bị chặn bởi một trình xử lý ngoại lệ]. Do đó, phần thân trình kích hoạt có thể ngăn việc thực thi câu lệnh kích hoạt bằng cách đưa ra một ngoại lệ. Các ngoại lệ do người dùng xác định thường được sử dụng trong các trình kích hoạt thực thi các ủy quyền bảo mật phức tạp hoặc các ràng buộc về tính toàn vẹn
Kích hoạt và xử lý ngoại lệ từ xa
Trình kích hoạt truy cập trang web từ xa không thể xử lý ngoại lệ từ xa nếu liên kết mạng không khả dụng. Ví dụ
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
4Trình kích hoạt được biên dịch khi nó được tạo. Do đó, nếu một trang web từ xa không khả dụng khi trình kích hoạt phải biên dịch, Oracle không thể xác thực câu lệnh truy cập cơ sở dữ liệu từ xa và quá trình biên dịch không thành công. Câu lệnh ngoại lệ trong ví dụ trước không thể thực thi vì trình kích hoạt không hoàn tất quá trình biên dịch
Bởi vì thủ tục lưu trữ được lưu trữ ở dạng đã biên dịch, cách khắc phục cho ví dụ trên như sau
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
5Trình kích hoạt trong ví dụ này biên dịch thành công và gọi thủ tục được lưu trữ, vốn đã có câu lệnh xác thực để truy cập cơ sở dữ liệu từ xa;
Hạn chế trong việc tạo trình kích hoạt
Mã hóa trình kích hoạt yêu cầu một số hạn chế không bắt buộc đối với các khối PL/SQL tiêu chuẩn. Các phần sau thảo luận về những hạn chế này
Các câu lệnh SQL hợp lệ trong các phần tử kích hoạt
Phần thân của trình kích hoạt có thể chứa các câu lệnh DML SQL. Nó cũng có thể chứa các câu lệnh CHỌN, nhưng chúng phải là CHỌN. VÀO TRONG. câu lệnh hoặc câu lệnh SELECT trong định nghĩa con trỏ]
Câu lệnh DDL không được phép trong phần thân của trình kích hoạt. Ngoài ra, không có câu lệnh kiểm soát giao dịch nào được phép trong trình kích hoạt. Không thể sử dụng các lệnh ROLLBACK, CAM KẾT và SAVEPOINT
Ghi chú
Một thủ tục được gọi bởi trình kích hoạt không thể thực thi các câu lệnh kiểm soát giao dịch ở trên vì thủ tục thực thi trong ngữ cảnh của phần thân trình kích hoạt
Các câu lệnh bên trong trình kích hoạt có thể tham chiếu các đối tượng lược đồ từ xa. Tuy nhiên, đặc biệt chú ý khi gọi các thủ tục từ xa từ bên trong trình kích hoạt cục bộ;
Kiểu dữ liệu LONG và LONG RAW
Các kiểu dữ liệu
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;9 và
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;9
. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .1 trong trình kích hoạt phải tuân theo các hạn chế sau
- Một câu lệnh SQL trong trình kích hoạt có thể chèn dữ liệu vào một cột có kiểu dữ liệu
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9 hoặcDELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
1 - Nếu dữ liệu từ cột
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9 hoặcDELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
1 có thể được chuyển đổi thành kiểu dữ liệu bị ràng buộc [chẳng hạn như. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
8 và. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
9], thì cộtDELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9 hoặcDELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
1 có thể được tham chiếu trong câu lệnh SQL trong trình kích hoạt. Lưu ý rằng độ dài tối đa cho các kiểu dữ liệu này là 32000 byte - Các biến không thể được khai báo bằng cách sử dụng kiểu dữ liệu
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9 hoặcDELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
1 CREATE TRIGGER log_salary_increase AFTER UPDATE ON emp FOR EACH ROW WHEN [new.sal > 1000] BEGIN INSERT INTO emp_log [emp_id, log_date, new_salary, action] VALUES [:new.empno, SYSDATE, :new.sal, 'NEW SAL']; END;
6 và. Không thể sử dụngCREATE TRIGGER log_salary_increase AFTER UPDATE ON emp FOR EACH ROW WHEN [new.sal > 1000] BEGIN INSERT INTO emp_log [emp_id, log_date, new_salary, action] VALUES [:new.empno, SYSDATE, :new.sal, 'NEW SAL']; END;
7 với cộtDELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9 hoặcDELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
9. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
1
Tham chiếu đến các biến gói
Nếu một câu lệnh
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1 hoặc
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2 phát hiện xung đột với một
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1 đồng thời, Oracle sẽ thực hiện khôi phục trong suốt về điểm lưu trữ và khởi động lại bản cập nhật. Điều này có thể xảy ra nhiều lần trước khi câu lệnh hoàn thành thành công. Mỗi khi khởi động lại câu lệnh, trình kích hoạt
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;4
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;5 lại được kích hoạt. Việc khôi phục về điểm lưu trữ không hoàn tác các thay đổi đối với bất kỳ biến gói nào được tham chiếu trong trình kích hoạt. Gói nên bao gồm một biến đếm để phát hiện tình huống này
Thứ tự đánh giá hàng
Cơ sở dữ liệu quan hệ không đảm bảo thứ tự các hàng được xử lý bởi câu lệnh SQL. Do đó, không tạo trình kích hoạt phụ thuộc vào thứ tự các hàng sẽ được xử lý. Ví dụ: không gán giá trị cho biến gói chung trong trình kích hoạt hàng nếu giá trị hiện tại của biến toàn cầu phụ thuộc vào hàng được trình kích hoạt hàng xử lý. Ngoài ra, nếu các biến gói chung được cập nhật trong trình kích hoạt, thì tốt nhất là khởi tạo các biến đó trong trình kích hoạt câu lệnh
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;4
Khi một câu lệnh trong nội dung trình kích hoạt khiến một trình kích hoạt khác được kích hoạt, các trình kích hoạt được gọi là xếp tầng. Oracle cho phép tối đa 32 trình kích hoạt xếp tầng bất kỳ lúc nào. Tuy nhiên, bạn có thể giới hạn hiệu quả số tầng trình kích hoạt bằng cách sử dụng tham số khởi tạo
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;7, bởi vì con trỏ phải được mở cho mỗi lần thực hiện trình kích hoạt
Lệnh đánh giá kích hoạt
Mặc dù bất kỳ trình kích hoạt nào cũng có thể thực thi một chuỗi các hoạt động trong dòng hoặc bằng cách gọi các thủ tục, việc sử dụng nhiều trình kích hoạt cùng loại sẽ tăng cường quản trị cơ sở dữ liệu bằng cách cho phép cài đặt mô-đun các ứng dụng có trình kích hoạt trên cùng một bảng
Oracle thực thi tất cả các trình kích hoạt cùng loại trước khi thực hiện các trình kích hoạt thuộc loại khác. Nếu bạn có nhiều trình kích hoạt cùng loại trên một bảng, Oracle sẽ chọn một thứ tự tùy ý để thực thi các trình kích hoạt này
hướng dẫn sử dụng để biết thêm thông tin về thứ tự kích hoạt trình kích hoạt.Mỗi trình kích hoạt tiếp theo sẽ thấy những thay đổi được thực hiện bởi trình kích hoạt trước đó. Mỗi trình kích hoạt có thể thấy các giá trị cũ và mới. Các giá trị cũ là các giá trị ban đầu và các giá trị mới là các giá trị hiện tại được đặt bởi trình kích hoạt
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1 hoặc
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;9 được kích hoạt gần đây nhất
Để đảm bảo rằng nhiều hành động được kích hoạt xảy ra theo một thứ tự cụ thể, bạn phải hợp nhất các hành động này thành một trình kích hoạt duy nhất [ví dụ: bằng cách yêu cầu trình kích hoạt gọi một loạt thủ tục]
Bạn không thể mở cơ sở dữ liệu chứa nhiều trình kích hoạt cùng loại nếu bạn đang sử dụng bất kỳ phiên bản Oracle nào trước bản phát hành 7. 1, bạn cũng không thể mở cơ sở dữ liệu như vậy nếu tham số khởi tạo
CREATE TRIGGER log_emp_update AFTER UPDATE ON emp BEGIN INSERT INTO emp_log [log_date, action] VALUES [SYSDATE, 'EMP COMMISSIONS CHANGED']; END;0 của bạn được đặt thành phiên bản sớm hơn 7. 1. 0
Bảng thay đổi và ràng buộc
Bảng đột biến là một bảng hiện đang được sửa đổi bởi một câu lệnh
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1,
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2 hoặc
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;9 hoặc một bảng có thể cần được cập nhật do tác động của một ràng buộc toàn vẹn tham chiếu
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2
CREATE TRIGGER log_emp_update AFTER UPDATE ON emp BEGIN INSERT INTO emp_log [log_date, action] VALUES [SYSDATE, 'EMP COMMISSIONS CHANGED']; END;5. Bảng ràng buộc là bảng mà câu lệnh kích hoạt có thể cần đọc trực tiếp đối với câu lệnh SQL hoặc gián tiếp đối với ràng buộc toàn vẹn tham chiếu khai báo. Một bảng đang thay đổi hoặc chỉ hạn chế đối với phiên đã đưa ra tuyên bố đang diễn ra
Các bảng không bao giờ được coi là đột biến hoặc hạn chế đối với các trình kích hoạt câu lệnh trừ khi trình kích hoạt được kích hoạt do một
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2
CREATE TRIGGER log_emp_update AFTER UPDATE ON emp BEGIN INSERT INTO emp_log [log_date, action] VALUES [SYSDATE, 'EMP COMMISSIONS CHANGED']; END;5
Đối với tất cả các trình kích hoạt hàng hoặc đối với các trình kích hoạt câu lệnh đã được kích hoạt do
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2
CREATE TRIGGER log_emp_update AFTER UPDATE ON emp BEGIN INSERT INTO emp_log [log_date, action] VALUES [SYSDATE, 'EMP COMMISSIONS CHANGED']; END;5, có hai hạn chế quan trọng liên quan đến việc thay đổi và ràng buộc bảng. Những hạn chế này ngăn trình kích hoạt nhìn thấy tập hợp dữ liệu không nhất quán
- Các câu lệnh SQL của trình kích hoạt không thể đọc từ [truy vấn] hoặc sửa đổi bảng đột biến của câu lệnh kích hoạt
- Các câu lệnh kích hoạt không thể thay đổi các cột
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .
0,IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .
1 hoặcIF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .
2IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .
3 của bảng ràng buộc của câu lệnh kích hoạtCó một ngoại lệ đối với hạn chế này; . Lưu ý rằng các câu lệnh
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
9 có thể bao gồm nhiều hơn một hàng, chẳng hạn nhưUPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
9CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;
1 empCREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;
2. . . , không được coi là chèn một hàng, ngay cả khi chúng chỉ dẫn đến một hàng được chèn
minh họa hạn chế được đặt trên các bảng đột biến
Hình 13-1 Các bảng đột biến
Khái niệm Oracle8 để biết thông tin về sự tương tác của trình kích hoạt và ràng buộc toàn vẹn.
Vì các ràng buộc toàn vẹn tham chiếu khai báo hiện không được hỗ trợ giữa các bảng trên các nút khác nhau của cơ sở dữ liệu phân tán, các hạn chế của bảng ràng buộc không áp dụng cho các trình kích hoạt truy cập các nút từ xa. Những hạn chế này cũng không được thực thi giữa các bảng trong cùng một cơ sở dữ liệu được kết nối bằng các liên kết cơ sở dữ liệu lặp lại. Liên kết cơ sở dữ liệu lặp lại làm cho bảng cục bộ xuất hiện từ xa bằng cách xác định đường dẫn Net8 quay lại cơ sở dữ liệu chứa liên kết
Bạn không nên sử dụng các liên kết cơ sở dữ liệu lặp lại để tránh các hạn chế kích hoạt. Các ứng dụng như vậy có thể hoạt động không thể đoán trước
Ai là người dùng kích hoạt?
Nếu bạn đưa ra tuyên bố
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
9trong trình kích hoạt, tên của chủ sở hữu trình kích hoạt được trả về, không phải tên của người dùng đang cập nhật bảng
Đặc quyền cần thiết để tạo trình kích hoạt
Để tạo trình kích hoạt trong lược đồ của bạn, bạn phải có đặc quyền hệ thống
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
05 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06 và- sở hữu bảng được chỉ định trong câu lệnh kích hoạt hoặc
- có đặc quyền
07 cho bảng trong câu lệnh kích hoạt, hoặcUPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
- có đặc quyền hệ thống
07UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
09UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
10UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
Để tạo trình kích hoạt trong lược đồ của người dùng khác, bạn phải có đặc quyền hệ thống
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
05 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
09 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06. Với đặc quyền này, trình kích hoạt có thể được tạo trong bất kỳ lược đồ nào và có thể được liên kết với bất kỳ bảng nào của người dùngĐặc quyền cho các đối tượng lược đồ được tham chiếu
Các đặc quyền đối tượng đối với các đối tượng lược đồ được tham chiếu trong nội dung trình kích hoạt phải được cấp rõ ràng cho chủ sở hữu trình kích hoạt [không thông qua vai trò]. Các câu lệnh trong nội dung trình kích hoạt hoạt động theo miền đặc quyền của chủ sở hữu trình kích hoạt, không phải miền đặc quyền của người dùng đưa ra câu lệnh kích hoạt. Điều này tương tự như thủ tục được lưu trữ
Khi kích hoạt được biên dịch
Trình kích hoạt tương tự như các khối ẩn danh PL/SQL với việc bổ sung.
CREATE TRIGGER log_salary_increase AFTER UPDATE ON emp FOR EACH ROW WHEN [new.sal > 1000] BEGIN INSERT INTO emp_log [emp_id, log_date, new_salary, action] VALUES [:new.empno, SYSDATE, :new.sal, 'NEW SAL']; END;6 và. Khả năng của
CREATE TRIGGER log_salary_increase AFTER UPDATE ON emp FOR EACH ROW WHEN [new.sal > 1000] BEGIN INSERT INTO emp_log [emp_id, log_date, new_salary, action] VALUES [:new.empno, SYSDATE, :new.sal, 'NEW SAL']; END;7, nhưng phần tổng hợp của chúng thì khác. Một khối ẩn danh PL/SQL được biên dịch mỗi khi nó được tải vào bộ nhớ. Biên dịch bao gồm ba giai đoạn
- kiểm tra cú pháp. Cú pháp PL/SQL được kiểm tra và cây phân tích cú pháp được tạo
- kiểm tra ngữ nghĩa. kiểm tra kiểu và xử lý thêm trên cây phân tích cú pháp
- Tạo mã. mã pcode được tạo
Ngược lại, các trình kích hoạt được biên dịch đầy đủ khi lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
05 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06 được ban hành và mã pcode được lưu trữ trong từ điển dữ liệu. Do đó, kích hoạt trình kích hoạt không còn yêu cầu mở con trỏ dùng chung để chạy hành động kích hoạt. Thay vào đó, trình kích hoạt được thực hiện trực tiếpNếu xảy ra lỗi trong quá trình biên dịch trình kích hoạt, trình kích hoạt vẫn được tạo. Nếu câu lệnh DML kích hoạt trình kích hoạt này, câu lệnh DML sẽ không thành công. [Lỗi kích hoạt thời gian chạy luôn khiến câu lệnh DML bị lỗi. ] Bạn có thể sử dụng lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
18 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
19 trong SQL*Plus hoặc Enterprise Manager để xem bất kỳ lỗi biên dịch nào khi bạn tạo trình kích hoạt hoặc bạn có thể CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;2 lỗi từ chế độ xem
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
21phụ thuộc
Trình kích hoạt được biên dịch có phụ thuộc. Chúng trở nên không hợp lệ nếu một đối tượng phụ thuộc, chẳng hạn như thủ tục được lưu trữ hoặc hàm được gọi từ phần thân trình kích hoạt, bị sửa đổi. Trình kích hoạt bị vô hiệu hóa vì lý do phụ thuộc được biên dịch lại khi được gọi tiếp theo
Bạn có thể kiểm tra chế độ xem
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
22 để xem các phụ thuộc cho trình kích hoạt. Ví dụ, tuyên bốCREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;0
hiển thị các phụ thuộc cho các trình kích hoạt trong lược đồ
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
23Biên dịch lại một Trigger
Sử dụng lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06 để biên dịch lại trình kích hoạt theo cách thủ công. Ví dụ, lệnhCREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;1
biên dịch lại trình kích hoạt
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
26Để biên dịch lại trình kích hoạt, bạn phải sở hữu trình kích hoạt hoặc có đặc quyền hệ thống
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
09 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06Vấn đề di chuyển
Trình kích hoạt không được biên dịch không thể được kích hoạt trong các bản phát hành trình kích hoạt được biên dịch [chẳng hạn như Oracle 7. 3 và Oracle8]. Nếu nâng cấp từ bản phát hành trình kích hoạt không được biên dịch lên bản phát hành trình kích hoạt đã biên dịch, tất cả các trình kích hoạt hiện có phải được biên dịch. Tập lệnh nâng cấp cat73xx. sql vô hiệu hóa tất cả các trình kích hoạt để chúng được tự động biên dịch lại khi thực thi lần đầu. [Xx là viết tắt của một số phát hành nhỏ thay đổi. ]
Hạ cấp từ Oracle 7. 3 hoặc muộn hơn để phát hành trước 7. 3 yêu cầu bạn thực thi cat73xxd. kịch bản hạ cấp sql. Điều này xử lý các vấn đề về tính di động giữa các bản phát hành kích hoạt được lưu trữ và không được lưu trữ
Gỡ lỗi Trình kích hoạt
Bạn có thể gỡ lỗi trình kích hoạt bằng cách sử dụng các phương tiện tương tự có sẵn cho các thủ tục được lưu trữ
Sửa đổi Trigger
Giống như một thủ tục được lưu trữ, không thể thay đổi trình kích hoạt một cách rõ ràng; . [Lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06 chỉ được sử dụng để biên dịch lại, bật hoặc tắt trình kích hoạt. ]Khi thay thế bộ kích hoạt, bạn phải bao gồm tùy chọn
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
32 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
33 trong câu lệnh UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
05 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06. Tùy chọn UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
32 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
33 được cung cấp để cho phép phiên bản mới của trình kích hoạt hiện có thay thế phiên bản cũ hơn mà không ảnh hưởng đến bất kỳ khoản trợ cấp nào dành cho phiên bản gốc của trình kích hoạtNgoài ra, có thể hủy trình kích hoạt bằng cách sử dụng lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
38 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06 và bạn có thể chạy lại lệnh UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
05 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06Để loại bỏ trình kích hoạt, trình kích hoạt phải nằm trong lược đồ của bạn hoặc bạn phải có đặc quyền hệ thống
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
38 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
09 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06Kích hoạt và vô hiệu hóa trình kích hoạt
Trình kích hoạt có thể ở một trong hai chế độ riêng biệt
kích hoạt
Một trình kích hoạt được kích hoạt sẽ thực thi phần thân trình kích hoạt của nó nếu một câu lệnh kích hoạt được đưa ra và giới hạn kích hoạt [nếu có] có giá trị là
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
45. tàn tật
Trình kích hoạt bị vô hiệu hóa không thực thi phần thân trình kích hoạt của nó, ngay cả khi một câu lệnh kích hoạt được đưa ra và giới hạn kích hoạt [nếu có] ước tính thành
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
45. Vô hiệu hóa kích hoạt
Bạn có thể tạm thời tắt trình kích hoạt nếu
- một đối tượng nó tham chiếu không có sẵn
- bạn phải thực hiện tải dữ liệu lớn và muốn quá trình này diễn ra nhanh chóng mà không kích hoạt trình kích hoạt
- bạn đang tải lại dữ liệu
Theo mặc định, trình kích hoạt được bật khi tạo lần đầu. Vô hiệu hóa trình kích hoạt bằng lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06 với tùy chọn UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
49. Ví dụ: để vô hiệu hóa trình kích hoạt có tên UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
50 của bảng UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
51, hãy nhập câu lệnh sauCREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;2
Tất cả các trình kích hoạt được liên kết với một bảng có thể bị vô hiệu hóa bằng một câu lệnh bằng cách sử dụng lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
10 với mệnh đề UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
49 và tùy chọn UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
55 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
56. Ví dụ: để vô hiệu hóa tất cả các trình kích hoạt được xác định cho bảng UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
51, hãy nhập câu lệnh sauCREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;3
Kích hoạt trình kích hoạt
Theo mặc định, trình kích hoạt được bật tự động khi nó được tạo; . Khi bạn đã hoàn thành tác vụ yêu cầu tắt trình kích hoạt, hãy bật lại trình kích hoạt để trình kích hoạt kích hoạt khi thích hợp
Bật trình kích hoạt bị vô hiệu hóa bằng cách sử dụng lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06 với tùy chọn UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
60. Để bật trình kích hoạt bị vô hiệu hóa có tên là UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
50 của bảng UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
51, hãy nhập câu lệnh sauCREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;4
Tất cả các trình kích hoạt được xác định cho một bảng cụ thể có thể được kích hoạt bằng một câu lệnh bằng cách sử dụng lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
10 với mệnh đề UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
60 với tùy chọn UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
55 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
56. Ví dụ: để kích hoạt tất cả các trình kích hoạt được xác định cho bảng UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
51, hãy nhập câu lệnh sauCREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;5
Các đặc quyền cần thiết để bật và tắt trình kích hoạt
Để bật hoặc tắt trình kích hoạt bằng cách sử dụng lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
10, bạn phải sở hữu bảng, có đặc quyền đối tượng lược đồ UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 cho bảng hoặc có đặc quyền hệ thống UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
09 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
10Để bật hoặc tắt trình kích hoạt bằng lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06, bạn phải sở hữu trình kích hoạt hoặc có đặc quyền hệ thống UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
07 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
09 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
06Liệt kê thông tin về kích hoạt
Các chế độ xem từ điển dữ liệu sau tiết lộ thông tin về trình kích hoạt
80UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
81UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
82UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
Tài liệu tham khảo Oracle8 đưa ra mô tả đầy đủ về các chế độ xem từ điển dữ liệu này. Ví dụ: giả sử câu lệnh sau được sử dụng để tạo trình kích hoạt
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
50CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;6
Hai truy vấn sau trả về thông tin về trình kích hoạt
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
50CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;7
Ví dụ về các ứng dụng kích hoạt
Bạn có thể sử dụng trình kích hoạt theo một số cách để tùy chỉnh quản lý thông tin trong cơ sở dữ liệu Oracle. Ví dụ, trigger thường được sử dụng để
- cung cấp kiểm toán tinh vi
- ngăn chặn giao dịch không hợp lệ
- thực thi tính toàn vẹn tham chiếu [những hành động không được hỗ trợ bởi các ràng buộc toàn vẹn khai báo hoặc trên các nút trong cơ sở dữ liệu phân tán]
- thực thi các quy tắc kinh doanh phức tạp
- thực thi ủy quyền bảo mật phức tạp
- cung cấp ghi nhật ký sự kiện minh bạch
- tự động tạo các giá trị cột dẫn xuất
Phần này cung cấp một ví dụ về từng ứng dụng kích hoạt ở trên. Những ví dụ này không nhằm mục đích sử dụng nguyên trạng mà được cung cấp để hỗ trợ bạn thiết kế trình kích hoạt của riêng mình
Kiểm tra với Triggers
Trigger thường được sử dụng để bổ sung cho các tính năng kiểm toán có sẵn của Oracle. Mặc dù trình kích hoạt có thể được viết để ghi thông tin tương tự như được ghi bởi lệnh
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
85, nhưng trình kích hoạt chỉ nên được sử dụng khi cần thông tin kiểm tra chi tiết hơn. Ví dụ: sử dụng trình kích hoạt để cung cấp kiểm tra dựa trên giá trị trên bảng cơ sở mỗi hàngĐôi khi, lệnh Oracle
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
85 được coi là cơ sở kiểm toán bảo mật, trong khi trình kích hoạt có thể cung cấp cơ sở kiểm toán tài chínhKhi quyết định có nên tạo trình kích hoạt để kiểm tra hoạt động cơ sở dữ liệu hay không, hãy xem xét các tính năng kiểm tra của Oracle cung cấp những gì, so với kiểm tra được xác định bởi trình kích hoạt
Kiểm toán DML cũng như DDL
Các tùy chọn kiểm tra tiêu chuẩn cho phép kiểm tra các câu lệnh DML và DDL liên quan đến tất cả các loại cấu trúc và đối tượng lược đồ. Một cách tương đối, trình kích hoạt chỉ cho phép kiểm tra các câu lệnh DML được phát hành đối với các bảng.
Đường mòn kiểm toán tập trung
Tất cả thông tin kiểm tra cơ sở dữ liệu được ghi lại tập trung và tự động bằng các tính năng kiểm tra của Oracle.
phương pháp khai báo
Các tính năng kiểm tra được kích hoạt bằng các tính năng tiêu chuẩn của Oracle dễ khai báo và bảo trì hơn, đồng thời ít bị lỗi hơn khi so sánh với các chức năng kiểm tra được xác định bởi trình kích hoạt.
Các tùy chọn kiểm toán có thể được kiểm toán
Mọi thay đổi đối với các tùy chọn kiểm tra hiện tại cũng có thể được kiểm tra để bảo vệ chống lại hoạt động cơ sở dữ liệu độc hại.
Kiểm tra phiên và thời gian thực hiện
Bằng cách sử dụng các tính năng kiểm tra cơ sở dữ liệu, các bản ghi có thể được tạo một lần mỗi khi đưa ra tuyên bố đã kiểm tra [
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
87 UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
88] hoặc một lần cho mỗi phiên đưa ra tuyên bố đã kiểm tra [BỞI UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
89]. Trình kích hoạt không thể kiểm tra theo phiên; . Kiểm tra truy cập dữ liệu không thành công
Kiểm tra cơ sở dữ liệu có thể được đặt thành kiểm tra khi xảy ra truy cập dữ liệu không thành công. Tuy nhiên, bất kỳ thông tin kiểm tra nào do trình kích hoạt tạo ra đều được khôi phục nếu câu lệnh kích hoạt được khôi phục.
Phiên có thể được kiểm tra
Kết nối và ngắt kết nối, cũng như hoạt động của phiên [I/O vật lý, I/O logic, bế tắc, v.v. ], có thể được ghi lại bằng kiểm tra cơ sở dữ liệu tiêu chuẩn.
Khi sử dụng trình kích hoạt để cung cấp khả năng kiểm tra tinh vi, thường sử dụng
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 trình kích hoạt. Bằng cách sử dụng trình kích hoạt
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6, thông tin kiểm tra được ghi lại sau khi câu lệnh kích hoạt phải tuân theo bất kỳ ràng buộc toàn vẹn hiện hành nào, ngăn ngừa các trường hợp khi quá trình kiểm tra được thực hiện không cần thiết đối với các câu lệnh tạo ra ngoại lệ đối với các ràng buộc toàn vẹn
Khi nào sử dụng hàng
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 so với.
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 trình kích hoạt câu lệnh phụ thuộc vào thông tin được kiểm toán. Ví dụ: trình kích hoạt hàng cung cấp kiểm tra dựa trên giá trị trên cơ sở từng hàng cho các bảng. Trình kích hoạt cũng có thể yêu cầu người dùng cung cấp "mã lý do" để phát hành câu lệnh SQL đã được kiểm tra, điều này có thể hữu ích trong cả tình huống kiểm tra cấp hàng và cấp câu lệnh
Ví dụ sau minh họa một trình kích hoạt kiểm tra các sửa đổi đối với bảng
CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;7 trên cơ sở mỗi hàng. Nó yêu cầu "mã lý do" được lưu trữ trong biến gói chung trước khi cập nhậtThí dụ
Kích hoạt này chứng tỏ
- cách sử dụng trình kích hoạt để cung cấp kiểm toán dựa trên giá trị
- cách sử dụng các biến gói công khai
Nhận xét trong mã giải thích chức năng của trình kích hoạt
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;8
Theo tùy chọn, bạn cũng có thể đặt lại mã lý do thành
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
95 nếu bạn muốn bắt buộc đặt mã lý do cho mỗi lần cập nhật. Trình kích hoạt câu lệnh IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 đơn giản sau đây đặt mã lý do trở lại
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
95 sau khi câu lệnh kích hoạt được thực thiCREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;9
Lưu ý rằng cả hai trình kích hoạt trước đó đều được kích hoạt bởi cùng một loại câu lệnh SQL. Tuy nhiên, trình kích hoạt hàng
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 được kích hoạt một lần cho mỗi hàng của bảng bị ảnh hưởng bởi câu lệnh kích hoạt, trong khi trình kích hoạt câu lệnh
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 chỉ được kích hoạt một lần sau khi hoàn thành việc thực thi câu lệnh kích hoạt
Một ví dụ khác về việc sử dụng trình kích hoạt để thực hiện kiểm toán được hiển thị bên dưới. Trình kích hoạt này theo dõi các thay đổi được thực hiện đối với bảng
CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;7 và lưu trữ thông tin này trong
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;01 và
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;02
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;0
Ràng buộc toàn vẹn và Triggers
Cả hai kích hoạt và ràng buộc toàn vẹn khai báo đều có thể được sử dụng để hạn chế đầu vào dữ liệu. Tuy nhiên, trigger và ràng buộc toàn vẹn có sự khác biệt đáng kể
Các ràng buộc toàn vẹn khai báo là các phát biểu về cơ sở dữ liệu luôn đúng. Một ràng buộc áp dụng cho dữ liệu hiện có trong bảng và bất kỳ câu lệnh nào thao tác trên bảng
Kích hoạt hạn chế những gì một giao dịch có thể làm. Trình kích hoạt không áp dụng cho dữ liệu được tải trước định nghĩa trình kích hoạt;
Mặc dù các trình kích hoạt có thể được viết để thực thi nhiều quy tắc tương tự được hỗ trợ bởi các tính năng ràng buộc toàn vẹn khai báo của Oracle, nhưng các trình kích hoạt chỉ nên được sử dụng để thực thi các quy tắc kinh doanh phức tạp không thể được xác định bằng các ràng buộc toàn vẹn tiêu chuẩn. Các tính năng ràng buộc toàn vẹn khai báo được cung cấp bởi Oracle cung cấp các ưu điểm sau khi so sánh với các ràng buộc được xác định bởi trình kích hoạt
Kiểm tra tính toàn vẹn tập trung
Tất cả các điểm truy cập dữ liệu phải tuân thủ bộ quy tắc chung được xác định bởi các ràng buộc toàn vẹn tương ứng với từng đối tượng lược đồ.
phương pháp khai báo
Các ràng buộc được xác định bằng các tính năng ràng buộc toàn vẹn tiêu chuẩn dễ viết hơn nhiều và ít bị lỗi hơn khi so sánh với các ràng buộc tương đương được xác định bởi trình kích hoạt.
Mặc dù hầu hết các khía cạnh của tính toàn vẹn dữ liệu có thể được xác định và thực thi bằng cách sử dụng các ràng buộc toàn vẹn khai báo, nhưng các trình kích hoạt có thể được sử dụng để thực thi các ràng buộc nghiệp vụ phức tạp không thể xác định được bằng các ràng buộc toàn vẹn khai báo. Ví dụ: các trình kích hoạt có thể được sử dụng để thực thi
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
1 vàUPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
2CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;
05
95, vàUPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
1 vàUPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
2CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;
05CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;
10 hành động tham chiếu- tính toàn vẹn tham chiếu khi bảng cha và bảng con nằm trên các nút khác nhau của cơ sở dữ liệu phân tán
- các ràng buộc kiểm tra phức tạp không thể xác định được bằng cách sử dụng các biểu thức được phép trong một ràng buộc
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;
11
Thực thi tính toàn vẹn tham chiếu bằng cách sử dụng trình kích hoạt
Nhiều trường hợp toàn vẹn tham chiếu có thể được thực thi bằng trình kích hoạt. Tuy nhiên, chỉ sử dụng trình kích hoạt khi bạn muốn thực thi
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1 và
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;05
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
95 [khi dữ liệu tham chiếu được cập nhật hoặc xóa, tất cả dữ liệu phụ thuộc liên quan đến trang web của UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
95] và UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1 và
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;05
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;10 [khi dữ liệu tham chiếu được cập nhật hoặc xóa,
Khi sử dụng trình kích hoạt để duy trì tính toàn vẹn của tham chiếu, hãy khai báo ràng buộc
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .0 [hoặc
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .2]
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .3 trong bảng cha. Nếu tính toàn vẹn tham chiếu đang được duy trì giữa bảng cha và bảng con trong cùng một cơ sở dữ liệu, bạn cũng có thể khai báo khóa ngoại trong bảng con nhưng vô hiệu hóa nó;
Để duy trì tính toàn vẹn tham chiếu bằng cách sử dụng trình kích hoạt
- Phải xác định trình kích hoạt cho bảng con để đảm bảo các giá trị được chèn hoặc cập nhật trong khóa ngoại tương ứng với các giá trị trong khóa cha
- Một hoặc nhiều trình kích hoạt phải được xác định cho bảng cha. Các trình kích hoạt này đảm bảo hành động tham chiếu mong muốn [_______3_______29,
CREATE TRIGGER log_emp_update AFTER UPDATE ON emp BEGIN INSERT INTO emp_log [log_date, action] VALUES [SYSDATE, 'EMP COMMISSIONS CHANGED']; END;
5 hoặcCREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;
05
95] cho các giá trị trong khóa ngoại khi các giá trị được cập nhật hoặc xóa trong khóa cha. Không cần thực hiện hành động nào để chèn vào bảng cha [không tồn tại khóa ngoại phụ thuộc]UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
Các phần sau đây cung cấp các ví dụ về trình kích hoạt cần thiết để thực thi tính toàn vẹn tham chiếu. Mối quan hệ bảng
CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;7 và
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;34 được sử dụng trong các ví dụ này
Một số trình kích hoạt bao gồm các câu lệnh khóa các hàng [_______10_______2.
CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;9
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1]. Hoạt động này là cần thiết để duy trì đồng thời khi các hàng đang được xử lýKích hoạt khóa ngoại cho bảng con
Trình kích hoạt sau đảm bảo rằng trước khi câu lệnh
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;9 hoặc
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1 ảnh hưởng đến giá trị khóa ngoại, thì giá trị tương ứng tồn tại trong khóa cha. Ngoại lệ bảng đột biến có trong ví dụ bên dưới cho phép sử dụng trình kích hoạt này với trình kích hoạt
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;40 và
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;41. Ngoại lệ này có thể được loại bỏ nếu trình kích hoạt này được sử dụng một mình
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;1Kích hoạt CẬP NHẬT và XÓA GIỚI HẠN cho Bảng gốc
Trình kích hoạt sau đây được xác định trên bảng
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;34 để thực thi hành động tham chiếu
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1 và
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;29 trên khóa chính của bảng
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;34
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;2
CẢNH BÁO
Trình kích hoạt này sẽ không hoạt động với các bảng tự tham chiếu [nghĩa là các bảng có cả khóa chính/khóa duy nhất và khóa ngoại]. Ngoài ra, trình kích hoạt này không cho phép trình kích hoạt quay vòng [chẳng hạn như A kích hoạt B kích hoạt A]
CẬP NHẬT và XÓA SET NULL Kích hoạt cho Bảng gốcTrình kích hoạt sau đây được xác định trên bảng
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;34 để thực thi hành động tham chiếu
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1 và
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;05
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
95 trên khóa chính của bảng CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;34
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;3DELETE Cascade Trigger cho Parent Table
Trình kích hoạt sau đây trên bảng
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;34 thực thi hành động tham chiếu
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;2
CREATE TRIGGER log_emp_update AFTER UPDATE ON emp BEGIN INSERT INTO emp_log [log_date, action] VALUES [SYSDATE, 'EMP COMMISSIONS CHANGED']; END;5 trên khóa chính của bảng
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;34
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;4
Ghi chú
Thông thường, mã cho tầng DELETE được kết hợp với mã cho UPDATE SET NULL hoặc UPDATE SET DEFAULT để giải thích cho cả cập nhật và xóa
CẬP NHẬT Trình kích hoạt Cascade cho Bảng gốcTrình kích hoạt sau đây đảm bảo rằng nếu số bộ phận được cập nhật trong bảng
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;34, thay đổi này sẽ được truyền tới các khóa ngoại phụ thuộc trong bảng
CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;7
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;5
Ghi chú
Vì trình kích hoạt này cập nhật bảng EMP nên trình kích hoạt EMP_DEPT_CHECK, nếu được bật, cũng được kích hoạt. Lỗi bảng đột biến kết quả bị bẫy bởi trình kích hoạt EMP_DEPT_CHECK. Bạn nên kiểm tra cẩn thận bất kỳ trình kích hoạt nào yêu cầu bẫy lỗi thành công để đảm bảo rằng chúng sẽ luôn hoạt động bình thường trong môi trường của bạn
Thực thi các ràng buộc kiểm tra phức tạp
Trình kích hoạt có thể thực thi các quy tắc toàn vẹn khác với tính toàn vẹn tham chiếu. Ví dụ: trình kích hoạt này thực hiện kiểm tra phức tạp trước khi cho phép thực thi câu lệnh kích hoạt. Nhận xét trong mã giải thích chức năng của trình kích hoạt
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;6
Ủy quyền và kích hoạt bảo mật phức tạp
Trình kích hoạt thường được sử dụng để thực thi ủy quyền bảo mật phức tạp cho dữ liệu bảng. Chỉ sử dụng các trình kích hoạt để thực thi các ủy quyền bảo mật phức tạp không thể xác định được bằng các tính năng bảo mật cơ sở dữ liệu được cung cấp cùng với Oracle. Ví dụ: trình kích hoạt có thể cấm cập nhật dữ liệu lương của bảng
CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR [:newest.field1]; END;7 vào cuối tuần, ngày lễ và ngoài giờ làm việc
Khi sử dụng trình kích hoạt để thực thi ủy quyền bảo mật phức tạp, tốt nhất là sử dụng trình kích hoạt câu lệnh
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;4. Sử dụng trình kích hoạt câu lệnh
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;4 có những lợi ích này
- Kiểm tra bảo mật được thực hiện trước khi câu lệnh kích hoạt được phép thực thi để không có công việc lãng phí nào được thực hiện bởi một câu lệnh trái phép
- Kiểm tra bảo mật chỉ được thực hiện một lần cho câu lệnh kích hoạt, không phải cho mỗi hàng bị ảnh hưởng bởi câu lệnh kích hoạt
Ví dụ này cho thấy một trình kích hoạt được sử dụng để thực thi bảo mật. Nhận xét trong mã giải thích chức năng của trình kích hoạt
DELETE FROM emp; INSERT INTO emp VALUES [ . . . ]; INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;7
Trình kích hoạt và ghi nhật ký sự kiện minh bạch
Trình kích hoạt rất hữu ích khi bạn muốn thực hiện rõ ràng một thay đổi có liên quan trong cơ sở dữ liệu sau một số sự kiện nhất định
Thí dụVí dụ về trình kích hoạt
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
50 trên cho thấy một trình kích hoạt sắp xếp lại các bộ phận khi cần thiết khi một số điều kiện nhất định được đáp ứng [nghĩa là một câu lệnh kích hoạt được đưa ra và giá trị CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;63 nhỏ hơn giá trị
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES[:n.empno, :n.name]; ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES[:n.deptno, :n.dept_type]; ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES[:n.projno, :n.project_level]; ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;64]
Giá trị và kích hoạt cột dẫn xuất
Trình kích hoạt có thể tự động lấy giá trị cột dựa trên giá trị được cung cấp bởi câu lệnh
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;9 hoặc
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;1. Loại trình kích hoạt này hữu ích để buộc các giá trị trong các cột cụ thể phụ thuộc vào giá trị của các cột khác trong cùng một hàng.
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;4 trình kích hoạt hàng là cần thiết để hoàn thành loại thao tác này vì
- các giá trị phụ thuộc phải được dẫn xuất trước khi chèn hoặc cập nhật xảy ra để câu lệnh kích hoạt có thể sử dụng các giá trị dẫn xuất
- bộ kích hoạt phải kích hoạt cho mỗi hàng bị ảnh hưởng bởi câu lệnh kích hoạt
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
9 hoặcUPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
1
Ví dụ sau minh họa cách sử dụng trình kích hoạt để lấy các giá trị cột mới cho bảng bất cứ khi nào một hàng được chèn hoặc cập nhật. Nhận xét trong mã giải thích chức năng của nó