Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server

àn vẹn dữ liệu theo quy tắc xác định, được quản lý theo bảng dữ liệu hoặc khung nhìn. Sử dụng DML trigger một cách hợp lý trong CSDL sẽ có tác động rất lớn trong việc tăng hiệu năng của CSDL. 7.1 DML Trigger 7.1.1 Giới thiệu DML Trigger Ý nghĩa: Bảo đảm toàn vẹn dữ liệu theo quy tắc xác định, được quản lý theo bảng dữ liệu hoặc khung nhìn. Sử dụng DML trigger một cách hợp lý trong CSDL sẽ có tác động rất lớn trong việc tăng hiệu năng của CSDL. 7.1.1 Giới thiệu DML Trigger Thực sự hữu dụng với những khả năng: Nhận biết, ngăn chặn và huỷ bỏ được những thao tác làm thay đổi trái phép dữ liệu trong CSDL. Các thao tác trên dữ liệu có thể được trigger phát hiện ra và tự động thực hiện một loạt các thao tác khác trên CSDL nhằm đảm bảo tính hợp lệ của dữ liệu. Thông qua DML trigger, bạn có thể tạo và kiểm tra được những mối quan hệ phức tạp hơn giữa các bảng trong CSDL mà bản thân các ràng buộc không thể thực hiện được. 7.1.2 Tạo DML Trigger Tạo Trigger theo cấu trúc sau: CREATE TRIGGER tên_trigger ON tên_bảng FOR {[INSERT][,][UPDATE][,][DELETE]} 	AS 	[IF UPDATE(tên_cột) 	 [AND UPDATE(tên_cột)|OR UPDATE(tên_cột)] 	...]	 	các_câu_lệnh_của_trigger 7.1.2.1 Tạo Trigger cho hành động thêm bản ghi SQL Server định nghĩa hai bảng logic INSERTED và DELETED để sử dụng trong các trigger. Cấu trúc của hai bảng này tương tự như cấu trúc của bảng mà trigger tác động. Dữ liệu trong hai bảng này tùy thuộc vào câu lệnh tác động lên bảng làm kích hoạt trigger, cụ thể trong các trường hợp sau: 7.1.2.1 Tạo Trigger cho hành động thêm bản ghi Khi câu lệnh DELETE được thực thi trên bảng, các dòng dữ liệu bị xoá sẽ được sao chép vào trong bảng DELETED. Bảng INSERTED trong trường hợp này không có dữ liệu. Dữ liệu trong bảng INSERTED sẽ là dòng dữ liệu được chèn vào bảng gây nên sự kích hoạt đối với trigger bằng câu lệnh INSERT. Bảng DELETED trong trường hợp này không có dữ liệu. Khi câu lệnh UPDATE được thực thi trên bảng, các dòng dữ liệu cũ chịu sự tác động của câu lệnh sẽ được sao chép vào bảng DELETED, còn trong bảng INSERTED sẽ là các dòng sau khi đã được cập nhật. 7.1.2.1 Tạo Trigger cho hành động thêm bản ghi Ví dụ: Tạo Trigger thực hiện công việc, khi người dùng nhập bản ghi vào bảng LOP, Nếu cột dữ liệu hedaotao là rỗng thì hệ thống tự động gán giá trị của cột này bằng giá trị là ‘Chính quy’. 7.1.2.1 Tạo Trigger cho hành động thêm bản ghi CREATE TRIGGER trg_insert_tblLOP ON LOP AFTER INSERT AS BEGIN 	DECLARE @hedaotao nvarchar(25) 	DECLARE @malop nvarchar(10) 	SELECT @hedaotao=hedaotao, @malop= malop 	FROM INSERTED 	IF @hedaotao='' or @hedaotao is null 	UPDATE LOP 	SET hedaotao='Chính quy' 	Where malop=@malop END 7.1.2 Tạo DML Trigger 7.1.2.2 Tạo Trigger cho hành động cập nhật bản ghi: Ví dụ: Tạo Trigger thực hiện công việc, khi người dùng cập nhật điểm lần 1 cho các bản ghi của bảng DIEMTHI, Nếu sau khi cập nhật cột dữ liệu điểm lần 1 có giá trị >=5 thì thì hệ thống tự động xóa dòng điểm thi lần 2 của sinh viên thi học phần đó. 7.1.2 Tạo DML Trigger CREATE TRIGGER Trg_Update_diemlan1 ON DIEMTHI AFTER UPDATE AS BEGIN DECLARE @diemlan1 numeric(5,2) DECLARE @masv nvarchar(10) DECLARE @mahocphan nvarchar(10) SELECT @masv=masv, @mahocphan=mahocphan, @diemlan1=diem FROM INSERTED WHERE lanthi=1 IF @diemlan1>=5 DELETE FROM DIEMTHI 	 WHERE masv=@masv and mahocphan=@mahocphan and lanthi=2 END 7.1.2.3 Tạo Trigger cho hành động xóa bản ghi Ví dụ: Tạo Trigger thực hiện lưu các mẫu tin bị xóa từ bảng DIEMTHI vào bảng DELECTED_DIEMTHI). CREATE TRIGGER Trg_Delete_DIEMTHI ON DIEMTHI AFTER DELETE AS BEGIN 	INSERT INTO DELETED_DIEMTHI 	SELECT * FROM DELETED END 7.1.2.4 Sử dụng mệnh đề IF UPDATE Sử dụng mệnh đề IF UPDATE trong trigger nếu muốn trigger được kích hoạt khi việc thay đổi dữ liệu chỉ liên quan đến một số cột nhất định nào đó của bảng. IF UPDATE không sử dụng được đối với câu lệnh DELETE. 7.1.2.4 Sử dụng mệnh đề IF UPDATE Ví dụ 7.4: CREATE TRIGGER trg_nhatkybanhang_update_soluong ON nhatkybanhang FOR UPDATE AS IF UPDATE(soluong) UPDATE mathang SET mathang.soluong = mathang.soluong – 	 (inserted.soluong-deleted.soluong) FROM (deleted INNER JOIN inserted ON deleted.stt = inserted.stt) INNER JOIN mathang 	 ON mathang.mahang = deleted.mahang 7.1.2.5 Lệnh ROLLBACK TRANSACTION Một trigger có khả năng nhận biết được sự thay đổi về mặt dữ liệu trên bảng dữ liệu, từ đó có thể phát hiện và huỷ bỏ những thao tác không đảm bảo tính toàn vẹn dữ liệu. Trong một trigger, để huỷ bỏ tác dụng của câu lệnh làm kích hoạt trigger, bạn sử dụng câu lệnh: ROLLBACK TRANSACTION 7.1.2.5 Lệnh ROLLBACK TRANSACTION Ví dụ 7.7: Ví dụ: Tạo Trigger để đảm bảo ràng buộc số học phần thi lại của một sinh viên không được vượt quá 2. 7.2 DDL Trigger 7.2.1 Giới thiệu DDL Trigger DDL (Data Definition Language) Trigger được kích hoạt khi người sử dụng làm thay đổi cấu trúc CSDL hay đối tượng CSDL bằng các phát biểu SQL thuộc DDL như: Create, Alter, Drop, Grant, Deny, Revoke,… Nếu DML Trigger dùng để kiểm soát dữ liệu chứa trong Table hay View thì DDL Trigger có thể được sử dụng cho chức năng quản trị CSDL. Đây là loại Trigger mới xuất hiện trong SQL Server 2005 7.2 DDL Trigger 7.2.1 Giới thiệu DDL Trigger Sau khi giai đoạn thiết kế CSDL hoàn tất, để kiểm soát mọi sự thay đổi cấu trúc của CSDL, chúng ta cần sử dụng loại Trigger này. Như vậy, mục đích của DDL Trigger là ngăn ngừa sự thay đổi cấu trúc CSDL. Ngoài ra, bạn có thể ghi lại những hành động làm thay đổi cấu trúc CSDL khi sử dụng DDL Trigger. 7.2 DDL Trigger 7.2.1 Giới thiệu DDL Trigger Mục đích của DDL Trigger là kiểm soát mọi sự thay đổi cấu trúc của CSDL. Có thể ghi lại những hành động làm thay đổi cấu trúc CSDL. 7.2 DDL Trigger 7.2.2 Tạo DDL Trigger Tạo DDL Trigger theo cấu trúc sau: CREATE TRIGGER tên_trigger ON DATABASE|ALL SERVER FOR {[ Các sự kiện DDL] [nhóm các sự kiện]} 	AS 	các_câu_lệnh_của_trigger 7.2 DDL Trigger 7.2.2 Tạo DDL Trigger Ví dụ 7.8: Ví dụ: Tạo Trigger không cho tạo Database 7.2 DDL Trigger 7.2.3 Hàm Eventdata Thông tin về những sự kiện làm kích hoạt DDL trigger được lưu lại trong trong hàm Eventdata. Hàm này trả về một giá trị kiểu xml. Lược đồ xml này bao gồm các thông tin sau: Thời gian của sự kiện. Định danh xử lý hệ thống (The System Process ID: SPID) của kết nối trong lúc trigger thực hiện được thực hiện. Kiểu sự kiện đã kích hoạt trigger Tùy thuộc vào kiểu sự kiện, lược đồ bao gồm thêm những thông tin như: database nơi sự kiện xuất hiện, đối tượng bị tác động khi sự kiện xuất hiện và câu lệnh T-SQL của sự kiện. Chương 8: BẢO MẬT VÀ QUẢN TRỊ NGƯỜI DÙNG 8.1 Chứng thực người dùng 8.2 Gán quyền cho người dùng 8.3 Bảo trì cơ sở dữ liệu 8.1 Chứng thực người dùng Chứng thực (Authentication) nhằm đảm bảo biết được người dùng là ai. Sau khi SQL Server chứng thực người dùng, họ có thể thực hiện bất kỳ hành động được phép nào với đăng nhập của họ cũng như những hành động được phép với nhóm mà họ là thành viên. 8.1.1 Các kiểu chứng thực Có hai kiểu chứng thực: SQL Server and Windows Authentication: Đây là kiểu chứng thực hỗn hợp. SQL Server hỗ trợ trên cả đăng nhập SQL Server và Windows. Windows Authentication: Kiểu chứng thực này SQL Server chỉ hỗ trợ đăng nhập Windows. 8.1.1 Các kiểu chứng thực Thay đổi kiểu chứng thực: Trong cửa sổ Object Explorer, nhấn phải chuột lên Server bạn muốn thay đổi kiểu chứng thực, chọn Properties. Chọn nút Security Ở phần Server Authentication chọn lại kiểu chứng thực là Windows Authentication mode hoặc SQL Server and Windows Authentication mode. Nhấn OK. 8.1.1 Các kiểu chứng thực Lưu ý: Mặc định, phiên bản SQL Server 2005 Express Edition và phiên bản SQL Server 2005 Developer Edition không cho phép các kết nối từ xa. Để cấu hình SQL Server 2005 chấp nhận các kết nối từ xa cần thực hiện các bước sau: Cho phép tiếp nhận các kết nối từ xa trên thể hiện của SQL Server mà các ứng dụng máytrạm cần kết nối. Kích hoạt dịch vụ SQL Server Browser Cấu hình tường lửa cho phép các dữ liệu liên quan đến SQL Server và dịch vụ SQL Server Browser được lưu thông trên mạng. 8.1.2 Cách tạo đăng nhập 8.1.2.1 Tạo đăng nhập Windows 8.1.2.2 Tạo đăng nhập SQL Server 8.1.2.2 Đăng nhập với User sa 8.1.3 Nhóm (Roles) 8.1.3.1 Giới thiệu Roles trong SQL Server tương đương với Group trong Windows, gọi chung là nhóm. Bạn tạo nhóm, sau đó cấp quan hệ thành viên của người dùng cho nhóm. Người dùng là thành viên của nhóm sẽ thừa hưởng quyền được cấp cho nhóm. 8.1.3 Nhóm (Roles) 8.1.3.1 Giới thiệu SQL Server phân ra 4 loại nhóm: Nhóm quyền Server (Server Roles): Nhóm này được xây dựng sẵn trong SQL Server và người dùng không thể thay đổi được (thêm, sửa đổi hoặc xóa). Nhóm này cho phép người dùng thành viên thực hiện các tác vụ quản trị cấp Server. Bạn tìm thấy nhóm quyền này ở nút Security cấp Server 8.1.3 Nhóm (Roles) 8.1.3.1 Giới thiệu SQL Server phân ra 4 loại nhóm: Nhóm quyền CSDL (Database Roles): Nhóm quyền CSDL cho phép bạn gán quyền cho người dùng ở cấp CSDL. Nó cung cấp các quyền liên quan đến CSDL 8.1.3 Nhóm (Roles) 8.1.3.1 Giới thiệu SQL Server phân ra 4 loại nhóm: Nhóm quyền CSDL do người dùng định nghĩa: Ở cấp CSDL, bạn không bị hạn chế với các nhóm quyền đã định nghĩa trước. Bạn có thể tự định nghĩa nhóm quyền của riêng bạn. SQL Server phân ra 2 loại nhóm quyền do người dùng định nghĩa: Nhóm quyền chuẩn: Dùng cho các tác vụ gán quyền tới CSDL. Nhóm quyền ứng dụng: Dùng cho các ứng dụng. Nhóm quyền ứng dụng: Các quyền liên quan đến ứng dụng. 8.1.3 Nhóm (Roles) 8.1.3.2 Thêm người dùng vào nhóm quyền Server 8.1.3.3 Thêm người dùng vào nhóm quyền CSDL 8.1.3.4 Cách tạo nhóm quyền do người dùng tự định nghĩa 8.2 Gán quyền cho người dùng Chứng thực người dùng là quá trình đảm bảo chỉ có người dùng hợp lệ mới được phép làm việc với CSDL. Sau khi người dùng đạt được truy cập vào CSDL, điều quan trọng là họ có các quyền cụ thể gì với các đối tượng trong CSDL. 8.2 Gán quyền cho người dùng 8.2.1 Tạo người dùng CSDL 8.2.2 Quản lý quyền trên đối tượng 8.2.3 Quản lý quyền trên đối tượng 8.3 Bảo trì cơ sở dữ liệu 8.3.1 Sao lưu dự phòng 8.3.2 Khôi phục cơ sở dữ liệu