1、Module 10: 实现用户自定义函数,Overview,什么是用户自定义函数 定义用户自定义函数 用户自定义函数实列,什么是用户自定义函数？,标量函数 Similar to a built-in function 多语句表值函数 Content like a stored procedure Referenced like a view 嵌入式表值函数 Similar to a view with parameters Returns a table as the result of single SELECT statement,定义用户自定义函数,创建用户自定义函数 创建带模式绑定的函
2、数 设置用户自定义函数的权限 修改和删除用户自定义函数,创建用户自定义函数,USE Northwind CREATE FUNCTION fn_NewRegion(myinput nvarchar(30) RETURNS nvarchar(30) BEGINIF myinput IS NULLSET myinput = Not ApplicableRETURN myinput END,Creating a Function Restrictions on Functions,创建带模式绑定的函数,Referenced User-defined Functions and Views Are Al
3、so Schema Bound Objects Are Not Referenced with a Two-Part Name Function and Objects Are All in the Same Database Have Reference Permission on Required Objects,设置用户自定义函数的权限,Need CREATE FUNCTION Permission Need EXECUTE Permission Need REFERENCE Permission on Cited Tables, Views, or Functions Must Own
4、 the Function to Use in CREATE or ALTER TABLE Statement,修改和删除用户自定义函数,Altering FunctionsRetains assigned permissions Causes the new function definition to replace existing definition Dropping Functions,ALTER FUNCTION dbo.fn_NewRegion ,DROP FUNCTION dbo.fn_NewRegion,用户自定义函数实列,使用标量用户自定义函数 标量用户自定义函数举列 使
5、用多语句表值函数 多语句表值函数举列 使用嵌入式表值函数 嵌入式表值函数举列,使用标量用户自定义函数,RETURNS Clause Specifies Data Type Function Is Defined Within a BEGIN and END Block Return Type Is Any Data Type Except text, ntext, image, cursor, or timestamp,标量用户自定义函数举列,USE Northwind CREATE FUNCTION fn_DateFormat (indate datetime, separator char
6、(1) RETURNS Nchar(20) AS BEGIN RETURN CONVERT(Nvarchar(20), datepart(mm,indate) + separator + CONVERT(Nvarchar(20), datepart(dd, indate) + separator + CONVERT(Nvarchar(20), datepart(yy, indate) END,SELECT dbo.fn_DateFormat(GETDATE(), :),Creating the FunctionCalling the Function,使用多语句表值函数,BEGIN and E
7、ND Enclose Multiple Statements RETURNS Clause Specifies table Data Type RETURNS Clause Names and Defines the Table,多语句表值函数举列,Creating the FunctionCalling the Function,USE Northwind GO CREATE FUNCTION fn_Employees (length nvarchar(9) RETURNS fn_Employees table(EmployeeID int PRIMARY KEY NOT NULL,Empl
8、oyee Name nvarchar(61) NOT NULL) AS BEGINIF length = ShortNameINSERT fn_Employees SELECT EmployeeID, LastName FROM EmployeesELSE IF length = LongNameINSERT fn_Employees SELECT EmployeeID, (FirstName + + LastName) FROM Employees RETURN END,SELECT * FROM dbo.fn_Employees(LongName) Or SELECT * FROM dbo
9、.fn_Employees(ShortName),使用嵌入式表值函数,Content of the Function Is a SELECT Statement Do Not Use BEGIN and END RETURN Specifies table as the Data Type Format Is Defined by the Result Set,嵌入式表值函数举列,Creating the FunctionCalling the Function Using a Parameter,USE Northwind GO CREATE FUNCTION fn_CustomerName
10、sInRegion( RegionParameter nvarchar(30) ) RETURNS table AS RETURN (SELECT CustomerID, CompanyNameFROM Northwind.dbo.CustomersWHERE Region = RegionParameter),SELECT * FROM fn_CustomerNamesInRegion(NWA),Recommended Practices,Lab A: Creating User-defined Functions,Review,What Is a User-defined Function
11、? Defining User-defined Functions Examples of User-defined Functions,Module 11:实现触发器,Overview,介绍触发器 定义触发器 触发器如何工作 触发器举列 性能考虑,介绍触发器,什么是触发器 触发器的使用 使用触发器时的考虑,什么是触发器？,Associated with a Table Invoked Automatically Cannot Be Called Directly Is Part of a Transaction,触发器的使用,Cascade Changes Through Related T
12、ables in a Database Enforce More Complex Data Integrity Than a CHECK Constraint Define Custom Error Messages Maintain Denormalized Data Compare Before and After States of Data Under Modification,使用触发器时的考虑,Triggers Are Reactive; Constraints Are Proactive Constraints Are Checked First Tables Can Have
13、Multiple Triggers for Any Action Table Owners Can Designate the First and Last Trigger to Fire You Must Have Permission to Perform All Statements That Define Triggers Table Owners Cannot Create AFTER Triggers on Views or Temporary Tables,定义触发器,创建触发器 修改和删除触发器,创建触发器,Requires Appropriate Permissions Ca
14、nnot Contain Certain Statements,Use Northwind GO CREATE TRIGGER Empl_Delete ON Employees FOR DELETE AS IF (SELECT COUNT(*) FROM Deleted) 1 BEGINRAISERROR( You cannot delete more than one employee at a time., 16, 1)ROLLBACK TRANSACTION END,修改和删除触发器,Altering a Trigger Changes the definition without dr
15、opping the trigger Can disable or enable a triggerDropping a Trigger,USE Northwind GO ALTER TRIGGER Empl_Delete ON Employees FOR DELETE AS IF (SELECT COUNT(*) FROM Deleted) 6 BEGINRAISERROR( You cannot delete more than six employees at a time., 16, 1)ROLLBACK TRANSACTION END,触发器如何工作,INSERT触发器如何工作 DE
16、LETE触发器如何工作 UPDATE触发器如何工作 INSTEAD OF触发器如何工作 嵌套触发器如何工作 递归触发器,INSERT触发器如何工作,INSERT statement to a table with an INSERT Trigger Defined,INSERT Order Details VALUES (10525, 2, 19.00, 5, 0.2),Order Details,OrderID,10522 10523 10524,ProductID,10 41 7,UnitPrice,31.00 9.65 30.00,Quantity,7 9 24,Discount,0.2
17、 0.15 0.0,UPDATE P SET UnitsInStock = (P.UnitsInStock I.Quantity) FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID,DELETE触发器如何工作,DELETE Statement to a table with a DELETE Trigger Defined,DELETE Statement to a table with a DELETE Trigger Defined,DELETE Categories WHERE Categor
18、yID = 4,UPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryID,UPDATE触发器如何工作,INSTEAD OF触发器如何工作,Create a View That Combines Two or More Tables,CREATE VIEW Customers AS SELECT * FROM CustomersMex UNION SELECT * FROM CustomersGer,INSTEAD OF trigger directs
19、 the update to the base table,Original Insert to the Customers View Does Not Occur,UPDATE is Made to the View,嵌套触发器如何工作,递归触发器,Activating a Trigger Recursively Types of Recursive Triggers Direct recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again In
20、direct recursion occurs when a trigger fires and performs an action that causes a trigger on another table to fire Determining Whether to Use Recursive Triggers,触发器实列,强制数据完整性 强制业务规则,强制数据完整性,强制业务规则,Products with Outstanding Orders Cannot Be Deleted,IF (Select Count (*) FROM Order Details INNER JOIN d
21、eleted ON Order Details.ProductID = deleted.ProductID ) 0 ROLLBACK TRANSACTION,DELETE statement executed on Product table,Trigger code checks the Order Details table,Transaction cannot be processed This product has order history,Transaction rolled back,性能考虑,Triggers Work Quickly Because the Inserted
22、 and Deleted Tables Are in Cache Execution Time Is Determined by: Number of tables that are referenced Number of rows that are affected Actions Contained in Triggers Implicitly Are Part of a Transaction,Recommended Practices,Lab A: Creating Triggers,Review,Introduction to Triggers Defining Triggers How Triggers Work Examples of Triggers Performance Considerations,
