數(shù)據(jù)庫(kù) 觸發(fā)器(超贊)

1、數(shù)據(jù)庫(kù)觸發(fā)器案例一、課堂演示案例例一：創(chuàng)建一個(gè)簡(jiǎn)單的insert觸發(fā)器先創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)備用create database sampledbgouse sampledbgo在新創(chuàng)建的庫(kù)中創(chuàng)建一個(gè)表備用create table aa( a int, b int)go在新創(chuàng)建的表上創(chuàng)建一個(gè)insert觸發(fā)器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!'查看這個(gè)觸發(fā)器的定義文本sp_helptext checkpubdate查看這個(gè)觸發(fā)器的信息sp_help checkpubdate驗(yàn)證這個(gè)觸發(fā)器的工作情況insert into aa values (1,2)-例二：創(chuàng)建一個(gè)觸發(fā)器監(jiān)視insert操作，若插入的記錄中版權(quán)費(fèi)超過(guò)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：版權(quán)費(fèi)不能超過(guò) 30' print '請(qǐng)將版權(quán)費(fèi)修改為小于 30 的值' rollback transactionendinsert into roysched values ('BU1032',2,5,90)sele

4、ct * from roysched where title_id='BU1032'-例三：創(chuàng)建一個(gè)觸發(fā)器監(jiān)視insert操作，若插入的記錄中出版日期小于當(dāng)前日期，則提示用戶，并回滾此操作。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 -查看內(nèi)存表中的數(shù)據(jù) print '出版日期小于當(dāng)前日期' rollback transactionend觸發(fā)器示例測(cè)試insert into titles(title_id,title,type,pubdate)values('SW0001','test book','business','1990-1-1')select * from inserted-例四：列級(jí)update觸發(fā)器示例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 '不能修改訂單的付費(fèi)條款'ROLLBACK TRANSACTIONEND測(cè)試觸發(fā)器的工作情況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'-例五：表級(jí)update觸發(fā)器實(shí)例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 -查看內(nèi)存表中的數(shù)據(jù) select * from deleted -查看內(nèi)存表中的數(shù)據(jù) print '不能指定大于 12% 的折扣' rollback transaction end表級(jí) UPDA

9、TE 觸發(fā)器測(cè)試update discounts set discount = 20 where stor_id = '8042'-例六：列級(jí)update 觸發(fā)器示例use northwindgo建立登記修改人帳號(hào)的表create table who_change( change_date datetime, change_column varchar(50), who varchar(50)go建立觸發(fā)器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測(cè)試觸發(fā)器的工作情況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-例七：觸發(fā)器只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建。 但是，觸發(fā)器可以引用其他數(shù)據(jù)庫(kù)中的對(duì)象。(示例)use sampledbgo創(chuàng)建表test備用create table test( aa int, bb int)go向test表中插入一些數(shù)據(jù)備用insert into test values (1001,0)insert into test values (1002,0)insert into test values (1003,0)創(chuàng)建另一個(gè)庫(kù)備用

13、create database testdbgouse testdbgo在庫(kù)testdb中再創(chuàng)建一個(gè)表備用create table test_11( aa int, bb int)go在testdb庫(kù)中的表test_11上創(chuàng)建一個(gè)insert觸發(fā)器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)測(cè)試觸發(fā)器的工作情況insert into test_11 values (1002,2)insert into test_11 values (1001,1)-例八：DELETE 觸發(fā)器示例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 的詳細(xì)信息' rollback transaction endDELETE 觸發(fā)器示例測(cè)試delete pub_info where pub_id = '9901'-例九：視圖上的 INSTEAD OF 觸發(fā)器示例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 -查看內(nèi)存表中的數(shù)據(jù) delete bak_publishers where emp_id in (select emp_i

17、d from deleted)視圖上的 INSTEAD OF 觸發(fā)器示例測(cè)試delete Emp_pub-例十：表上的INSTEAD OF 觸發(fā)器示例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'-例十一：禁用觸發(fā)器嵌套exec sp_configure 'nested trigger', 0例十二：?jiǎn)⒂糜|發(fā)器嵌套exec sp_configure 'nested trigger', 1-例十三：觸發(fā)器嵌套示例use sampledbgo建立觸發(fā)器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')觸發(fā)器嵌套示例(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)觸發(fā)器嵌套示例測(cè)試(1)delete testa where a_id = '1'- drop trigger del_testa2- drop trigger del_testb2-觸發(fā)器嵌套示例(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)觸發(fā)器嵌套示例測(cè)試(1)delete testa where a_id = '1'-例十四：觸發(fā)器綜合應(yīng)用創(chuàng)建觸發(fā)器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管理觸發(fā)器use northwindgosp_helptrigger products,deleteinerted和deleted表實(shí)現(xiàn)級(jí)聯(lián)修改多數(shù)據(jù)表的觸發(fā)器use northwindgocreate trigger tr_suppliers_delon suppliersfor deleteasif rowcount