Microsoft SQL Server 2000 Programming by Example phần 7

Ngăn ngừa sự Thay đổi các đối tượng phụ thuộc: Các lựa chọn SCHEMABINDING Bạn có thể phòng ngừa thay đổi trên các đối tượng phụ thuộc của một chức năng người dùng định nghĩa bằng cách sử dụng tùy chọn SCHEMABINDING. Sử dụng tùy chọn này, bạn không thể sửa đổi định nghĩa của các đối tượng phụ thuộc sử dụng ALTER Bất kỳ của báo cáo, | Chapter 10. Enhancing Business Logic User-Defined Functions UDF USE Northwind GO DROP FUNCTION Caution Before dropping a user-defined function as with any other database object check its dependencies. You cannot drop a user-defined function if it is used in a constraint definition. If you drop a user-defined function and it is used in other functions views triggers or stored procedures those functions will produce an error on next execution. Preventing the Alteration of Dependent Objects The SCHEMABINDING Option You can prevent changes on the dependent objects of a user-defined function by using the SCHEMABINDING option. Using this option you cannot modify the definition of the dependent objects using any of the ALTER statements and you cannot drop dependent objects using any of the DROP statements. This link disappears when the function is dropped or when you alter the function definition without using the SCHEMABINDING option. To use this option you must ensure that the following conditions are met Every function and view referenced in the function must be defined as SCHEMABINDING as well. Every object referenced in the function must be referenced using two-part names . Every object referenced in the function belongs to the same database as the function. The user who creates the function not necessarily the owner has REFERENCES permissions on every object referenced inside the function. It is recommended that only members of the db_owner role execute the CREATE FUNCTION statement. Listing shows how to use the SCHEMABINDING option and the effect when you try to modify a dependent object. The process is as follows 1. You create the NewCustomers table with data coming from the Customers table. 2. You create the GetCustomers table-valued function reading the CustomerID and CompanyName fields from the NewCustomers table. 3. You try to alter the NewCustomers table dropping the CompanyName column and it is successful because the .