Bài giảng Microsoft SQL Server - Bài 12: Thủ tục lưu trú, hàm và trigger - Lê Thị Tú Kiên

 đến 
thứ tự các biến trong khai báo thủ tục). 
EXEC spInvTotal3 @VendorVar = 'M%', 
 @InvTotal = @MyInvTotal OUTPUT; 
 Lời gọi chỉ truyền giá trị cho biến tham trị tùy chọn và tham biến biến 
EXEC spInvTotal3 @MyInvTotal OUTPUT; 
 Lời gọi chỉ truyền giá trị cho tham biến biến 
14 
15 
- Thủ tục cũng có thể trả ra một giá trị kiểu số nguyên. Khi đó trong 
định nghĩa thủ tục có câu lệnh RETURN. 
- Ví dụ tạo thủ tục spInvCount: 
- Thủ tục có hai tham trị tùy chọn. 
- Nhiệm vụ của thủ tục: đếm số hóa đơn có InvoiceDate >= 
@DateVar và VendorName LIKE @VendorVar rồi gán tổng số hóa 
đơn này cho biến @InvCount. Sau đó, thủ tục thủ tục sẽ trả ra giá trị 
của biến @InvCount bằng câu lệnh Return (câu lệnh được tô vàng). 
17 
- Thủ tục cũng có thể trả ra một giá trị kiểu số nguyên. Khi đó trong 
định nghĩa thủ tục có câu lệnh RETURN. 
- Ví dụ tạo thủ tục spInvCount: 
- Thủ tục có hai tham trị tùy chọn. 
- Nhiệm vụ của thủ tục: đếm số hóa đơn có InvoiceDate >= 
@DateVar và VendorName LIKE @VendorVar rồi gán tổng số hóa 
đơn này cho biến @InvCount. Sau đó, thủ tục thủ tục sẽ trả ra giá trị 
của biến @InvCount bằng câu lệnh Return (câu lệnh được tô vàng). 
18 
Trên slide là ví dụ cách thực hiện thủ tục spInvCount ở slide trước. Giá trị trả 
ra của thủ tục được gán cho một biến @InvCount khai báo trước đó. 
19 
20 
- Ví dụ tạo thủ tục spInsertInvoice kiểm tra giá trị của một khóa ngoài. 
- Thủ tục có 6 tham trị bắt buộc (tương ứng các cột trong bảng 
Invoices. 
- Nhiệm vụ của thủ tục: kiểm tra xem đã có giá trị VendorID nào 
trong bảng Vendor có trùng với giá trị của tham biến @VendorID 
không (Kiểm tra giá trị khóa ngoài VendorID định chèn vào bảng 
Invoices đã tồn tại bên cột VendorID ở bảng Vendor chưa). Nếu tồn 
lại rồi thì cho phép chèn thêm bản ghi mới vào bảng Invoices (câu 
lệnh Insert). Nếu không (else), đưa ra thông báo lỗi “Not a valid 
VendorID!” bằng câu lệnh THROW. 
21 
Ví dụ một script có lời gọi thủ tục spInsertInvoice. Đầu tiên, câu lệnh thực 
hiện thủ tục spInsertInvoice trong khối TRY sẽ được thực hiện trước. Nếu thủ 
tục không có lỗi thì khối CATH được bỏ qua, nếu thủ tục có lỗi thì các câu 
lệnh trong khối CATCH được thực hiện để in thông báo lỗi được định nghĩa 
trong thủ tục bằng câu lệnh THROW. 
Kết quả hiện ra trong trường hợp này là thủ tục chạy bị lỗi vì có mã VendorID 
=799 của bản ghi muốn thêm vào bảng Invoices (bảng khóa ngoài) chưa tồn tại 
trong cột VendorID ở bảng Vendor (bảng khóa chính). Do đó các lệnh trong 
khối CATCH được thực hiện để in thông báo lỗi. 
22 
Chú ý: câu lệnh THROW trong khối lệnh BEGINEND phải có ; ở phía 
trước. 
23 
Từ slide 21 đến 24 là ví dụ định nghĩa thủ tục spInsertInvoice. Thủ tục này mở 
rộng của ví dụ trong slide trước. 
24 
25 
26 
27 
Ví dụ script chạy thủ tục spInsertInvoice trong slide 21-24. 
28 
Kết quả sau khi chạy script. 
29 
30 
- Cú pháp định nghĩa kiểu dưc liệu Table(bảng dữ liệu) 
- Ví dụ tạo một kiểu dữ liệu bảng LineItems. Mỗi biến thuộc kiểu dữ liệu này 
là một bảng có năm cột và cột InvoiceID và InvoiceSequence là khóa chính 
cho bảng. 
31 
Ví dụ khai báo tham biến @LineItems có kiểu dữ liệu bảng LineItems định 
nghĩa ở slide trước. 
32 
Ví dụ thực hiện thủ tục spInsertLineItems. 
33 
- Dùng câu lệnh DROP PROC để xóa một thủ tục lưu trú khỏi CSDL. 
- Dùng lệnh ALTER PROC để sửa định nghĩa một thủ tục. 
- Chú ý: khi một thủ tục bị xóa, tất cả các quyền bảo mật được gán cho thủ 
tục cũng bị xóa theo. 
34 
Silde 21 và 22 là ví dụ định nghĩa thủ tục spVendorState sau đó dùng câu lệnh 
Alter proc để sửa thủ tục và câu lệnh Drop proc để xóa thủ tục. 
35 
36 
Một số thủ tục đĩnh nghĩa sẵn trong hệ thống SQL Server 2012 
37 
Ví dụ dùng thủ tục sp_HelpText để xem định nghĩa thủ tục spInvoiceReport. 
38 
39 
Hàm do NSD định nghĩa bao gồm 3 kiểu: 
- Scalar-valued function type: hàm trả ra giá trị vô hướng (int, float, char, 
v.v). 
- Simple table-valued function: hàm trả ra giá trị là một bảng dựa trên một câu 
truy vấn Select 
- Multi-statement table-valued function: hàm trả ra giá trị là một bảng dựa trên 
nhiều câu truy vấn 
40 
- Trên cùng slide là ví dụ tạo hàm fnVendorID có giá trị trả ra thuộc kiểu vô 
hướng (int). Nhiệm vụ của hàm này là trả ra giá trị của VendorID (kiểu int) 
của vendor có VendorName = @VendorName. 
- Ở giữa là ví dụ lời gọi thực hiện hàm fnVendorID. Giá trị trả ra của hàm 
fnVendorID là VendorID của vendor có tên là „IBM‟ và giá trị VendorID 
này được sử dụng làm điều kiện lọc cho câu lệnh SELECT. 
- Ở dưới cùng slide là ví dụ khác thực hiện lời gọi hàm fnTopVendorsDue 
giống như một bảng nguồn dữ liệu cho truy vấn vì hàm này trả ra giá trị là 
một bảng dữ liệu. 
Chú ý: các lời gọi thực hiện hàm đều phải có thêm tên lược đồ quản lý nó 
(dbo) ở phía trước. 
41 
Trên slide là cú pháp tạo một hàm trả ra giá trị kiểu vô hướng. Kiểu hàm này 
cũng có các tham biến trị bắt buộc và trùy chọn. 
- RETURNS data_type là định nghĩa kiểu dữ liệu vô hướng mà hàm sẽ trả ra. 
- RETURN scalar_expression là lệnh trả giá trị trong định nghĩa hàm thuộc 
kiểu vô hướng đã được xác định ở câu lệnh RETURNS phía trên. 
42 
- Phía trên slide là ví dụ tạo hàm fnBalancedue() có kiểu dữ liệu trả ra là 
money. Hàm này trả ra tổng tiền của các hóa đơn chưa thanh toán hết (còn 
nợ tiền). 
- Bên dưới là ví dụ cách gọi thực hiện hàm fnBalanceDue() và kết quả. 
43 
44 
- Phía trên slide là cú pháp tạo hàm trả ra giá trị kiểu bảng đơn giản. Sau câu 
lệnh RETURN thứ hai là một câu lệnh SELECT. 
- Phía bên dưới slide là một ví dụ tạo hàm fnTopVendorsDue() trả ra giá trị 
bảng đơn giản. Hàm này có nhiệm vụ trả ra một bảng gồm có hai cột 
VendorName và ToTalDue (tổng tiền nợ) của các vendor có tổng tiền nợ các 
hóa đơn lớn hơn giá trị của biến @CutOff. 
45 
- Trên slide là ví dụ lời gọi thực hiện hàm fnTopVendorsDue với giá trị của 
tham biến trị @CutOff= 50000 và kết quả. 
46 
Vì hàm fnTopVendorsDue() trả ra một bảng dữ liệu nêm có có thể xuất hiện 
trong mệnh đề FROM và liên kết với bảng dữ liệu khác (bảng Vendors). 
47 
48 
- Trên slide là cú pháp câu lệnh tạo hàm trả ra bảng dữ liệu dựa trên nhiều 
câu lệnh. 
- RETURNS @return_variable TABLE(): câu lệnh xác định biến trả kiểu 
Table để trả ra trị cho hàm. 
- Các câu lệnh định nghĩa hàm nằm trong khối lệnh BEGINEND. Cuối 
cùng là một câu lệnh RETURN rỗng. 
49 
Ví dụ tạo hàm fnCreditAdj(): 
- Biến @OutTable là một biến bảng bao gồm bảy cột (như trên phần khai 
báo). Biến bảng này sẽ được cập nhật dữ liệu trong khối BEGINEND. 
- Trong khôi BEGINEND: câu lệnh INSERT đầu tiên là thêm vào bảng 
@OutTable các hóa đơn còn nợ tiền. Các hóa đơn này là kết quả của câu 
lệnh SELECT. Sau đó, câu lệnh lặp WHILE cập nhật giá trị cột CreditTotal 
lên 1% chừng nào tổng tiền nợ của các hóa đơn >=@HowMuch. Kết thúc là 
câu lệnh RETURN. 
50 
Ví dụ thực hiện hàm fnCreditAdj() trong một câu lệnh SELECT với giá trị 
biến @HowMuch = 25000. 
51 
52 
- Dùng câu lệnh DROP FUNCTION để xóa hàm ra khỏi CSDL. 
- Chú ý: khi xóa một hàm, tất cả các quyền bảo mật gán cho hàm + tất cả các 
phụ thuộc giữa hàm và các bảng dữ liệu và các view mà nó sử dụng cũng bị 
xóa theo. 
53 
- Để sửa hàm dùng câu lệnh ALTER FUNCTION có cú pháp như trên slide để 
sửa hàm trả ra giá trị kiểu vô hướng. 
54 
- Để sửa hàm dùng câu lệnh ALTER FUNCTION có cú pháp như trên slide để 
sửa hàm trả ra giá trị kiểu bảng đơn giản. 
55 
- Để sửa hàm dùng câu lệnh ALTER FUNCTION có cú pháp như trên slide để 
sửa hàm trả ra giá trị kiểu bảng dựa trên nhiều câu lệnh. 
56 
57 
58 
59 
60 
61 
- Ví dụ tạo DML trigger Vendor_INSERT_UPDATE trên bảng Vendors. 
Trigger này được thực hiện tự động khi có các câu lệnh INSERT hoặc 
UPDATE xảy ra trên bảng Vendors. Nhiệm vụ của trigger 
Vendor_INSERT_UPDATE là cập nhật trường VendorState thành chữ hoa 
của bản ghi vừa mới được chèn thêm hoặc cập nhật. 
- Phía dưới là câu lệnh ví dụ chèn một dòng mới vào bảng Vendors có trường 
VendorState = „Oh‟ (không in hoa). Nhưng khi thực hiện câu lệnh: 
 SELECT * 
 FROM Vendors 
 WHERE VendorName = 'Peerless Uniforms, Inc.„ 
thì nhìn thấy ở kết quả giá trị „Oh‟ ở cột VendorState đã được chuyển thành 
chữ hoa „OH‟. Đó là kết quả của trigger Vendor_Insert_Update tự động thực 
hiện bởi hệ thống khi có câu lệnh Insert được thực hiện trên bảng Vendors. 
62 
- Trên slide là cú pháp câu lệnh tạo DML trigger. 
CREATE TRIGGER trigger_name 
 đặt tên cho trigger 
ON {table_name|view_name} 
 Tên bảng hoặc tên view mà trigger gắn với nó 
[WITH [ENCRYPTION] [,] [EXECUTE_AS_clause]] 
 ENCRYPTION: dùng khi không muốn cho NSD đọc định nghĩa trigger (câu 
lệnh tạo trigger). 
 EXECUTE_AS_clause: thực hiện trigger trong một ngữ cảnh bảo mật cụ 
thể. 
{FOR|AFTER|INSTEAD OF} Kiểu trigger. 
[INSERT] [,] [UPDATE] [,] [DELETE] xác định những hành động 
(Insert, Update , Delete) nào trên bảng hoặc view xảy ra thì trigger sẽ tự kích 
hoạt thực hiện. 
AS sql_statements các câu lệnh Sql định nghĩa trigger 
63 
64 
65 
66 
67 
Ví dụ duy trì ràng buộc tham chiếu giữa hai bảng VendorCopy và 
InsertCopy (3 slide) 
Bó lệnh trên slide bao gồm các câu lệnh: 
- Mở CSDL AP 
- Kiểm tra nếu đã tồn tại bảng VendorCopy trong hệ thống thì xóa bỏ. 
- Kiểm tra nếu đã tồn tại bảng InvoiceCopy trong hệ thống thì xóa bỏ. 
- Sao chép Vendors sang bảng VendorCopy. 
- Sao chép bảng Invoice thành InvoiceCopy. 
68 
(Tiếp) 
Bó lệnh thứ hai trong script là lệnh tạo trigger 
VendorCopy_UPDATE_DELETE_RI: 
- Trigger được gắn với bảng VendorCopy 
- Trigger được kích hoạt khi có câu lệnh Delete thực hiện trên bảng 
VendorCopy 
- Nhiệm vụ của trigger: Kiểm tra xem trong cột VendorID bảng InvoiceCopy 
có giá trị nào trùng với giá trị VendorID được xóa bởi câu lệnh Delete 
không. Nếu có, đưa ra thông báo lỗi „VendorID in use‟ (mã VendorID đang 
sử dụng) và phục hồi lại câu lệnh xóa bằng câu lệnh ROLLBACK TRAN. 
Chú ý: Câu lệnh THROW có thể thay bằng thủ tục RAISERROR trong SQL 
Server 2018 
Nghĩa là: 
Thay 
THROW 50002, 'VendorID in use.', 1; 
Bằng 
RAISERROR(N'VendorID in use.', 16,1); 
69 
Hàm RAISERROR: 
Cú pháp: Raiserror(tbao_lỗi, mức_độ, trạng_thái [, các_tham_số]) 
 Tbao_lỗi: có thể là: Chuỗi thông báo lỗi bất kỳ hoặc Mã thông báo lỗi do người 
dùng định nghĩa trước bằng sp_addmessage và được lưu trong sys.messages. Giá trị 
phải > 50000 
 Mức_độ: số có giá trị từ 0 đến 25 thể hiện mức độ nghiêm trọng của lỗi 
 Trạng_thái: số từ 1-127 để xác định vị trí lỗi khi sử dụng cùng một thông báo lỗi tại 
nhiều điểm khác nhau 
 Các_tham_số: hỗ trợ các thông báo lỗi cần tham số 
69 
(Tiếp) 
Bó lệnh thứ ba trong script là lệnh tạo trigger InvoiceCopy_INSERT 
_DELETE_RI: 
- Trigger được gán trên bảng InvoiceCopy 
- Trigger được kích hoạt khi có câu lệnh Insert hoặc Update thực hiện trên 
bảng InvoiceCopy 
- Nhiệm vụ của trigger: Kiểm tra nếu không tồn tại giá trị VendorID nào 
trong cột VendorID của bảng Vendors trùng với giá trị VendorID của bản 
ghi được chèn thêm (hoặc sửa) lưu trong bảng Inserted thì đưa ra thông báo 
lỗi „ Invalid VendorID‟ (mã VendorID không đúng) và phục hồi lại (hủy bỏ) 
câu lệnh chèn thêm hoặc sửa bằng câu lệnh ROLLBACK TRAN. 
70 
Ví dụ thực hiện câu lệnh xóa trên bảng VendorCopy và kết quả. 
71 
72 
73 
Ví dụ tạo một View IBM_Invoices có thể cập nhật dữ liệu cho bảng Invoices 
thông qua View này. 
74 
Ví dụ một câu lệnh chèn thêm dữ liệu vào bảng Invoices thông qua view 
IBM_Invoices nhưng kết quả thông báo câu lệnh bị lỗi vì câu lệnh trên chèn 
một bản ghi mới có giá trị cột VendorID = Null (vi phạm ràng buộc). 
75 
Ví dụ từ slide này đến 2 slide tiếp the là tạo một INSTEAD OF INSERT 
trigger cho view IBM_Invoice để xử lý tình huống thêm một bản ghi mới 
thông qua View có chứa giá trị Null. 
INSTEAD OF INSERT trigger IBM_Invoice_Insert cung cấp giá trị cho các 
cột nhận giá trị null dựa trên 3 giải thiết. 
- Thứ nhất, giá trị cột VendorID có thể đoán (suy ra) được vì view 
IBM_Invoice view chỉ xem và cập nhật các hóa đơn của vendor là IBM. 
- Thứ 2, kì hạn thanh toán của hóa đơn (term, 20 ngày, 30 ngày, ) có thể 
đoán ra được nhờ giá trị kì hạn mặc định của Vendor. 
- Thứ 3, ngày tới hạn của hóa đơn có thể tính gia được từ thông tin ngày lập 
hóa đơn và kì hạn thanh toán của hóa đơn. 
Các câu lệnh trên slide bao gồm: khai báo các biến sử dụng trong trigger 
IBM_Invoice_Insert và gián giá trị cho biến @TestRowCount 
76 
Tiếp 
- Vì trigger chỉ hoạt động trên câu lệnh Insert một bản ghi nên nếu biên 
@TestRowCount > 1 thì trigger đưa ra thống báo lỗi. 
- Nếu @TestRowCount =1 thì trigger sẽ lấy giá trị 03 cột của bản ghi dự 
định được chèn vào bảng Invoices trong bảng Inserted. Nếu giá trị 03 cột 
khác null thì trigger sẽ tính giá trị cho các cột có giá trị null trong lệnh 
Insert từ các cột này 
77 
Vì INSTEAD OF trigger thực hiện thay thế cho câu lệnh Insert nên trong định 
nghĩa trigger phải có câu lệnh Insert bản ghi mới vào bảng Invoices với các giá 
trị được lấy từ câu lệnh Insert qua view và các giá trị nó tính toán được cho các 
cột có giá trị null. Nếu không có câu lệnh Insert này trong định gnhiax trigger 
thì câu lệnh Insert không bao giờ thực hiện. 
78 
Trigger có thể được dùng để kiểm tra các ràng buộc tham toàn vẹn dữ liệu. Ví 
dụ, tổng tiền bán trong bảng InvoiceLineItems của mỗi đơn hàng phải bằng 
tổng tiền tiền của hóa đơn trong bảng Invoices. Chúng ta không thể thiết lập 
ràng buộc này bằng cách định nghĩa các ràng buộc thông thường đã biết. Do 
vậy cần phải xây dựng trigger để thực hiện việc này. Trên slide này và hai slide 
tiếp theo là ví dụ tạo trigger cập nhật Invoice_UPDATE mô tả cho trường 
hợp trên. 
Trigger Invoices_UPDATE: 
- Trigger gắn với bảng Invoices 
- Trigger được thực hiện khi có câu lệnh Update xẩy ra trên bảng Invoice 
- Nhiệm vụ của trigger: 
- Nếu tồn tại trong bảng liên kết Deleted và Invoices bản ghi mà 
Deleted.PaymentTotal Invoices.PaymentTotal thì 
79 
- Nhiệm vụ của trigger: 
- IF tồn tại trong bảng liên kết Deleted và Invoices bản ghi mà 
Deleted.PaymentTotal Invoices.PaymentTotal thì (tiếp) 
 IF tồn tại trong bảng liên kết Invoices và LineItems (bảng tạm 
được định nghĩa bằng câu lệnh SELECT, gồm mã InvoiceID và 
 SumOfInvoices(tổng InvoiceLineItemAmount) của mỗi hóa 
đơn) bản ghi có (Invoices.InvoiceTotal LineItems.SumOfInvoices) AND 
 (LineItems.InvoiceID IN(SELECT InvoiceID FROM 
Deleted)) thì 
 Thông báo lỗi: sửa line item amount trước khi 
công bố thông tin thanh toán 
 Phục hồi câu lệnh Update. 
 Kết thúc IF 
 Kết thúc IF 
80 
Ví dụ thực hiện câu lệnh Update trên bảng Invoices và kết quả tringger được 
kích hoạt và đưa ra thông báo. 
81 
- Sử dụng lệnh DROP TRIGER để xóa trigger 
- Sử dụng lệnh ALTER TRIGGER để sửa triggrer 
- Chú ý: khi xóa một trigger thì các quyền bảo mật gán cho nó cũng bị xóa 
theo. 
82 
Ví dụ câu lệnh sửa và xóa trigger. 
83