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

1 Mục tiêu: - Kỹ băng: - Viết thủ tục lưu trú để giải quyết một vấn đề được đăt ra về CSDL. Bao gồm cả việc kiểm chứng dữ liệu khi cần thiết. - Viết các hàm kiểu vô hướng dựa trên một biểu thức hoặc công thức. - Viết hàm kiểu bảng để thay thế cho câu lệnh SELECT có điều kiện WHERE. - Viết trigger để ngăn chặn lỗi CSDL gây ra bởi một truy vấn hành động (Insert, Update, Delete). - Viết trigger ngăn chặn các lỗi CSDL gây ra bởi một câu lệnh định nghĩa dữ liệu (create, alter, v.v) - Kiến thức: - Giải thích tại sao một thủ tục thực hiện nhanh hơn một SQL script tương ứng. - Mô tả các xử lý cơ bản để kiểm chứng dữ liệu trong một thủ tục lưu trú. - Mô tả ý nghĩa của một số thủ tục hệ thống. 2 - Mô tả hai kiểu hàm do người dùng định nghĩa - Mô tả hai loại triggers - Giải thích ảnh hưởng của các mệnh đề WITH ENCRYPTION và WITH CHEMABINDING trong thủ tục, hàm và triger. - Giải thích tại sao chúng ta nên sử dụng câu lệnh ALTER hơn là xóa và tạo lại một thủ tục, hàm hoặc trigger. 2 - Chúng ta có thể dùng T-SQL để viết các chương trình có tính thủ tục như scripts (tập lệnh/tệp lệnh), Store Procedures (Thủ tục lưu trú), User-defined functions (hàm do người dùng định nghĩa) và triggers (bẫy lỗi). - Các tệp lệnh hữu ích cho các NSD truy cập SQL Server thông qua các công cụ của Client như Management Studio. Các công cụ Client thường chỉ được dùng bởi những người lập trình SQL và quản trị CSDL, không được dùng bởi các chương trình ứng dụng hay NSD cuối. - Store Procedures (Thủ tục lưu trú), User-defined functions (hàm do người dùng định nghĩa) và triggers (bẫy lỗi) là các đối tượng CSDL chứa các câu lệnh SQL và có khả năng thực thi trên server. Các đối tượng này điều khiển và thực hiện tốt hơn tệp lệnh. - Bảng trên slide so sánh sự khác nhau của các chương trình có tính thủ tục trong SQL. - Script: nhiều bó lệnh, được lưu trữ trên ổ đĩa, được thực hiện bởi các công cụ phía client, không có tham biến. - Store procedure (thủ tục lưu trú): một bó lệnh, được lưu trữ như một đối tượng trong CSDL, được thực hiện bởi một chương trình hoặc trong một script, có tham biến. - User- defined function (hàm do người dùng định nghĩa): một bó lệnh, được lưu trữ như một đối tượng trong CSDL, được thực hiện 3 bởi một chương trình hoặc trong một script, có tham biến. - Trigger: một bó lệnh, được lưu trữ như một đối tượng trong CSDL, được tự động thực hiện bởi server khi một truy vấn hành động được thực hiện, không có tham biến. 3 - Thủ tục lưu trú được sử dụng bởi: - Các lập trình viên SQL để điều khiển ai sẽ truy cập CSDL và truy cập như thế nào. - Các lập trình viên ứng dụng để làm đơn giản hóa việc sử dụng CSDL của họ. - Hàm do NSD định nghĩa: - Được sử dụng thường xuyên bởi các lập trình viên SQL ở trong các thủ tục hoặc các trigger mà họ viết. - Cũng có thể được sử dụng bởi lập trình viên ứng dụng và NSD CSDL. - Các trigger được sử dụng bởi: - Các lập trình viên SQL để ngăn chặn các lỗi CSDL khi một truy vấn hành động được thực hiện. - Các lập trình viên SQL để cung cấp các view có thể cập nhật dữ liệu. 4 5 Ví dụ cách tạo và thực thi thủ tục spInvoiceReport. - Thủ tục spInvoiceReport có nhiệm vụ là thực hiện một câu lệnh Select (như trên slide). Câu lệnh này lọc ra các hóa đơn còn nợ tiền. Thông tin trong kết quả lấy từ hai bảng Invoices và bảng Vendors. Kết quả được sắp xếp theo chiều tăng dần của VendorName. - Sau khi viết script như trên slide, chạy script để thủ tục spInvoiceReport được tạo ra và lưu trú trong CSDL AP (xem kết quả trong mục Programmability trong cửa sổ Object exprole). Chi tiết câu lệnh tạo thủ tục sẽ giới thiệu trong phần tiếp theo. 6 Để thực hiện (chạy) thủ tục chúng ta dùng câu lệnh EXEC. Như trên slide, sau khi thực hiện thủ tục spInvoiceReport chúng ta thu được kết quả là một bảng dữ liệu các hóa đơn chưa trả hết tiền (kết quả của câu lệnh select trong thủ tục). Trong lần chạy đầu tiên, các câu lệnh SQL trong thủ tục sẽ được biên dịch và thực thi để tạo một kế hoạch thực thi (execution plan). Sau đó, thủ tục sẽ được lưu trữ dưới dạng thực thi trong CSDL và ở những lần chạy thủ tục sau thì các câu lệnh SQL trong nó không cần phải biên dịch lại. Do vậy, quá trình trình thực thi của thủ tục lưu trú nhanh hơn quá trình thực thi của têp lệnh SQL tương đương. Chúng ta thấy rằng, NSD hay một chương trình khi gọi thủ tục spInvoiceReport không cần phải biết không cần biết cấu trúc của CSDL sử dụng trong thủ tục và cũng không nhất thiết phải biết câu lệnh SQL. Đây cũng là một cách để tăng tính bảo mật cho CSDL. 7 Cú pháp của câu lệnh tạo thủ tục CREATE PROCE: - procedure_name: tên của thủ tục được tạo, tên đặt tùy ý theo nguyên tắc đặt tên trong SQL server, tuy nhiên nên thêm hai kí tự „sp‟ vào phí trước tên để phân biệt với hàm. - Parameter_declarations: khai báo tham biến (tùy chọn) - RECOMPILE: thủ tục được biên dịch lại khi được chạy. - ENCRYPTION: thủ tục bị mã hóa không được xem bởi thủ tục sp_helptext - EXECUTE_AS_clause: - Sql_statements: tập hợp các câu lệnh SQL được sử dụng để giải quyết nhiệm vụ của thủ tục. Phía bên dưới slide là ví dụ tạo thủ tục spCopyInvoices thực hiện nhiệm vụ sao chép bảng dữ liệu Invoices vào bảng InvoiceCopy bằng câu lệnh SelectIntoFrom. 8 9 - Cú pháp khai báo các tham biến trong thủ tục: - @tên_biến kiểu_dữ_liệu [= giá_trị_mặc_định] [OUTPUT], các biến ngăn cách nhau bởi dấu cách. - Có 3 loại tham biến cho thủ tục: - Tham biến (tham trị) bắt buộc: luôn luôn phải có trong lời gọi thủ tục. Ví dụ @DateVar smalldatetime. - Tham biến (tham trị) tùy chọn: biến được gán giá trị mặc định trong câu lệnh khai báo biến, do đó nó có thể không cần xuất hiện trong lời gọi thủ tục. Ví dụ, VendorVar varchar(40) = NULL. - Tham biến biến: biến truyền giá trị ra ngoài thủ tục, trong khai báo có thêm từ khóa OUTPUT. Ví dụ, @InvTotal money OUTPUT Chú ý: Nên khai báo các biến bắt buộc trước các biến không bắt buộc trong định nghĩa thủ tục. 10 Ví dụ tạo thủ tục spInvotal1 có một tham trị bắt buộc (@DateVar) và một tham biến biến (@InvTotal). Thủ tục thực hiện nhiệm vụ: Tính tổng tiền của các hóa đơn có ngày lập hóa đơn (InvoiceDate) >= ngày của tham trị @DateVar rồi gán giá trị tổng này cho tham biến biến @InvTotal. 11 Ví dụ tạo thủ tục spInvTotal2 có một tham trị tùy chọn (@DateVar). Thủ tục thực hiện nhiệm vụ: kiểm tra xem nếu tham trị @DateVar nhận giá trị NULL thì sẽ gán lại giá trị cho tham trị này bằng giá trị của hóa đơn có ngày lập nhỏ nhất. Sau đó mới thực hiện lệnh Select đư ra tổng tiền của tất cả các hóa đơn có ngày lập hóa đơn (InvoiceDate) lớn hơn giá trị biến @DateVar. 12 Ví dụ định nghĩa thủ tục spInvTotal3 có 3 tham biến: 1 tham biến biến và 2 tham biến tùy chọn. Thủ tục thực hiện nhiệm vụ: - Kiểm trs biến @DateVar nhận giá trị Null thì gán lại giá trị mới như trong thủ tục spInvTotal2. - Gán giá trị cho tham biến biến @InvTotal bằng tổng tiền các hóa đơn có ngày lập >= giá trị biến @DateVar và có VendorName LIKE @VendorVar 13 Ví dụ các lời gọi thực hiện thủ tục khác nhau của thủ tục spInvTotal3: EXEC spInvTotal3 @MyInvTotal OUTPUT, '2012-02-01', 'P%';  lời gọi có đủ 3 biến. EXEC spInvTotal3 @DateVar = '2012-02-01', @VendorVar = 'P%', @InvTotal = @MyInvTotal OUTPUT;  lời gọi tường minh gán từng giá trị cho từng biến (không cần quan tâm đế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