Chương 5. Trigger

08/02/2012 1 1 Chương 5. Trigger NỘI DUNG 2  Giới thiệu  Các yêu cầu của ràng buộc  Trigger 08/02/2012 2 GiỚI THIỆU 3 Trong phân tích, thiết kế CSDL ràng buộc toàn vẹn là mối quan hệ ràng buộc trên các đối tượng, quan hệ, các thuộc tính các thực thể và mối kết hợp. Mục đích: Ràng buộc toàn vẹn đảm bảo tính kết dính của các bộ phận cấu thành nên CSDL. Ràng buộc toàn vẹn bảo đảm CSDL luôn biểu diễn đúng ngữ nghĩa trong thực tế ở mọi thời điểm. GIỚI THIỆU 4 Người dùng có thể cài đặt ràng buộc trong các hoạt động sau: Câu lệnh thuộc ngôn ngữ DML Câu lệnh thuộc ngôn ngữ DDL Các sự kiện liên quan đến CSDL như logon, logoff, startup, shutdown 08/02/2012 3 Các yêu cầu của ràng buộc toàn vẹn 5  Bối cảnh: Một hay nhiều quan hệ có thể bị vi phạm  Nội dung ràng buộc toàn vẹn được biểu diễn bằng:  Ngôn ngữ tự nhiên  Ngôn ngữ hình thức  Bảng tầm ảnh hưởng: xác định khi nào cẩn kiểm tra RBTV  Hành động: phản hồi của RBTV nếu bị vi phạm Các yêu cầu của ràng buộc toàn vẹn (tt) 6  Ví dụ: điểm thi cuối khóa nhận giá trị từ 0 đến 100 Bối cảnh: ENROLLMENT Nội dung: e  ENROLLMENT: e.final_grade>=0 and e.final_grade<=100 Bảng tầm ảnh hưởng: Quan hệ Thêm Xóa Sửa ENROLLMENT + - +(final_grade) 08/02/2012 4 Trigger 7 Mục đích của trigger Đáp ứng các quy tắc nghiệp vụ phức tạp mà không thể sử dụng các ràng buộc toàn vẹn để thay thế. Quản lý các quy tắc an toàn phức tạp. Tự động tạo ra các giá trị cho các cột nhận giá trị tính toán từ các cột khác. Ngăn chặn các thao tác không hợp lệ. Cung cấp cách kiểm tra các giá trị. Trigger 8  Cú pháp: CREATE [OR REPLACE] trigger_name BEFORE | AFTER INSERT OR UPDATE OR DELETE ON table_name FOR EACH ROW DECLARE Các khai báo BEGIN Các phát biểu thực thi EXCEPTION Các phát biểu xử lý ngoại lệ END; • Chú ý: trong trigger không dùng kiểu dữ liệu Long để khai báo 08/02/2012 5 Trigger 9  BEFORE:  Cập nhật các gia ́ trị trong record gia ̉ :new  Không cập nhật gia ́ trị trong record gia ̉ :old  AFTER:  Không cập nhật gia ́ trị trong record gia ̉ :new  Không cập nhật gia ́ trị trong record gia ̉ :old Trigger 10 create or replace trigger student_bi before insert on student for each row declare v_student_id student.student_id%type; begin select student_id_seq.nextval into v_student_id from dual; :new.student_id:=v_student_id; :new.created_by:=user; :new.created_date:=sysdate; :new.modified_by:=user; :new.modified_date:=sysdate; end; 08/02/2012 6 Trigger 11 insert into student(first_name,last_name,zip,registration_date) values('nguyen','le','00914','13-2-2012'); Trigger 12  Để tập hợp các thông tin thống kê, ghi lại người nào xóa và update record trên bảng Instructor. Tạo bảng thống kê và trigger sau: create table statistic ( table_name varchar2(20), transaction_name varchar2(10), transaction_user varchar2(30), transaction_date date ) 08/02/2012 7 Trigger 13 create or replace trigger instructor_aud after update or delete on instructor declare v_type varchar2(10); begin if updating then v_type:='Update'; else v_type:='Delete'; end if; ... Trigger 14 create or replace trigger instructor_aud after update or delete on instructor ... update statistic set transaction_user=user, transaction_date=sysdate where table_name='instructor' and transaction_name=v_type; if sql%notfound then insert into statistic values('Instructor',v_type,user,sysdate); end if; end; 08/02/2012 8 Trigger 15  Điểm thi cuối khóa nhận giá trị từ 0 đến 100 create or replace trigger tr_enrollment before insert or update on enrollment for each row begin if (:new.final_grade not between 0 and 100) then raise_application_error(-20011,'diem khong hop le'); end if; end; Trigger 16 Tăng lương mới phải lớn hơn lương cũ CREATE OR REPLACE TRIGGER TR_LUONG_TANG BEFORE UPDATE ON NHANVIEN FOR EACH ROW BEGIN IF (:new.Luong < :old.Luong) THEN RAISE_APPLICATION_ERROR(-20010, 'Luong moi phai tang chu khong giam'); ELSE dbms_output.put_line('Update thanh cong!'); END IF; END; 08/02/2012 9 Trigger 17 Số lượng đặt phải dương create or replace trigger tr_soluong_duong before insert or update on ctdonhang for each row when(new.soluongdat<=0) begin raise_application_error(-20010,'so luong dat phai duong'); end; Disable/Enable Triggers 18  Cú pháp Disable: ALTER TRIGGER trigger_name DISABLE;  Ví dụ: ALTER TRIGGER tr_enrollment DISABLE;  Cú pháp Enable ALTER TRIGGER trigger_name ENABLE;  Ví dụ: ALTER TRIGGER tr_enrollment ENABLE; 08/02/2012 10 Disable/Enable all Triggers on a table 19  Cú pháp: ALTER TABLE table_name DISABLE|ENABLE ALL TRIGGERS;  Ví dụ: ALTER TABLE enrollment DISABLE ALL TRIGGERS ALTER TABLE enrollment ENABLE ALL TRIGGERS