數據庫概論第四章存儲過程函數觸發器存儲過程存儲過程將程序在服務器中預先編譯好并存儲起來，然后應用程序只需簡單地向服務器發出調用該存儲過程的請求即可存儲過程的優點執行效率高重復使用統一的操作流程維護業務邏輯安全性存儲過程的命令格式createprocget_PROF_name@prof_idvarchar(10)asselectpnamefromPROFwherepno=@prof_idexecget_PROF_name‘p01'createproc

存儲過程名[@參數數據類型]as

存儲過程SQL體嵌套存儲過程createprocfactorial@param1intasdeclare@one_lessint,@answerintif(@param1<0or@param1>12)return-1if(@param1=0or@param1=1)select@answer=1else begin select@one_less=@param1-1

exec@answer=factorial@one_less if(@answer=-1)return-1 select@answer=@answer*@param1 if(@@error<>0)return-1 endreturn(@answer)n!=n*(n-1)!存儲過程

創建一個存儲過程delete_one操作，每次它刪除表中重復元組中的一個。例如假定表T={1,2,2,3,3,3,4,4,4,4}；對其執行一次delete_one，T={2,3,3,4,4,4}，再執行一次delete_one，T={3,4,4}。如何只保留重復行中的一個？用戶定義函數用戶定義函數是用于封裝經常執行的邏輯的子例程用戶定義函數與存儲過程的區別存儲過程只能返回一個整數值，用戶定義的函數可以返回各種數據類型的值存儲過程可以做任何數據庫修改，用戶定義的函數不可以修改數據庫的狀態或內容存儲過程只能由exec來執行，不能用在表達式中，用戶定義的函數可以由exec來執行，也可以用于表達式中（有些還可用于from子句中）存儲過程一般用于對數據庫的操作或設置，用戶定義的函數則適用于計算或提取數據標量函數

createfunction

function_name ([@parameter_name

data_type]) returns

return_data_type begin

function_body return

scalar_expression end標量函數createfunctionAverageGrade(@c_numbervarchar(8))returnsintasbegin declare@avgint select @avg=avg(grade) from SC where cno=@c_number

return@avgend標量函數select dbo.AverageGrade(‘c01')---------------------------------------------------select sno,gradefrom SCwhere grade>dbo.AverageGrade(‘c01') and cno=‘c01'內嵌表值函數

createfunction

function_name

( [@parameter_namedata_type]) returnsTABLE

