数据库触发器案例
一、课堂演示案例
例一：创建一个简单的insert触发器
先创建一个数据库备用
create database sampledb
go
use sampledb
go
在新创建的库中创建一个表备用
create table aa
(
a int,
b int
)
go
在新创建的表上创建一个insert触发器
use sampledb
go
if exists(select name from sysobjects where name ='tr_intoa' and type='tr')
drop trigger tr_intoa
go
create trigger tr_intoa on aa
for insert
as
print 'success inserted one row!'
查看这个触发器的定义文本
sp_helptext checkpubdate
查看这个触发器的信息
sp_help checkpubdate
验证这个触发器的工作情况
insert into aa values (1,2)
----------------------------------------------------------------------------------------------------------------------
例二：创建一个触发器监视insert操作，若插入的记录中版权费超过30，则提示用户，并回滚此操作。
use pubs
go
if exists(select name from sysobjects where name ='CheckRoyalty' and type='tr')
drop trigger CheckRoyalty
go
create trigger checkroyalty
on roysched
for insert as
if (select royalty from inserted) > 30
begin
print 'royaltytrigger：版权费不能超过 30'
print '请将版权费修改为小于 30 的值'
rollback transaction
end
insert into roysched values ('BU1032',2,5,90)
select * from roysched where title_id='BU1032'
----------------------------------------------------------------------------------------------------------------------
例三：创建一个触发器监视insert操作，若插入的记录中出版日期小于当前日期，则提示用户，并回滚此操作。
use pubs
go
if exists(select name from sysobjects where name ='checkpubdate' and type='tr')
drop trigger checkpubdate
go
create trigger checkpubdate
on titles
for insert as
if (select pubdate from inserted) < getdate()
begin
select * from inserted --查看内存表中的数据
print '出版日期小于当前日期'
rollback transaction
end
触发器示例测试
insert into titles(title_id,title,type,pubdate)
values('SW0001','test book','business','1990-1-1')
select * from inserted
----------------------------------------------------------------------------------------------------------------------
例四：列级update触发器示例
use pubs
go
if exists(select name from sysobjects where name ='NoUpdatePayterms' and type='tr')
drop trigger NoUpdatePayterms
go
CREATE TRIGGER NoUpdatePayterms
ON sales
FOR UPDATE AS
IF UPDATE (payterms)
BEGIN
PRINT '不能修改订单的付费条款'
ROLLBACK TRANSACTION
END
测试触发器的工作情况
update sales set qty=8
where stor_id='6380' and
ord_num='6871' and
title_id='BU1032'
update sales set payterms='aa'
where stor_id='6380' and
ord_num='6871' and
title_id='BU1032'
----------------------------------------------------------------------------------------------------------------------
例五：表级update触发器实例
use pubs
go
if exists(select name from sysobjects where name ='NoUpdateDiscount' and type='tr')
drop trigger NoUpdateDiscount
go
create trigger NoUpdateDiscount
on discounts
for update as
if (select discount from inserted) > 12
begin
select * from inserted --查看内存表中的数据
select * from deleted --查看内存表中的数据
print '不能指定大于 12% 的折扣'
rollback transaction
end
表级 UPDATE触发器测试
update discounts
set discount = 20
where stor_id = '8042'
----------------------------------------------------------------------------------------------------------------------
例六：列级update触发器示例
use northwind
go
建立登记修改人帐号的表
create table who_change
(
change_date datetime,
change_column varchar(50),
who varchar(50)
)
go
建立触发器
use northwind
go
if exists(select name from sysobjects where name ='tr_orderdetail_insupd' and type='tr')
drop trigger tr_orderdetail_insupd
go
create trigger tr_orderdetail_insupd
on
[order details]
for update
as
if update (unitprice)
begin
insert who_change
values (getdate(),'unitprice updated',user_name())
end
else if update (Quantity)
begin
insert who_change values(getdate(),'quantity updated',user_name())
end
else if update(discount)
begin
insert who_change values (getdate(),'discount updated',user_name())
end
go
测试触发器的工作情况
update [order details] set unitprice=2 where orderid=10248 and productid=1
update [order details] set Quantity=4 where orderid=10248 and productid=1
update [order details] set discount=0 where orderid=10248 and productid=1
----------------------------------------------------------------------------------------------------------------------
例七：触发器只能在当前数据库中创建。 但是，触发器可以引用其他数据库中的对象。(示例)
use sampledb
go
创建表test备用
create table test
(
aa int,
bb int
)
go
向test表中插入一些数据备用
insert into test values (1001,0)
insert into test values (1002,0)
insert into test values (1003,0)
创建另一个库备用
create database testdb
go
use testdb
go
在库testdb中再创建一个表备用
create table test_11
(
aa int,
bb int
)
go
在testdb库中的表test_11上创建一个insert触发器
use testdb
go
if exists(select name from sysobjects where name ='tri_test' and type='tr')
drop trigger tri_test
go
create trigger tri_test on test_11
for insert
as
update sampledb.dbo.test
set bb=bb+(select bb from inserted)
where aa= (select aa from inserted)
测试触发器的工作情况
insert into test_11 values (1002,2)
insert into test_11 values (1001,1)
----------------------------------------------------------------------------------------------------------------------
例八：DELETE触发器示例
use testdb
go
if exists(select name from sysobjects where name ='NoDelete9901' and type='tr')
drop trigger NoDelete9901
go
create trigger NoDelete9901
on pub_info
for delete AS
if (select pub_id from deleted) = '9901'
begin
print '不能删除出版商 9901 的详细信息'
rollback transaction
end
DELETE 触发器示例测试
delete pub_info
where pub_id = '9901'
----------------------------------------------------------------------------------------------------------------------
例九：视图上的 INSTEAD OF 触发器示例
use pubs
go
select * into bak_employee from employee
select * into bak_publishers from publishers
create view Emp_pub
as
select emp_id, lname, job_id, pub_name
from bak_employee e, bak_publishers p
where e.pub_id = p.pub_id
create trigger del_emp
on Emp_pub
instead of delete
as
select * from deleted --查看内存表中的数据
delete bak_publishers
where emp_id in
(select emp_id from deleted)
视图上的 INSTEAD OF触发器示例测试
delete Emp_pub
----------------------------------------------------------------------------------------------------------------------
例十：表上的INSTEAD OF触发器示例
use pubs
go
if exists(select name from sysobjects where name ='tri_deltitle' and type='tr')
drop trigger tri_deltitle
go
create trigger tri_deltitle on titles
instead of delete
as
print '不允许删除！'
delete from titles where title_id='BU1032'
----------------------------------------------------------------------------------------------------------------------
例十一：禁用触发器嵌套
exec sp_configure 'nested trigger', 0
例十二：启用触发器嵌套
exec sp_configure 'nested trigger', 1
----------------------------------------------------------------------------------------------------------------------
例十三：触发器嵌套示例
use sampledb
go
建立触发器
create table testa
(
a_id char(1),
a_name char(2)
)
insert into testa values('1','1')
insert into testa values('2','2')
insert into testa values('3','3')
create table testb
(
b_id char(1),
b_name char(2)
)
insert into testb values('1','1')
insert into testb values('2','2')
insert into testb values('3','3')
create table testc
(
c_id char(1),
c_name char(2)
)
insert into testc values('1','1')
insert into testc values('2','2')
insert into testc values('3','3')
触发器嵌套示例(1)
create trigger del_testa
on testa
instead of delete
as
delete testb
where b_id in
(select a_id from deleted)
create trigger del_testb
on testb
instead of delete
as
delete testc
where c_id in
(select b_id from deleted)
触发器嵌套示例测试(1)
delete testa where a_id = '1'
-- drop trigger del_testa2
-- drop trigger del_testb2
----------------------------------------------------------------------------------------------------------------------
触发器嵌套示例(2)
create trigger del_testa2
on testa
for delete
as
delete testb
where b_id in
(select a_id from deleted)
create trigger del_testb2
on testb
for delete
as
delete testc
where c_id in
(select b_id from deleted)
触发器嵌套示例测试(1)
delete testa where a_id = '1'
----------------------------------------------------------------------------------------------------------------------
例十四：触发器综合应用
创建触发器
use northwind
if exists(select name from sysobjects where name ='tr_product_update' and type='tr')
drop trigger tr_product_update
go
use northwind
go
create trigger tr_product_update on products
for update
as
declare @msg varchar(100)
select @msg = str(@@rowcount)+'employees updated by this statement'
print @msg
return
go
管理触发器
use northwind
go
sp_helptrigger products,delete
inerted和deleted表实现级联修改多数据表的触发器
use northwind
go
create trigger tr_suppliers_del
on suppliers
for delete
as
if @@rowcount=0
return
delete products from deleted d,suppliers s
where d.supplierid=s.supplierid
if @@error != 0
begin
rollback tran
return
end
return
go
----------------------------------------------------------------------------------------------------------------------
例十五：列级触发器应用
列级触发器 在通常情况下，用户对表所作的修改都只局限在表中的某些列上，而且，用户经常需要判断在某些列上的数据是否发生了修改，并在数据被修改时作出相应的反应。这种形式的触发器，被称为列级触发器。列级触发器主要
针对某些列实施监控。
use northwind
go
建立登记修改人帐号的表
create table who_change
(
change_date datetime,
change_column varchar(50),
who varchar(50)
)
go
建立触发器
create trigger tr_orderdetail_insupd
on
[order details]
for insert,uodate
as
if update (unitprice)
begin
insert who_change
values (getdate(),'unitprice updated',user_name())
end
else if update (Quantity)
begin
insert who_change values(getdate(),'quantity updated',user_name())
end
else if update(discount)
begin
insert who_change values (getdate(),'discount updated',user_name())
end
go