《数据库 触发器(超赞)》由会员分享，可在线阅读，更多相关《数据库 触发器(超赞)（11页珍藏版）》请在金锄头文库上搜索。
1、数据库触发器案例数据库触发器案例一、课堂演示案例一、课堂演示案例 例一：创建一个简单的例一：创建一个简单的 insert 触发器触发器 先创建一个数据库备用 create database sampledb gouse sampledb go在新创建的库中创建一个表备用 create table aa (a int,b int ) go在新创建的表上创建一个 insert 触发器 use sampledb goif exists(select name from sysobjects where name =tr_intoa and type=tr) drop trigger tr_intoa
2、 gocreate 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 goif exis
3、ts(select name from sysobjects where name =CheckRoyalty and type=tr) drop trigger CheckRoyalty gocreate trigger checkroyalty on roysched for insert as if (select royalty from inserted) 30 begin print royaltytrigger：版权费不能超过 30print 请将版权费修改为小于 30 的值 rollback transaction endinsert into roysched values
4、(BU1032,2,5,90)select * from roysched where title_id=BU1032 -例三：创建一个触发器监视例三：创建一个触发器监视 insert 操作，若插入的记录中出版日期小于当前日期，则提示操作，若插入的记录中出版日期小于当前日期，则提示 用户，并回滚此操作。用户，并回滚此操作。 use pubs goif exists(select name from sysobjects where name =checkpubdate and type=tr) drop trigger checkpubdate gocreate trigger checkpu
5、bdate on titles for insert as if (select pubdate from inserted) 12 beginselect * from inserted -查看内存表中的数据select * from deleted -查看内存表中的数据print 不能指定大于 12% 的折扣rollback transaction end表级 UPDATE 触发器测试 update discounts set discount = 20 where stor_id = 8042 -例六：列级例六：列级 update 触发器示例触发器示例 use northwind go建
6、立登记修改人帐号的表 create table who_change (change_date datetime,change_column varchar(50),who varchar(50) ) go建立触发器 use northwind goif exists(select name from sysobjects where name =tr_orderdetail_insupd and type=tr) drop trigger tr_orderdetail_insupd gocreate trigger tr_orderdetail_insupd on order details
7、 for update as if update (unitprice) begininsert who_changevalues (getdate(),unitprice updated,user_name() end else if update (Quantity)begininsert who_change values(getdate(),quantity updated,user_name() end else if update(discount) begininsert who_change values (getdate(),discount updated,user_nam
8、e() 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 -例七：触发器只能在当前数据库中创建。例七：触发器只能在当前数据库中创建。 但是，触发器可以引用其他数据库中的对象。但是，触
9、发器可以引用其他数据库中的对象。 (示例示例) 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 gouse testdb go在库 testdb 中再创建一个表备用create table test_11 (aa int,b
10、b int ) go在 testdb 库中的表 test_11 上创建一个 insert 触发器 use testdb goif exists(select name from sysobjects where name =tri_test and type=tr) drop trigger tri_test gocreate trigger tri_test on test_11 for insert asupdate sampledb.dbo.testset bb=bb+(select bb from inserted)where aa= (select aa from inserted)
11、测试触发器的工作情况 insert into test_11 values (1002,2)insert into test_11 values (1001,1) -例八：例八：DELETE 触发器示例触发器示例 use testdb goif exists(select name from sysobjects where name =NoDelete9901 and type=tr) drop trigger NoDelete9901 gocreate trigger NoDelete9901 on pub_info for delete AS if (select pub_id from
12、 deleted) = 9901 beginprint 不能删除出版商 9901 的详细信息rollback transaction endDELETE 触发器示例测试 delete pub_info where pub_id = 9901 -例九：视图上的例九：视图上的 INSTEAD OF 触发器示例触发器示例 use pubs goselect * into bak_employee from employee select * into bak_publishers from publisherscreate view Emp_pub as select emp_id, lname,
13、job_id, pub_name from bak_employee e, bak_publishers p where e.pub_id = p.pub_idcreate trigger del_emp on Emp_pub instead of delete asselect * from deleted -查看内存表中的数据delete bak_publishers where emp_id in (select emp_id from deleted)视图上的 INSTEAD OF 触发器示例测试 delete Emp_pub -例十：表上的例十：表上的 INSTEAD OF 触发器示例触发器示例 use pubs goif exists(select name from sysobjects where name =tri_deltitle and type=tr) drop trigger tri_deltitle gocreate trigger tri_deltitle on titles instead of delete as print
