Làm cách nào để truy cập giá trị mới trong trình kích hoạt MySQL?
|
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ụ Show
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ápCú 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ế
Ghi chú
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ạtSử dụng các nguyên tắc sau khi thiết kế trình kích hoạt
Tạo trình kích hoạtKí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ư 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ạtTrướ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ênTê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à SAUTù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ọnTù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 đổiKhô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
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ạtINSTEAD 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ạtCâ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ẬTNế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ọnTù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 đề KHITheo 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ạtPhầ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àngTrong 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
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ẢOTù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ệnNế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 Trong 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 Đ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à Mã 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 Điều kiện lỗi và ngoại lệ trong phần thân kích hoạtNế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ừ xaTrì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ụ Trì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 Trì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ạtMã 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ạtPhầ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 RAWCá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
Tham chiếu đến các biến góiNế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àyThứ tự đánh giá hàngCơ 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;
4Khi 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ạtLệnh đánh giá kích hoạtMặ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. 0Bảng thay đổi và ràng buộcBả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 raCá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
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ếnLưu ý rằng câu lệnh SQL được thực thi cho hàng đầu tiên của bảng và sau đó trình kích hoạt IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .5 được kích hoạt. Đổi lại, một câu lệnh trong phần thân trình kích hoạt IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .5 cố gắng truy vấn bảng gốc. Tuy nhiên, vì 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 đang thay đổi, truy vấn này không được Oracle cho phép. Nếu cố gắng, sẽ xảy ra lỗi thời gian chạy, tác động của phần thân trình kích hoạt và câu lệnh kích hoạt được khôi phục và quyền điều khiển được trả lại cho người dùng hoặc ứng dụng Xem xét trình kích hoạt sau Nếu câu lệnh SQL được ban hành, lỗi sau được trả về Oracle trả về lỗi này khi trình kích hoạt kích hoạt do bảng bị thay đổi khi hàng đầu tiên bị xóa. (Chỉ có một hàng bị xóa bởi câu lệnh, vì CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR (:newest.field1); END;8 là khóa chính, nhưng Oracle không có cách nào biết được điều đó. ) Nếu bạn xóa dò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;9 IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .5" khỏi trình kích hoạt ở trên, trình kích hoạt sẽ trở thành trình kích hoạt câu lệnh, bảng không thay đổi khi trình kích hoạt kích hoạt và trình kích hoạt xuất dữ liệu chính xác Nếu bạn cần cập nhật bảng thay đổi hoặc ràng buộc, bạn có thể sử dụng bảng tạm thời, bảng PL/SQL hoặc biến gói để bỏ qua những hạn chế này. Ví dụ: thay vì một trình kích hoạt hàng IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 duy nhất cập nhật bảng gốc, dẫn đến lỗi bảng đột biến, bạn có thể sử dụng hai trình kích hoạt-một trình kích hoạt hàng IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 cập nhật bảng tạm thời và một trình kích hoạt câu lệnh IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 cập nhật Các ràng buộc toàn vẹn khai báo được kiểm tra tại các thời điểm khác nhau đối với các trình kích hoạt hàng Xem thêm 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ố trong 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
Để 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 Đặc quyền cho các đối tượng lược đồ được tham chiếuCá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ịchTrì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
Ngược lại, các trình kích hoạt được biên dịch đầy đủ khi lệnh Nế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 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 phụ thuộcTrì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 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;
0hiển thị các phụ thuộc cho các trình kích hoạt trong lược đồ Biên dịch lại một TriggerSử dụng lệnh 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;
1biên dịch lại trình kích hoạt Để 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 Vấn đề di chuyểnTrì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ạtBạ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 TriggerGiố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 Khi thay thế bộ kích hoạt, bạn phải bao gồm tùy chọn Ngoài ra, có thể hủy trình kích hoạt bằng cách sử dụng lệnh Để 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 Kích hoạt và vô hiệu hóa trình kích hoạtTrì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à 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 Vô hiệu hóa kích hoạtBạn có thể tạm thời tắt trình kích hoạt nế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 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;
2Tấ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 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;
3Kích hoạt trình kích hoạtTheo 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 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;
4Tấ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 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;
5Cá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 Để bật hoặc tắt trình kích hoạt bằng lệnh Liệt kê thông tin về kích hoạtCác chế độ xem từ điển dữ liệu sau tiết lộ thông tin về trình kích hoạt
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 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;
6Hai truy vấn sau trả về thông tin về 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;
7Ví dụ về các ứng dụng kích hoạtBạ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 để
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 TriggersTrigger 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 Đôi khi, lệnh Oracle Khi 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 ( 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ật Thí dụKích hoạt này chứng tỏ
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;
8Theo tùy chọn, bạn cũng có thể đặt lại mã lý do thành IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .6 đơn giản sau đây đặt mã lý do trở lại 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;
9Lư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;
02DELETE FROM emp; INSERT INTO emp VALUES ( . . . ); INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;0 Ràng buộc toàn vẹn và TriggersCả 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
Thực thi tính toàn vẹn tham chiếu bằng cách sử dụng trình kích hoạtNhiề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 + 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
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àyMộ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 conTrì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ìnhDELETE FROM emp; INSERT INTO emp VALUES ( . . . ); INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;1 Kích hoạt CẬP NHẬT và XÓA GIỚI HẠN 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;
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;
34DELETE 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 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;
34DELETE FROM emp; INSERT INTO emp VALUES ( . . . ); INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;3 DELETE Cascade Trigger cho Parent TableTrì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;
34DELETE 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ạpTrì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ạpTrì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
Thí dụ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ạchTrì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 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ấtTrì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ì
Thí dụ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ó Làm cách nào bạn có thể truy cập giá trị mới bên trong trình kích hoạt trong MySQL?Trả lời. MỚI. qty tham chiếu số lượng trên bảng mà trình kích hoạt được đặt, không phải bảng đang được cập nhật. TẠO KÍCH HOẠT after_sales_insert SAU KHI CHÈN VÀO doanh số CHO MỖI HÀNG BẮT ĐẦU CẬP NHẬT sản phẩm SET qty = qty - NEW .
Làm cách nào để sử dụng sau khi kích hoạt cập nhật trong MySQL?Trình kích hoạt SAU CẬP NHẬT trong MySQL được gọi tự động bất cứ khi nào một sự kiện CẬP NHẬT được kích hoạt trên bảng được liên kết với trình kích hoạt. . TẠO TRIGGER trigger_name SAU KHI CẬP NHẬT TRÊN tên_bảng CHO MỖI HÀNG trigger_body ; Làm cách nào để chỉ thực thi trình kích hoạt khi một cột cụ thể được cập nhật trong MySQL?Trình kích hoạt vẫn kích hoạt nhưng bạn có thể kiểm tra xem một cột cụ thể đã được cập nhật hay chưa và sau đó chỉ chạy mã nếu cột đó đã được cập nhật. Bạn có thể làm điều này bằng cách sử dụng hàm UPDATE() bên trong trình kích hoạt của mình . Hàm này chấp nhận tên cột làm đối số của nó.
Có gì mới và cũ trong trình kích hoạt?Giới thiệu về bản ghi giả CŨ và MỚI
. Đối với trình kích hoạt CẬP NHẬT, OLD chứa các giá trị cũ và MỚI chứa các giá trị mới. Đối với trình kích hoạt XÓA, OLD chứa các giá trị cũ và MỚI không chứa giá trị nào. For an INSERT trigger, OLD contains no values, and NEW contains the new values. For an UPDATE trigger, OLD contains the old values, and NEW contains the new values. For a DELETE trigger, OLD contains the old values, and NEW contains no values. |
