數據庫觸發器案例

一、課堂演示案例

例一：創建一個簡單的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

歡迎關注公眾號：

?

總結

以上是生活随笔為你收集整理的【触发器】数据库_触发器实例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。