一:什么是触发器
触发器是一种响应特定事件的特殊类型的存储过程
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()有检查多个列的能力
比较高级,也比较少用 我在这里就不写了