数据库概论-第四章存储过程 函数 触发器

数据库概论第四章存储过程函数触发器存储过程存储过程将程序在服务器中预先编译好并存储起来，然后应用程序只需简单地向服务器发出调用该存储过程的请求即可存储过程的优点执行效率高重复使用统一的操作流程维护业务逻辑安全性存储过程的命令格式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====]]\\\\\///////*****<<<<<<<{}{}{}{}{}{}{}{}{}%%%%~~~~~~~~

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