【触发器】数据库_触发器实例

数据库触发器案例

一、课堂演示案例

例一：创建一个简单的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

欢迎关注公众号：