数据库 触发器(超赞).doc

数据库触发器案例一、课堂演示案例例一：创建一个简单的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_intoa 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 and 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)select * 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 pubdate from inserted) 12begin select * from inserted -查看内存表中的数据 select * from deleted -查看内存表中的数据 print 不能指定大于 12% 的折扣 rollback transaction end表级 UPDATE 触发器测试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_insupd 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(),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 productid=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)创建另一个库备用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 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 testdbgoif exists(select name from sysobjects where name =NoDelete9901 and type=tr)drop trigger NoDelete9901gocreate trigger NoDelete9901on pub_info for delete ASif (select pub_id from deleted) = 9901begin print 不能删除出版商 9901 的详细信息 rollback transaction endDELETE 触发器示例测试delete pub_info where pub_id = 9901-例九：视图上的 INSTEAD OF 触发器示例use pubsgoselect * into bak_employee from employeeselect * 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_id 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 titles 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(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_testaon testainstead of deleteas delete testb where b_id in (select a_id from deleted)create trigger del_testbon 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 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_updategouse northwindgocreate trigger tr_product_update on productsfor updateasdeclare msg varchar(100)select msg = str(rowcount)+employees updated by this statementprint msgreturngo管理触发器use northwindgosp_helptrigger products,deleteinerted和deleted表实现级联修改多数据表的触发器use northwindgocreate trigger tr_suppliers_delon suppliersfor deleteasif rowcount=0 returndelete products from deleted d,suppliers swhere d.supplierid=s.supplieridif error != 0begin rollback tran returnendreturn