触发器总结

1.用户定义函数介绍:
SQL Server 允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能
优点:
允许模块化程序设计 执行速度更快 减少网络流量
三种 :标量函数 表值函数 多语句表值函数、内联表值函数 内置函数
简单的介绍：
(1)标量函数：返回一个标量（单值）结果
标量函数返回 RETURNS 子句中定义的数据类型的单个数据值
在 BEGIN … END 块之间定义了函数体，包含返回值的一系列 Transact-SQL语句
返回值可以是除了 text、ntext、p_w_picpath、cursor 、 timestamp 之外的任何数据类型
额外的限制:不能包括任何非确定性系统函数
例子：
CREATE FUNCTION fn_DateFormat (@indate datetime, @separator char(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(), ':')
(2)表值函数：返回 table 数据类型
调用格式：函数名 [参数列表]
不需要指定“拥有者名”，但必须包括所有定义的参数
函数体内允许有
赋值语句、流控制语句
DECLARE 语句
SELECT 语句
局部游标操作
BEGIN 和 END 分隔了函数体
RETURNS 子句指定 table 作为返回的数据类型
RETURNS 子句定义了返回表的名字和格式
返回变量名的使用域限定于函数局部
不允许：
出现返回非函数返回值的语句以及产生副作用的语句
对数据库表的更新、全局游标语句、创建对象的语句、事务控制语句
例子：
CREATE FUNCTION fn_Employees (@length varchar(9))
RETURNS @fn_Employees table
   (EmployeeID int PRIMARY KEY NOT NULL,
   [Employee Name] nvarchar(61) NOT NULL)
AS
BEGIN
   IF @length = 'ShortName'
      INSERT @fn_Employees SELECT EmployeeID,  LastName FROM Employees
   ELSE IF @length = 'LongName'
      INSERT @fn_Employees SELECT EmployeeID,
      (FirstName + ' ' + LastName) FROM Employees
RETURN
END
SELECT * FROM dbo.fn_Employees('ShortName')

(3)内置函数：系统提供，返回标量数据类型或 table 数据类型
内联表值函数返回表，以在 FROM 子句中被引用，就像视图一样
使用内联表值函数
RETURN 子句在括号中包含单个 SELECT 语句，SELECT 语句的结果集构成函数所返回的表
函数体不由 BEGIN 和 END 分隔
RETURNS 指定 table 作为返回的数据类型
不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置
例子：
CREATE FUNCTION fn_CustomerNamesInRegion
   ( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN
(
   SELECT CustomerID, CompanyName
   FROM Northwind.dbo.Customers
   WHERE Region = @RegionParameter
)
SELECT * FROM fn_CustomerNamesInRegion('WA')
下面是一些具体细节的限制和注意的内容：
函数的限制：用户定义函数体中不允许使用内置非确定性函数，如：@@ERROR、 @@IDENTITY等
唯一、合法的用户定义函数名
CREATE FUNCTION 数据库名.架构名.函数名 （参数…）
语句指定输入参数及它们的数据类型、处理指令，以及返回的值及其数据类型
标量用户定义函数可在任何需要它们返回值的数据类型值的地方使用
表值用户定义函数只能用在 SELECT 语句的 FROM 子句中
创建架构绑定的函数：
使用架构绑定
函数创建时使用 SCHEMABINDING 选项
创建后，所引用的数据库对象不能被更改
创建时指定SCHEMABINDING 选项
该函数引用的所有视图和用户定义函数都绑定到架构
函数引用的对象的引用格式：“架构者名.对象名”
位于同一数据库
用户权限：REFERENCE 权限
创建、更改或删除用户定义函数，必须具有 CREATE FUNCTION 权限
其他用户使用函数，则必须具有 EXECUTE 权限
若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的 REFERENCE 权限
若在 CREATE TABLE 或 ALTER TABLE 语句的 CHECK 约束、DEFAULT 子句或计算列中引用了用户定义函数，则表的拥有者必须具有该函数的 REFERENCE 权限
2.触发器
触发器是一种特殊的存储过程，它与表紧密相连，可以看作是表定义的一部分。当用户修改指定表或视图中的数据时，触发器将会自动运行。
Create trigger 触发器名字
On {表名或视图名}
With encryption
{for|after|instead of}{delete|insert|update}
As
操作SQL语句
1、for和after的作用相同，都是在触发器都在指定的事件之后。
Instead of是用触发器的操作替代原有的操作。
2、delete ，insert，update可以只有一个，也可以有多个
触发器不能执行的操作：
(1)对数据的操作
(2)不允许对基表执行修改，删除等操作
(3)对索引的操作
(4)Reconfigure语句
创建insert触发器
建一个日志记录表，用来存放操作的日志
use mydb
go
create  table Roc
(
  id int identity not null primary key,
  op_name varchar(20),
  op_des varchar(40),
 op_user varchar(20)
)
create  trigger stu_insert_trig
on test
after insert
as
begin
 insert into roc values('insert','插入操作',user_name())
end
select * from test
select * from roc
insert into test([name],[sum],fid) values('李四',200,3)
创建update触发器:
create trigger stu_update_trig
on test
after update
as
begin
 insert into roc values('update','更新操作',user_name())
end
测试语句
update test set [name]='李四' where id=2
select * from roc
select * from test
创建delete触发器:
create trigger stu_delete_trig
on test
after delete
as
begin
 insert into roc values('delete','删除操作',user_name())
end
测试语句
delete  test  where id=2
select * from roc
创建instead of触发器
create trigger stu_instead_trig
on stu
instead of insert
as
begin
 insert into stu values('无名',0,0)
end
--测试语句
insert into stu values('张三',19,1)
select * from  stu

修改触发器
alter trigger stu_instead_trig
on stu
instead of insert
as
begin
 insert into stu values('真的无名',0,0)
End
删除触发器
drop trigger stu_instead_trig
禁用触发器
alter table stu
disable trigger stu_insert_trig
解禁触发器
alter table stu
enable trigger stu_insert_trig