return(select-stmt)內嵌表值函數createfunctionStudentsByClass(@c_numbervarchar(8))returnstableasreturn (select sno,grade from SC where cno=@c_number)-----------------------------------------------------select*fromStudentsByClass(‘c01')多語句表值函數

createfunction

function_name ([@parameter_name

data_type]) returns@return_variable

TABLE<table_definition> begin function_body

return endapply與表值函數apply可以看成是左右兩個表的連接操作，右邊的表是一個表值函數的返回結果，該表值函數的輸入參數是左邊表的某個或者幾個列返回每門課程的成績排在前三的學生createfunction

fn_CourseTop3Grade(@course_no

char(8))returns

table

asreturn(select

top3sno,gradefrom SCorderbygradedesc

)select C.cno,R.sno,R.gradefrom courseCcrossapply

fn_CourseTop3Grade(C.cno)asRorderbyC.cno觸發器ECA：Event-Condition-Action事件－條件－動作主動數據庫pullvspush規則數據庫主動：一切習慣中最好的那個觸發器觸發器是一條語句，當對數據庫做修改時，它自動被系統執行觸發器的定義指明監視哪些事件Insert、

delete、update指明什么條件下觸發器被執行指明觸發器執行的動作是什么觸發器的作用維護約束防止在選定一門課后刪除該課程商業規則如果客戶進行國際貨幣轉賬，向其發送e-mail消息監控傳感器感知到一氧化碳濃度級別提高，則開啟通風系統輔助緩存數據的維護當基礎表發生改變時，更新物化視圖簡化應用設計將核心編程邏輯從異常處理中分離出來觸發器的定義createtriggertrigger-namebeforeafter{}insertdeleteupdate{}ontable-name[ofcolumn-name]referencingoldrowasidentifiernewrowasidentifier{}triggered-SQL-statementforeachrowwhen(search-condition){}beginatomicendreferencingoldtableasidentifiernewtableasidentifier{}foreachstatement行級觸發器EMP(ENO,ENAME,SAL,JOB)職工工資增幅不得超過10%createtrigger

RAISE_LIMITafterupdateof

SAL

on

EMP

referencingnewrowas

nrowoldrowas

orow

foreachrow when

(nrow.SAL>1.1*orow.SAL)

begin

signalSQLSTATE‘7500’(“Salaryincrease10%)

end

行級觸發器當帳戶透支時，將帳戶余額設為0，并建一筆貸款，其金額為透支額createtrigger

overdraft-triggerafterupdateon

account

referencingnewrowas

nrowforeachrow

when

nrow.balance<0

beginatomic insertinto

borrower (selectcustomer-name,account-number

from

depositor

where

nrow.account-number=depositor.account-number);

insertinto

loanvalues(nrow.account-number, nrow.branch-name,–nrow.balance);

update

accountsetbalance=0

whereaccount.account-number=nrow.account-number end

語句級觸發器EMP(ENO,ENAME,SAL,JOB)職工平均工資不得低于800createtrigger

RAISE_LIMITafterupdateof

SAL

on

EMPreferencingnewtableas

n_tboldtableas

o_tbforeachstatementwhen

(800>(selectavg(SAL)fromEMP)

begin

deletefromEMP whereENOin(selectENOfromn_tb) insertintoEMP(select*fromo_tb)

end

before觸發器如果插入的成績不及格，則將其改為60分createtrigger

pass-grade-triggerbeforeinsert

on

SCreferencingnewrowas

nrowforeachrowwhen

(nrow.GRADE<60)

begin

setnrow.GRADE=60

end觸發器：SQLServercreatetriggerS_SC_DeleteonSafterdeleteas if@@rowcount=0 return delete fromSC whereSC.S#=deleted.S#go deleted

和inserted

是邏輯（概念）表。這些表在結構上類似于定義觸發器的表（也就是在其中嘗試用戶操作的表）；這些表用于保存用戶操作可能更改的行的舊值或新值。例如，若要檢索deleted表中的所有值，可以執行如下查詢：

select* from

deletedcreatetriggerreminder_trigger

on

empafterdeleteas if@@rowcount=0return declare @msg

varchar(100),@enochar(10),@enamechar(10) declare cursorDeleted

cursorfor select eno,ename

fromdeleted open cursorDeleted fetchnextfrom

cursorDeleted

into@eno,@ename while@@fetch_status=0 begin set @msg= '被刪除的員工主碼是:'+@eno+ '被刪除的員工主碼是:'+@.ename print@msg fetchnextfrom

cursorDeleted

into@eno,@ename end close

cursorDeleted

deallocate

cursorDeleted觸發動作影響到多行時使用游標遞歸觸發器設計觸發器，保證部門預算始終等于該部門預算與其所有子部門預算之和dept_nameparent_namebudgetd1d210d2d3100d3null500dept_nameparent_namebudgetd1d220d2d3110d3null510遞歸觸發器createtriggerbudgetondeptafterupdateasif(selectparent_namefrominserted)isnullreturnupdate deptset budget=budget+(selectbudgetfrominserted)–

(selectbudgetfromdeleted)where dept_name=(selectparent_namefrominserted)execsp_dboptionuniversity,'recursivetriggers',true替代觸發器createviewCOMPUTER_PROFas

(selectpno,PNAME,SAL

fromPROF，DEPT

wherePROF.dno=DEPT.dno

andDEPT.dname=“計算機系”)insertintoCOMPUTER_PROF(‘p01’,‘tom’,800)轉化為：insertintoPROF(‘p01’,‘tom’,800,null,null)我們所期望的：insertintoPROF(‘p01’,‘tom’,800,null,‘計算機系系號’)替代觸發器createtriggerINSERT_VIEWonCOMPUTER_PROFinsteadofinsertas declare@d_nochar(10) set@d_no=(selectdno fromDEPT wheredname=“計算機系”) insertintoPROF(inserted.pno,inserted.pname, inserted.sal,null,@d_no)替代觸發器：更新不可更新的視圖createviewjoin_viewas selectTable1.aasa1,Table2.aasa2 from Table1joinTable2onTable1.a=Table2.aTable1a124Table2a112233createtriggerDELETE_JOINonjoin_viewinsteadofdeleteas deleteTable1whereain(selecta1fromdeleted) deleteTable2whereain(selecta2fromdeleted)觸發器的沖突當一個事件同時激活多個觸發器時，觸發順序如何確定？有序沖突解決方案輪流計算觸發器的前提條件。當一個條件求值為真時，執行相應的觸發器；當執行完成時，考慮下一個觸發器分組沖突解決方案同時計算所有觸發器的前提條件，然后調度執行所有前提條件為真的觸發器觸發器的沖突sp_settriggerorder @triggername='MyTrigger', @order='first', @stmttype='UPDATE'

Trigger1ON 在課程注冊表中插入一行IF 超過課程班容量THEN 將未滿足請求通知注冊者ON 在課程注冊表中插入一行IF 超過課程班容量THEN 將請求放入等待列表Trigger2DDL觸發器DDL觸發器，是對服務器或者數據庫級別的事件作出響應，例如各種create,alter,drop,grant,revoke操作等利用DDL觸發器，可以保證架構的修改符合業務規則，或者對數據庫進行安全審核等可以使用eventdata()函數獲得引發觸發器的事件信息，它返回的是XML數據類型.對于不同的事件，eventdata()函數返回的值是不一樣的要查看所有事件的架構定義，可以訪問網頁/sqlserver/2006/11/eventdata/events.xsdDDL觸發器<EVENT_INSTANCE><EventType>CREATE_TABLE</EventType><PostTime>2012-08-13T09:28:31.140</PostTime><SPID>55</SPID><ServerName>LJCHEN-PC</ServerName><LoginName>ljchen-PC\ljchen</LoginName><UserName>dbo</UserName><DatabaseName>demoDB</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MyTable</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptionsANSI_NULLS="ON"ANSI_NULL_DEFAULT="ON"ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"ENCRYPTED="FALSE"/><CommandText>CREATETABLEMyTable(Column1int);</CommandText></TSQLCommand></EVENT_INSTANCE>DDL觸發器目錄視圖sys.trigger_event_types中包含了關于觸發器的所有觸發事件或事件組的描述：select *from sys.trigger_event_typesorderby type_nameDDL觸發器這些事件總計有260個，可以對其按照彼此之間的隸屬關系分組，形成不同層次的事件組例如，指定DDL_TABLE_EVENTS事件就相當于指定了CREATETABLE、ALTERTABLE和DROPTABLE事件DDL觸發器禁止在數據庫上執行建表操作createtriggernocreateTbondatabaseforcreate_tableas

select

eventdata().value( '(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(max)')raiserror('不允許在本數據庫上創建表',16,1)rollbackDDL觸發器禁止在數據庫上創建沒有指定主碼的表createtriggercreateTbwithPKondatabaseforcreate_tableas declare@tb_namevarchar(100)

set @tb_name=eventdata().value( '(/EVENT_INSTANCE/SchemaName)[1]','sysname')+'.'+eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

ifobjectproperty(object_id(@tb_name),'TableHasPrimaryKey')=0

begin print'建表時未指定主碼'

rollback end====]]\\\\\///////*****<<<<<<<{}{}{}{}{}{}{}{}{}%%%%~~~~~~~~

////////^^!~~~~~::---))))*