一:什么是触发器
触发器是一种响应特定事件的特殊类型的存储过程
insert update... drop alter...等事件都有相应的触发器
二:简单的触发器
下面一个例子是在插入或者修改记录的时候的一个触发器
其中inserted表是一个临时表
存储的是将要插入的信息
这个触发器的目的是检查将要插入的信息是否符合规定
(在product表里没有特殊的记录)
这个例子是check约束所不能解决的了的
use
AdventureWorks
go
create trigger orderDetailNotDiscontinued
on Sales.SalesOrderDetail
after insert , update
as
if exists
(
select ' true ' from Inserted i
join Production.Product p
on i.ProductID = p.ProductID
where p.DiscontinuedDate is not null
)
begin
print ( ' error appear. ' )
rollback tran
end
go
create trigger orderDetailNotDiscontinued
on Sales.SalesOrderDetail
after insert , update
as
if exists
(
select ' true ' from Inserted i
join Production.Product p
on i.ProductID = p.ProductID
where p.DiscontinuedDate is not null
)
begin
print ( ' error appear. ' )
rollback tran
end
为了验证这个触发器
先给表加入一条不符合条件的记录
先给表加入一条不符合条件的记录
use
AdventureWorks
go
update Production.Product
set DiscontinuedDate = getdate ()
where ProductID = 680
go
update Production.Product
set DiscontinuedDate = getdate ()
where ProductID = 680
好了现在来一条插入语句看看
use
AdventureWorks
go
insert Sales.SalesOrderDetail
(SalesOrderID,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount)
values
( 43660 , 5 , 680 , 1 , 1431 , 0 )
go
insert Sales.SalesOrderDetail
(SalesOrderID,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount)
values
( 43660 , 5 , 680 , 1 , 1431 , 0 )
得到的消息是:
error appear.
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。
三:针对触发器的操作
1.禁用触发器
error appear.
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。
三:针对触发器的操作
1.禁用触发器
use
AdventureWorks
go
alter table Sales.SalesOrderDetail
disable trigger all -- 这里的all可以是具体的触发器名
go
alter table Sales.SalesOrderDetail
disable trigger all -- 这里的all可以是具体的触发器名
2.删除触发器
use
AdventureWorks
go
drop trigger orderDetailNotDiscontinued
go
drop trigger orderDetailNotDiscontinued
3.修改触发器
use
xland
go
alter trigger tr_title
on mytable
for insert , update
as
if update (title)
begin
print ( ' title1 ' )
end
go
alter trigger tr_title
on mytable
for insert , update
as
if update (title)
begin
print ( ' title1 ' )
end
(这里的update()函数不懂没关系,下面会说到)
四:使用触发器记录操作情况
先看代码
use
AdventureWorks
go
create table Production.inventoryAudit
(
id int identity primary key ,
productid int not null
references Production.Product(ProductID),
netadjustment smallint not null ,
modifieddate datetime default ( current_timestamp )
)
go
create trigger ProductAudit
on Production.ProductInventory
for insert , update , delete
as
insert into Production.inventoryAudit
(productid,netadjustment)
select coalesce -- coalesce哪个不为空用哪个
(i.ProductID,d.ProductID),
isnull (i.Quantity, 0 ) - isnull (d.Quantity, 0 ) as netadjustmenu
from Inserted i
full join Deleted d
on i.ProductID = d.ProductID
and i.LocationID = d.LocationID
where
isnull (i.Quantity, 0 ) - isnull (d.Quantity, 0 ) != 0
-- isnull(i.Quantity,0)如果i.Quantity为空返回0(这个函数比较有用)
go
create table Production.inventoryAudit
(
id int identity primary key ,
productid int not null
references Production.Product(ProductID),
netadjustment smallint not null ,
modifieddate datetime default ( current_timestamp )
)
go
create trigger ProductAudit
on Production.ProductInventory
for insert , update , delete
as
insert into Production.inventoryAudit
(productid,netadjustment)
select coalesce -- coalesce哪个不为空用哪个
(i.ProductID,d.ProductID),
isnull (i.Quantity, 0 ) - isnull (d.Quantity, 0 ) as netadjustmenu
from Inserted i
full join Deleted d
on i.ProductID = d.ProductID
and i.LocationID = d.LocationID
where
isnull (i.Quantity, 0 ) - isnull (d.Quantity, 0 ) != 0
-- isnull(i.Quantity,0)如果i.Quantity为空返回0(这个函数比较有用)
注意这里用了full join
其实如果不是高并发inserted和deleted中有一个表是不存在的
下面来测试一下这个触发器
下面来测试一下这个触发器
use
AdventureWorks
go
update Production.ProductInventory
set Quantity = Quantity + 7
where ProductID = 1
and LocationID = 50
select * from Production.inventoryAudit
go
update Production.ProductInventory
set Quantity = Quantity + 7
where ProductID = 1
and LocationID = 50
select * from Production.inventoryAudit
来看看消息:
(1 行受影响) 这是修改的一行
(1 行受影响) 这是修改的一行
(1 行受影响) 这是触发器执行的一行
(1 行受影响) 这是select的一行
五:视图上的触发器
instead of触发器
先创建一个视图
instead of触发器
先创建一个视图
use
xland
go
create view UserArticle_vw
with Schemabinding
as
select a.id,a.title,u.username
from dbo.mytable a -- 必须有架构名，可以用as
join dbo. [ user ] u
on u.id = a.uid
检查该视图
go
create view UserArticle_vw
with Schemabinding
as
select a.id,a.title,u.username
from dbo.mytable a -- 必须有架构名，可以用as
join dbo. [ user ] u
on u.id = a.uid
use
xland
go
select * from UserArticle_vw where id = 2
go
select * from UserArticle_vw where id = 2
创建一个视图上的触发器
use
xland
go
create trigger TRMytableInsert on UserArticle_vw
instead of insert
as
begin
if ( select count ( * ) from inserted) > 0
begin
insert into mytable
select i.title,u.id from inserted i
join [ user ] u
on u.username = i.username
if @@rowcount = 0 -- 返回受影响的行数
print ( ' error appear ' )
end
end
go
create trigger TRMytableInsert on UserArticle_vw
instead of insert
as
begin
if ( select count ( * ) from inserted) > 0
begin
insert into mytable
select i.title,u.id from inserted i
join [ user ] u
on u.username = i.username
if @@rowcount = 0 -- 返回受影响的行数
print ( ' error appear ' )
end
end
对这个视图执行插入操作!(有了上面的触发器就可以插入了)
use
xland
go
insert into UserArticle_vw (title,username) values ( ' zhe shi title ' , ' xland ' )
go
insert into UserArticle_vw (title,username) values ( ' zhe shi title ' , ' xland ' )
看下消息
(1 行受影响)
(1 行受影响)
(1 行受影响)
类似的还有
insted of update触发器
insted of delete触发器
六:触发器的相关函数
看这个触发器
类似的还有
insted of update触发器
insted of delete触发器
六:触发器的相关函数
看这个触发器
use
xland
go
alter trigger tr_title
on mytable
for insert , update
as
if update (title)
begin
print ( ' title1 ' )
end
update()函数意思是
go
alter trigger tr_title
on mytable
for insert , update
as
if update (title)
begin
print ( ' title1 ' )
end
指定的列被更改时发生
看下验证语句
title1
看下验证语句
use
xland
go
update mytable set title = ' xland ' where id = 7
结果
go
update mytable set title = ' xland ' where id = 7
title1
(1 行受影响)
还有一个函数
columns_updated()有检查多个列的能力
比较高级,也比较少用 我在这里就不写了
columns_updated()有检查多个列的能力
比较高级,也比较少用 我在这里就不写了
