函数
系统函数
创建函数
use SQL2008SBS
go
create function Orders.fn_oldestopenorder()
returns int
as
begin
declare @OrderID int,
@MinOrderDate date
select @MinOrderDate = MIN(OrderDate)
from Orders.OrderHeader
where FinalShipDate is null
select @OrderID = MIN(OrderID)
from Orders.OrderHeader
where OrderDate = @MinOrderDate
return @OrderID
end
go
创建表值函数
use SQL2008SBS
go
create function Orders.fn_openorders(@NumDays int)
returns table
as
return
(select OrderID,CustomerID,OrderDate
from Orders.OrderHeader
where OrderDate<=DATEADD(dd,@NumDays,Getdate())
and FinalShipDate is null)
go
use SQL2008SBS
go
--创建多语句表值函数
create function Orders.fn_openshippableorders(@NumDays int)
returns @ShippableOrders table
(OrderID int not null,
CustomerId int not null,
OrderDate date not null)
as
begin
declare @OpenOrders Table
(OrderID int not null,
CustomerID int not null,
OrderDate date not null)
insert into @OpenOrders
(OrderID,CustomerID,OrderDate)
select a.OrderID,a.CustomerID,a.OrderDate
from @OpenOrders a inner join
(select OrderId from Orders.OrderDetail
except
select OrderID from Orders.OrderDetail c
inner join Products.ProductOptions d on c.SKU = d.SKU
inner join Products.ProductInventory e on d.ProductID = e.ProductID
where c.Quantity > = e.Quantity
) b on a.OrderID = b.OrderID
return
end
go
从函数中获取数据
use SQL2008SBS
go
--从函数中获取数据
--查看Order.OrderHeader表内容
select OrderID,CustomerID,OrderDate
from Orders.OrderHeader
go
--查看Orders.OrderDetail表内容
select OrderID,OrderDetailID,SKU,Quantity
from Orders.OrderDetail
go
--查看数据库个sku数量
select *
from Products.ProductOptions a inner join Products.ProductInventory b
on a.ProductID = b.ProductID
go
--执行下列代码，从之前所创建的标量函数中获取结果
select Orders.fn_oldestopenorder()
go
--执行下列代码以查看一条之前的打开的订单
select * from Orders.fn_openorders(1)
go
--执行下列代码以查看天以前打开的订单
select * from Orders.fn_openorders(5)
修改可编程对象
use SQL2008SBS
go
--执行代码修改Oders.fn_openorders函数
alter function Orders.fn_openorders(@NumDays int)
returns table
as
return
(select OrderID,CustomerID,OrderDate
from Orders.OrderHeader
where OrderDate <= DATEADD(dd,-@NumDays,getdate())
and FinalShipDate is null)
go
--执行修改后的函数，查看一天前打开的订单
select * from Orders.fn_openorders(1)
go
--执行下列代码以查看5天前打开的订单
select * from Orders.fn_openorders(5)
go
--执行查看天前打开的，且已经可以运送的订单
select * from Orders.fn_openshippableorders(3)
go
--执行查看天前打开的，且已经可以运送的订单
select * from Orders.fn_openshippableorders(5)
go
--修改ProductID 2产品的现有库存，从而使得部分订单的产品库存数量达不到要求
update Products.ProductInventory set Quantity = 15 where ProductID = 2
go
--执行下列代码以查看天前打开的，且已经可以运送的订单
select * from Orders.fn_openshippableorders(3)
go
--执行下列代码以查看天前打开的，且已经可以运送的订单
select * from Orders.fn_openshippableorders(5)
go
|
执行函数 |
在select,from或select语句的where 或check/default约束中使用函数 |
|
确保函数中不删除依赖对象 |
指定SCHEMABINDING选项 |
|
如果传递NULL参数则不执行函数 |
指定RETURNS NULL ON NULL INPUT选项 |
|
修改函数执行的安全上下文 |
指定EXECUTE AS 子句 |
触发器
DML触发器
创建一个DML触发器
use SQL2008SBSFS
go
--创建一个DML触发器
create trigger tiu_productdocuments on Products.ProductDocument
for insert,update
as
if exists (select 1 from SQL2008SBS.Products.Product a
inner join inserted b on a.ProductID = b.ProductID)
begin
return
end
else
begin
rollback transaction
raiserror('Violation of foreign key',16,1)
end
go
use SQL2008SBS
go
create trigger td_product on Products.Product
for delete
as
if exists(select 1 from SQL2008SBSFS.Products.ProductDocument a
inner join deleted b on a.ProductID = b.ProductID)
begin
rollback transaction
raiserror('You must first delete all documents for this product',16,1)
end
else
begin
return
end
go
--测试触发器
use SQL2008SBSFS
go
--.....
DDL触发器
创建一个数据库级别的DDL触发器
use SQL2008SBS
go
--创建一个数据库级别的DDL触发器
create trigger tddl_preventdrop
on database
for DROP_TABLE
as
print 'Please disable DDL trigger before dropping tables'
rollback transaction
go
创建一个实例级别的DDL触发器
use SQL2008SBS
go
create trigger tddl_limitconnections
on all server
for LOGON
as
begin
if (select COUNT(*) from sys.dm_exec_sessions
where is_user_process = 1 and
login_name = SUSER_SNAME())>5
print 'You are only allowed a maximu of 5 concurrent connection'
rollback
end
go
--创建个以上的并发连接验证触发器
|
DML命令执行时运行代码 |
创建一个DML触发器 |
|
DDL命令执行时运行代码 |
创建一个DDL触发器 |