数据库触发器(非常好,力荐)

数据库触发器实例

一、课堂演示案例

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

触发器示例测试

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