数据库 触发器(超赞)

1、数据库触发器案例一、课堂演示案例例一：创建一个简单的insert触发器先创建一个数据库备用create database sampledbgouse sampledbgo在新创建的库中创建一个表备用create table aa( a int, b int)go在新创建的表上创建一个insert触发器use sampledbgoif exists(select name from sysobjects where name ='tr_intoa' and type='tr')drop trigger tr_intoagocreate trigger tr_int

2、oa on aafor insertasprint 'success inserted one row!'查看这个触发器的定义文本sp_helptext checkpubdate查看这个触发器的信息sp_help checkpubdate验证这个触发器的工作情况insert into aa values (1,2)-例二：创建一个触发器监视insert操作，若插入的记录中版权费超过30，则提示用户，并回滚此操作。use pubsgoif exists(select name from sysobjects where name ='CheckRoyalty' a

3、nd type='tr')drop trigger CheckRoyaltygocreate trigger checkroyaltyon royschedfor insert asif (select royalty from inserted) > 30beginprint 'royaltytrigger：版权费不能超过 30' print '请将版权费修改为小于 30 的值' rollback transactionendinsert into roysched values ('BU1032',2,5,90)sele

4、ct * from roysched where title_id='BU1032'-例三：创建一个触发器监视insert操作，若插入的记录中出版日期小于当前日期，则提示用户，并回滚此操作。use pubsgoif exists(select name from sysobjects where name ='checkpubdate' and type='tr')drop trigger checkpubdategocreate trigger checkpubdateon titlesfor insert as if (select pubd

5、ate from inserted) < getdate()begin select * from inserted -查看内存表中的数据 print '出版日期小于当前日期' rollback transactionend触发器示例测试insert into titles(title_id,title,type,pubdate)values('SW0001','test book','business','1990-1-1')select * from inserted-例四：列级update触发器示例us

6、e pubsgoif exists(select name from sysobjects where name ='NoUpdatePayterms' and type='tr')drop trigger NoUpdatePaytermsgoCREATE TRIGGER NoUpdatePaytermsON sales FOR UPDATE ASIF UPDATE (payterms)BEGIN PRINT '不能修改订单的付费条款'ROLLBACK TRANSACTIONEND测试触发器的工作情况update sales set qty=8

7、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 pubsgoif exists(select name from sysobjects where name ='NoUpdateD

8、iscount' and type='tr')drop trigger NoUpdateDiscountgocreate trigger NoUpdateDiscounton discounts for update asif (select discount from inserted) > 12begin select * from inserted -查看内存表中的数据 select * from deleted -查看内存表中的数据 print '不能指定大于 12% 的折扣' rollback transaction end表级 UPDA

9、TE 触发器测试update discounts set discount = 20 where stor_id = '8042'-例六：列级update 触发器示例use northwindgo建立登记修改人帐号的表create table who_change( change_date datetime, change_column varchar(50), who varchar(50)go建立触发器use northwindgoif exists(select name from sysobjects where name ='tr_orderdetail_in

10、supd' and type='tr')drop trigger tr_orderdetail_insupdgocreate trigger tr_orderdetail_insupdonorder detailsfor updateasif update (unitprice)begin insert who_change values (getdate(),'unitprice updated',user_name()endelse if update (Quantity) begin insert who_change values(getdate

11、(),'quantity updated',user_name() endelse if update(discount)begin insert who_change values (getdate(),'discount updated',user_name()endgo测试触发器的工作情况update order details set unitprice=2 where orderid=10248 and productid=1update order details set Quantity=4 where orderid=10248 and prod

12、uctid=1update order details set discount=0 where orderid=10248 and productid=1-例七：触发器只能在当前数据库中创建。 但是，触发器可以引用其他数据库中的对象。(示例)use sampledbgo创建表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)创建另一个库备用

13、create database testdbgouse testdbgo在库testdb中再创建一个表备用create table test_11( aa int, bb int)go在testdb库中的表test_11上创建一个insert触发器use testdbgoif exists(select name from sysobjects where name ='tri_test' and type='tr')drop trigger tri_testgocreate trigger tri_test on test_11for insert as se

14、t 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 testdbgoif exists(select name from sysobjects where name ='NoDelete9901' and type='tr')drop trigger NoDelete9901goc

15、reate trigger NoDelete9901on pub_info for delete ASif (select pub_id from deleted) = '9901'begin print '不能删除出版商 9901 的详细信息' rollback transaction endDELETE 触发器示例测试delete pub_info where pub_id = '9901'-例九：视图上的 INSTEAD OF 触发器示例use pubsgoselect * into bak_employee from employeese

16、lect * into bak_publishers from publisherscreate view Emp_pubasselect emp_id, lname, job_id, pub_name from bak_employee e, bak_publishers pwhere e.pub_id = p.pub_idcreate trigger del_empon Emp_pubinstead of deleteas select * from deleted -查看内存表中的数据 delete bak_publishers where emp_id in (select emp_i

17、d from deleted)视图上的 INSTEAD OF 触发器示例测试delete Emp_pub-例十：表上的INSTEAD OF 触发器示例use pubsgoif exists(select name from sysobjects where name ='tri_deltitle' and type='tr')drop trigger tri_deltitlegocreate trigger tri_deltitle on titlesinstead of deleteasprint '不允许删除！'delete from tit

18、les where title_id='BU1032'-例十一：禁用触发器嵌套exec sp_configure 'nested trigger', 0例十二：启用触发器嵌套exec sp_configure 'nested trigger', 1-例十三：触发器嵌套示例use sampledbgo建立触发器create table testa( a_id char(1), a_name char(2)insert into testa values('1','1')insert into testa values

19、('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

20、), 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_testaon testainstead of deleteas delete testb where b_id in (select a_id from deleted)create trigger del_te

21、stbon testbinstead of deleteas 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_testa2on testafor deleteas delete testb where b_id in (select a_id from deleted)create

22、trigger del_testb2on testbfor delete as delete testc where c_id in (select b_id from deleted)触发器嵌套示例测试(1)delete testa where a_id = '1'-例十四：触发器综合应用创建触发器use northwindif exists(select name from sysobjects where name ='tr_product_update' and type='tr')drop trigger tr_product_upda

23、tegouse northwindgocreate trigger tr_product_update on productsfor updateasdeclare msg varchar(100)select msg = str(rowcount)+'employees updated by this statement'print msgreturngo管理触发器use northwindgosp_helptrigger products,deleteinerted和deleted表实现级联修改多数据表的触发器use northwindgocreate trigger tr_suppliers_delon suppliersfor deleteasif rowcount