Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server - Nguyễn Văn Lợi

biên dịch.Bảo mật tốt hơn: Thay vì cấp phát quyền trực tiếp cho người sử dụng trên các câu lệnh SQL và trên các đối tượng CSDL, bạn có thể cấp phát quyền cho người sử dụng thông qua các thủ tục nội tại, nhờ đó tăng khả năng bảo mật đối với hệ thống.234SQL Server 20055.2 Phân loại thủ tụcTrong SQL Server 2005 có 3 nhóm thủ tục nội tại sau: Nhóm thứ nhất là do người dùng tạo ra. Nó bao gồm hai loại:Loại thủ tục nội tại được người dùng tạo ra và lưu vào CSDL. Chúng chứa các phát biểu T-SQL.Loại thứ hai được khai báo và tạo ra bằng ngôn ngữ lập trình .NET.235SQL Server 20055.2 Phân loại thủ tụcNhóm thứ hai là thủ tục nội tại hệ thống thực hiện các chức năng quản trị CSDL thường dùng. Các thủ tục này chứa trong CSDL Resource.Danh sách các thủ tục nội tại hệ thống hiển thị trong ngăn System Stored ProcedureThủ tục nội tại trong CSDL Resource luôn có tên với tiền tố là sp_. Do đó bạn không nên đặt tên thủ tục nội tại do mình tạo ra bằng tiền tố này.236SQL Server 20055.2 Phân loại thủ tụcNhóm thứ hai là thủ tục nội tại hệ thống thực hiện các chức năng quản trị CSDL thường dùng. Các thủ tục này chứa trong CSDL Resource.Danh sách các thủ tục nội tại hệ thống hiển thị trong ngăn System Stored ProcedureThủ tục nội tại trong CSDL Resource luôn có tên với tiền tố là sp_. Do đó bạn không nên đặt tên thủ tục nội tại do mình tạo ra bằng tiền tố này.Nhóm thứ ba là thủ tục nội tại hệ thống mở rộng. Loại này cũng được lưu trong CSDL Resouce nhưng có tên bắt đầu với xp_.237SQL Server 20055.3 Tạo thủ tụcTrong khung Object Explorer, chọn Database chứa thủ tục nội tại cần tạo, chọn Programmability. Kích nút phải chuột lên mục Stored Procedures chọn New Stored Procedure, cửa sổ Query xuất hiện cho phép bạn soạn thảo câu lệnh T-SQL để tạo thủ tục. Cú pháp tạo thủ tục nội tại như sau: CREATE PROCEDURE tên_thủ_tục [(danh_sách_tham_số)][WITH RECOMPILE |ENCRYPTION ] AS Các_câu_lệnh_của_thủ_tục 238SQL Server 20055.3 Tạo thủ tụcVí dụ 1: Viết thủ tục nhập vào dữ liệu cho bảng HOCPHANVí dụ 2: Viết thủ tục thực hiện công việc sau:Chèn thêm học phần công nghệ phần mềm có mã cnpm và số tín chỉ là 3 vào bảng HOCPHANLên danh sách nhập điểm thi môn công nghệ phần mềm cho các sinh viên học lớp có mã dl01 (tức là chèn thêm vào bảng DIEMTHI các bản ghi với cột MAHOCPHAN nhận giá trị cnpm, cột MASV nhận giá trị lần lượt là mã các sinh viên học lớp có mã dl01 và các cột điểm là NULL).239SQL Server 20055.3 Lời gọi thủ tụcThực thi thủ tục bằng lời gọi thủ tục có dạng:Tên_thủ_tục [danh_sách_các_đối_số]Lời gọi thủ tục được thực hiện bên trong một thủ tục khác, bên trong một trigger hay kết hợp với các câu lệnh SQL khác, sử dụng cú pháp :EXECUTE|EXEC tên_thủ_tục [danh_sách_các_đối_số]240SQL Server 20055.3 Lời gọi thủ tụcThứ tự của các đối số được truyền cho thủ tục có thể không cần phải tuân theo thứ tự của các tham số như khi định nghĩa thủ tục nếu tất cả các đối số được viết dưới dạng: @tên_tham_số = giá_trị241SQL Server 20055.4 Lời gọi thủ tụcVí dụ: Cách 1:spLenDanhSachDiem 'cnpm','Công nghệ phầm mềm',3,'dl01' Cách 2: spLenDanhSachDiem @malop='dl01',@tenhocphan='Công nghệ phần mềm',@mahocphan='cnpm', @sotinchi=3242SQL Server 20055.5 Khai báo và sử dụng biến5.5.1 Khai báo biếnDECLARE @tên_biến kiểu_dữ_liệuTên biến phải bắt đầu bởi ký tự @ và tuân theo qui tắc về định danh.Tất cả các biến sau khi khai báo sẽ có giá trị khởi tạo là Null.Ví dụ:DECLARE @hodem NVARCHAR(30) 243SQL Server 20055.5.2 Phát biểu SetPhát biểu Set dùng để gán giá trị cho các biến. Cú pháp của phát biểu Set như sau:SET =|()Bạn có thể sử dụng nhiều phát biểu SET trên cùng một dòng bằng cách sử dụng dấu chấm phẩy để phân cách.Set @Tong=0; Set @dem=0Set @max=(Select max(diemlan1) from DIEMTHI where mahocphan='sql')244SQL Server 20055.5.2 Phát biểu SetChú ý: Nếu sử dụng phát biểu SET với phát biểu SELECT, bạn bảo đảm phát biểu SELECT này trả về giá trị đơn. Nếu phát biểu SELECT trả về nhiều giá trị thì lỗi sẽ phát sinh. 245SQL Server 20055.5.3 Phát biểu Select để gán giá trịMột trong những điểm mạnh của phát biểu Select khi sử dụng để gán giá trị cho biến là cùng một lúc có thể lấy giá trị từ CSDL và gán vào nhiều biến.Ví dụ:Select @max=10, @min=0, @tong=0Select @max= max(diemlan1), @min=min(diemlan1) from DIEMTHI where mahocphan='sql' 246SQL Server 20055.6 Giá trị trả về của tham số trong thủ tụcXét câu lệnh sau đâyCREATE PROCEDURE sp_Conghaiso(@a INT,@b INT, @c INT)AS SELECT @c=@a+@bThực thi một tập các câu lệnh như sau:DECLARE @tong INTSELECT @tong=0EXECUTE sp_Conghaiso 100,200,@tongSELECT @tongKết quả tong=0247SQL Server 20055.6 Giá trị trả về của tham số trong thủ tụcNếu muốn giữ lại giá trị của đối số sau khi kết thúc thủ tục, bạn phải khai báo tham số theo cú pháp như sau:@tên_tham_số kiểu_dữ_liệu OUTPUThoặc:@tên_tham_số kiểu_dữ_liệu OUT Và trong lời gọi thủ tục, sau đối số được truyền cho thủ tục, bạn cũng phải chỉ định thêm từ khoá OUTPUT (hoặc OUT)248SQL Server 20055.6 Giá trị trả về của tham số trong thủ tụcĐịnh nghĩa lại thủ tục ở ví dụ trên như sau:CREATE PROCEDURE sp_Conghaiso(@a INT, @b INT, @c INT OUTPUT)ASSELECT @c=@a+@b249SQL Server 20055.6 Giá trị trả về của tham số trong thủ tụcThực hiện lời gọi thủ tục trong một tập các câu lệnh như sau:DECLARE @tong INTSELECT @tong=0EXECUTE sp_Conghaiso 100,200, @tong OUTPUTSELECT @tongThì câu lệnh “SELECT @tong” sẽ cho kết quả là: 300250SQL Server 20055.7 Cấu trúc điều khiển5.7.1 Cấu trúc If...Else5.7.2 Cấu trúc While5.7.3 Phát biểu Continue5.7.4 Phát biểu Break5.7.5 Phát biểu Return5.7.6 Cấu trúc Try...Catch5.7.7 Cấu trúc Case251SQL Server 20055.7.1 Cấu trúc If...ElseCấu trúc như sau :IF ELSE Trong Cấu trúc If...Else, nếu có từ hai lệnh trở lên thì phải đặt giữa hai từ khóa Begin và End.252SQL Server 20055.7.1 Cấu trúc If...ElseVí dụ 1: Viết thủ tục đưa vào một masv. Nếu sinh viên đó là Nam thì hiện ra câu thông báo “Chúc anh sức khỏe” ngược lại nếu sinh viên đó là Nữ hiện ra câu thông báo “Chúc chị sức khỏe”.Ví dụ 2: Viết thủ tục yêu cầu đưa vào hai mã sinh viên và in ra câu ai sinh trước hơn ai.253SQL Server 20055.7.2 Cấu trúc WhileCấu trúc điều khiển While cho phép chúng ta lặp lại thực thi tập lệnh cho đến khi biểu thức điều kiện là False. Cấu trúc Cấu trúc điều khiển While như sau:WHILE 254SQL Server 2005Ví dụ: Viết thủ tục in ra tổng của các số từ 1..n.CREATE PROCEDURE Tinhtong(@n int)ASBEGIN Declare @tong int Declare @i int set @tong=0 set @i=0 While @i}END TRYBEGIN CATCH     { }END CATCH[ ; ]264SQL Server 20055.7.6 Cấu trúc Try...CatchMột số thông tin về lỗi:ERROR_NUMBER(): Trả về mã số lỗi.ERROR_SEVERITY(): Trả về mức độ của lỗi.ERROR_STATE(): Mã trạng thái của lỗi.ERROR_PROCEDURE(): Trả về tên của thủ tục hay trigger xuất hiện lỗi.ERROR_LINE(): Trả về số dòng bên trong thủ tục xuất hiện lỗi.ERROR_MESSAGE(): Trả về dòng văn bản thông báo lỗi một cách đầy đủ.Các hàm này trả về Null nếu nó được gọi bên ngoài của khối Catch.265SQL Server 2005Ví dụ: Điều khiển lỗi trong thủ tục chia hai sốCREATE PROCEDURE phepchia(@sobichia float, @sochia float)ASBEGIN declare @thuong float begin try set @thuong=@sobichia/@sochia print @thuong end try begin catch SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; Print N'Số chia bằng không' end catchEND266SQL Server 20055.7.6 Cấu trúc Try...CatchKhi thực thi thủ tục trên:phepchia 3 , 0Kết quả trình bày trong ngăn Results và ngăn Messages như sau:267SQL Server 20055.7.7 Cấu trúc CaseCấu trúc này có cú pháp như sau:CASE biểu_thức  WHEN biểu_thức_kiểm_tra THEN kết_quả  [ ... ]   [ELSE kết_quả_của_else] END268SQL Server 20055.7.7 Cấu trúc CaseCấu trúc này có cú pháp như sau:Hoặc: CASE WHEN điều_kiện THEN kết_quả        [ ... ] [ELSE kết_quả_của_else] END269SQL Server 20055.7.7 Phát biểu CaseVí dụ 1: Để hiển thị mã, họ tên và giới tính (nam hoặc nữ) của các sinh viên, bạn sử dụng câu lệnhSELECT masv,hodem,ten, CASE gioitinh WHEN 1 THEN N'Nam' ELSE N'Nữ' END AS gioitinh FROM sinhvien270SQL Server 20055.7.7 Cấu trúc CaseHoặc: SELECT masv,hodem,ten, CASE WHEN gioitinh=1 THEN N'Nam' ELSE N'Nữ' END AS gioitinh FROM sinhvien271SQL Server 20055.7.7 Cấu trúc CaseVí dụ 2: Nhập vào một masv, mahocphanNếu Điểm lần 1>=9 thì hiển thị ra câu thông báo: Bạn “ Họ ten sinh vien” học học phần “ tên học phần” xuất sắcNếu 9>Điểm >=8 hiển thị giỏiNếu 8>Điểm >=7 hiển thị kháNếu 7>Điểm >=5 hiển thị trung bìnhĐiểm] Order By [DESC|ASC]) 303SQL Server 2005Hàm RankVí dụ: Hiển thị danh sách điểm thi lần 1 học phần có mã là Tinvp. Trong kết quả trả về có hiển thị thêm cột xếp vị thứ dựa vào điểm thi lần 1. SELECT DT.MASV, HODEM,TEN,GIOITINH, DIEM, RANK() OVER (ORDER BY DIEM DESC) AS [VI THU]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASVWHERE MAHOCPHAN='TINVP' AND LANTHI=1 304SQL Server 2005Hàm RankKết quả 305SQL Server 2005Hàm RankVí dụ: Hiển thị danh sách điểm thi lần 1 học phần có mã là Tinvp. Kết quả trả về hiển thị thêm cột xếp vị thứ theo từng nhóm giới tính dựa vào điểm thi lần 1.SELECT DT.MASV, HODEM,TEN,GIOITINH,DIEM, RANK() OVER (PARTITION BY GIOITINH ORDER BY DIEM DESC) AS [VI THU]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASVWHERE MAHOCPHAN='TINVP' AND LANTHI=1306SQL Server 2005Hàm RankKết quả 307SQL Server 2005Hàm Dense_Ranktương tự hàm Rank, tuy nhiên các giá trị trên cột vị thứ có giá trị liên tục. Dense_RankRank308SQL Server 2005Hàm NtileHàm này tương tự hàm Rank, tuy nhiên các giá trị trên cột vị thứ được giới hạn trong phạm vi chỉ định.Hàm NTILE chỉ có tác dụng chia đều số lượng các bản ghi và đưa vào từng nhóm số.Ví dụ: SELECT DT.MASV, HODEM,TEN,GIOITINH,DIEM, NTILE(2) OVER (ORDER BY DIEM DESC) AS [VI THU]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASV 309SQL Server 2005Hàm NtileSử dụng hàm NTILE cho từng bản ghi trong một nhóm sẽ đưa ra các xếp loại giống nhau. Ví dụ: SELECT DT.MASV, HODEM,TEN,GIOITINH,DIEM, NTILE(3) OVER (PARTITION BY GIOITINH ORDER BY DIEM DESC) AS [VI THU]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASV310SQL Server 2005Hàm Row_NumberTrả về số thứ tự của cho bản ghi trong tập bản ghi mà phát biểu SELECT trả về. Ví dụ:SELECT DT.MASV, HODEM, TEN, GIOITINH , DIEM, Row_Number () OVER (ORDER BY DIEM) AS [SO MAU TIN]FROM DIEMTHI DT INNER JOIN SINHVIEN SV ON DT.MASV=SV.MASV311SQL Server 20056.3 Hàm do người dùng định nghĩa6.3.1 Tạo hàm6.3.2 Thay đổi hàm312SQL Server 20056.3.1 Tạo hàm6.3.1.1 Tạo hàm trả về giá trị đơn Kích nút phải chuột lên mục Scalar - valued Functions chọn New Scalar - valued Function, cửa sổ Query xuất hiện cho phép bạn soạn thảo câu lệnh T-SQL để tạo hàm. Cú pháp tạo hàm trả về giá trị đơn như sau:CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) RETURNS (kiểu_trả_về_của_hàm)ASBEGIN Khai báo biến kết quả trả về các_câu_lệnh_của_hàm RETURN kết quả trả về của hàm END313SQL Server 20056.3.1.1 Tạo hàm trả về giá trị đơnVí dụ 1: Định nghĩa hàm tính thứ trong tuần của một giá trị kiểu ngày CREATE FUNCTION thu(@ngay DATETIME)RETURNS NVARCHAR(10)AS BEGIN DECLARE @st NVARCHAR(10) SELECT @st=CASE DATEPART(DW,@ngay) WHEN 1 THEN N'Chủ nhật' WHEN 2 THEN N'Thứ hai' WHEN 3 THEN N'Thứ ba' WHEN 4 THEN N'Thứ tư' WHEN 5 THEN N'Thứ năm' WHEN 6 THEN N'Thứ sáu' ELSE N'Thứ bảy' END RETURN (@st) /* Trị trả về của hàm */ END314SQL Server 20056.3.1.1 Tạo hàm trả về giá trị đơnMột hàm khi đã được định nghĩa có thể sử dụng như các hàm do HQTCSDL cung cấp (thông thường trước tên hàm, bạn phải chỉ định thêm tên của người sở hữu hàm)Ví dụ: Câu lệnh dưới đây sử dụng hàm đã được định nghĩa ở ví dụ trước:SELECT masv, hodem, ten, dbo.thu(ngaysinh) as Thu,ngaysinhFROM sinhvien WHERE malop='dl01' 315SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm nội tuyến trả về dữ liệu kiểu bảng (Inline Table- valued Function):Cú pháp:CREATE FUNCTION tên_hàm ([danh_sách_tham_số])RETURNS TABLE AS RETURN (câu_lệnh_select) 316SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm nội tuyến trả về dữ liệu kiểu bảng:Ví dụ: Định nghĩa hàm func_XemSV:CREATE FUNCTION func_XemSV(@khoahoc SMALLINT)RETURNS TABLEAS RETURN(SELECT masv,hodem,ten,ngaysinh FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE khoahoc=@khoahoc)317SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm nội tuyến trả về dữ liệu kiểu bảng:Ví dụ: Định nghĩa hàm func_XemSV:Để biết danh sách các sinh viên khoá 1, bạn sử dụng câu lệnh như sau:SELECT * FROM dbo.func_XemSV(1)318SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm chứa nhiều lệnh trả về dữ liệu kiểu bảng (Multi-Statement Table-valued Function ):CREATE FUNCTION tên_hàm([danh_sách_tham_số])RETURNS @biến_bảng TABLE định_nghĩa_bảngAS BEGIN     các_câu_lệnh_trong_thân_hàm     RETURN END319SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm chứa nhiều lệnh trả về dữ liệu kiểu bảng (Multi-Statement Table-valued Function ):Ví dụ: Định nghĩa hàm func_TongSV (@khoahoc SMALLINT) như sau: Hiển thị makhoa, tenkhoa, tongsosvNếu @khoahoc=0 thì cho biết tổng số sinh viên hiện có (tất cả các khóa) của mỗi khoa.Ngược lại Sẽ cho kết quả thống kê tổng số sinh viên khoá @khoahoc của mỗi khoa320SQL Server 20056.3.1.2 Tạo hàm trả về dữ liệu kiểu bảng Hàm chứa nhiều lệnh trả về dữ liệu kiểu bảng (Multi-Statement Table-valued Function ):Thực hiện lệnh:SELECT * FROM dbo.func_TongSV(1)HoặcSELECT * FROM dbo.func_TongSV(0) 321SQL Server 20056.3.2 Thay đổi hàmTrong khung Object Explorer, chọn Database chứa hàm cần sửa đổi, chọn Programmability/ Function.Kích nút phải chuột lên hàm cần sửa đổi chọn Modify.Cửa sổ Query xuất hiện chứa lệnh ALTER FUNCTION 322SQL Server 2005Chương 7TRIGGER7.1 DML Trigger 7.1.1 Giới thiệu DML Trigger 7.1.2 Tạo DML Trigger 7.2 DDL Trigger 7.2.1 Giới thiệu DDL Trigger7.2.2 Tạo DDL Trigger 7.2.3 Hàm Eventdata323SQL Server 20057.1 DML Trigger7.1.1 Giới thiệu DML (Data Manipulation Language) Trigger Dùng để kiểm soát sự dữ liệu thay đổi.trigger loại này phải được gắn liền với một bảng nào đó trong CSDL.Khi dữ liệu trong bảng bị thay đổi (INSERT, UPDATE hay DELETE) thì trigger sẽ được tự đông kích hoạt.DML Trigger được sử dụng trong việc 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.324SQL Server 20057.1 DML Trigger7.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.325SQL Server 20057.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.326SQL Server 20057.1.2 Tạo DML TriggerTạo Trigger theo cấu trúc sau:CREATE TRIGGER tên_triggerON tên_bảngFOR {[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_trigger327SQL Server 20057.1.2.1 Tạo Trigger cho hành động thêm bản ghiSQL 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: 328SQL Server 20057.1.2.1 Tạo Trigger cho hành động thêm bản ghiKhi 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. 329SQL Server 20057.1.2.1 Tạo Trigger cho hành động thêm bản ghiVí 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’.330SQL Server 20057.1.2.1 Tạo Trigger cho hành động thêm bản ghiCREATE TRIGGER trg_insert_tblLOP ON LOP AFTER INSERTASBEGIN 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=@malopEND 331SQL Server 20057.1.2 Tạo DML Trigger7.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 đó.332SQL Server 20057.1.2 Tạo DML TriggerCREATE TRIGGER Trg_Update_diemlan1ON DIEMTHI AFTER UPDATEASBEGIN 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=2END333SQL Server 20057.1.2.3 Tạo Trigger cho hành động xóa bản ghiVí 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_DIEMTHION DIEMTHI AFTER DELETEASBEGIN INSERT INTO DELETED_DIEMTHI SELECT * FROM DELETEDEND334SQL Server 20057.1.2.4 Sử dụng mệnh đề IF UPDATESử 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.335SQL Server 20057.1.2.4 Sử dụng mệnh đề IF UPDATEVí dụ 7.4: CREATE TRIGGER trg_nhatkybanhang_update_soluongON nhatkybanhangFOR UPDATEASIF 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.mahang336SQL Server 20057.1.2.5 Lệnh ROLLBACK TRANSACTIONMộ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 337SQL Server 20057.1.2.5 Lệnh ROLLBACK TRANSACTIONVí 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.338SQL Server 20057.2 DDL Trigger 7.2.1 Giới thiệu DDL TriggerDDL (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 2005339SQL Server 20057.2 DDL Trigger 7.2.1 Giới thiệu DDL TriggerSau 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.340SQL Server 20057.2 DDL Trigger 7.2.1 Giới thiệu DDL TriggerMụ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.341SQL Server 20057.2 DDL Trigger 7.2.2 Tạo DDL TriggerTạo DDL Trigger theo cấu trúc sau:CREATE TRIGGER tên_triggerON DATABASE|ALL SERVERFOR {[ Các sự kiện DDL] [nhóm các sự kiện]} AS các_câu_lệnh_của_trigger342SQL Server 20057.2 DDL Trigger 7.2.2 Tạo DDL TriggerVí dụ 7.8:Ví dụ: Tạo Trigger không cho tạo Database 343SQL Server 20057.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. 344SQL Server 2005Chương 8: BẢO MẬT VÀ QUẢN TRỊ NGƯỜI DÙNG 8.1 Chứng thực người dùng8.2 Gán quyền cho người dùng8.3 Bảo trì cơ sở dữ liệu 345SQL Server 20058.1 Chứng thực người dùngChứ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.346SQL Server 20058.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. 347SQL Server 20058.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.348SQL Server 20058.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 BrowserCấ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.349SQL Server 20058.1.2 Cách tạo đăng nhập8.1.2.1 Tạo đăng nhập Windows8.1.2.2 Tạo đăng nhập SQL Server8.1.2.2 Đăng nhập với User sa 350SQL Server 20058.1.3 Nhóm (Roles)8.1.3.1 Giới thiệuRoles 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.351SQL Server 20058.1.3 Nhóm (Roles)8.1.3.1 Giới thiệuSQL 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 Server352SQL Server 20058.1.3 Nhóm (Roles)8.1.3.1 Giới thiệuSQL 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 CSDL353SQL Server 20058.1.3 Nhóm (Roles)8.1.3.1 Giới thiệuSQL 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. 354SQL Server 20058.1.3 Nhóm (Roles)8.1.3.2 Thêm người dùng vào nhóm quyền Server8.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 355SQL Server 20058.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.356SQL Server 20058.2 Gán quyền cho người dùng8.2.1 Tạo người dùng CSDL8.2.2 Quản lý quyền trên đối tượng8.2.3 Quản lý quyền trên đối tượng 357SQL Server 20058.3 Bảo trì cơ sở dữ liệu 8.3.1 Sao lưu dự phòng8.3.2 Khôi phục cơ sở dữ liệu 358SQL Server 2005