存储过程触发器的例子.docx
《存储过程触发器的例子.docx》由会员分享，可在线阅读，更多相关《存储过程触发器的例子.docx（20页珍藏版）》请在冰豆网上搜索。
存储过程触发器的例子
存储过程：
1.CREATEPROCEDUREloving
AS
BEGIN
SELECT*fromstudentwheressex='女'
END：
执行
Execloving
2.CREATEPROCEDUREloving1
as
BEGIN
SELECT*fromstudent
end
3.CREATEPROCEDUREpr1_sc_ins
@Param1char（10）,@Param2char
（2）,@Param3real
AS
BEGIN
insertintosc（sno,cno,grade）values（@Param1,@Param2,@Param3）
END
执行:
EXECpr1_sc_ins'200215121','4',85或
EXECpr1_sc_ins@Param1='200215121',@Param2='6',@Param3=85
4.CREATEPROCEDUREs_grade
@snamechar（8）,@sgraderealoutput
AS
SELECT@sgrade=gradefromscjoinstudentonstudent.sno=sc.sno
wheresname=@sname
执行：
DECLARE@sgradereal
EXECs_grade
@sname='李勇',
@sgrade=@sgradeOUTPUT
5.CREATEPROCEDUREs_grade1
@ssnochar（9）,@sgraderealoutput
AS
SELECT@sgrade=avg（grade）fromsc
wheresno=@ssno
执行：
DECLARE@sgradereal
EXECs_grade1
@ssno='200215121',
@sgrade=@sgradeOUTPUT
SELECT@sgrade
6.CREATEPROCEDUREs_grade2
@youbiaocursorvaryingoutput
AS
set=cursorforward_only --forward_only表示从第一条开始往下
--[static]（这里可以添加）
for
SELECT@sgrade=avg（grade）fromsc
groupbysno
open@youbiao --打开游标
go
执行：
declare@youbiao2cursor --定义一个游标作参数，用于上面那个存储过程
execguocheng5@youbiao=@youbiao2output
SELECT@youbiao
7.ifexists（select*fromsysobjectswherename='guocheng'andtype='p'） ---判定存储过程是否存在
dropprocguocheng
go
createprocguocheng
@idint,
@namenvarchar（50）
as
setnocounton
setrowcount@id --设置要查询多少条数据
--select*fromadminurlwhereurlnamelike@name --
（1）这里的通配符是在传参数的时候带上的
select*fromstudentwhereurlnamelike'%'+@name+'%' --
（2）这里的通配符是程序自带的，推荐这个
go
8.加密存储过程和实现另外一种模糊查询（用到系统函数）-------------
ifexists（selectnamefromsysobjectswherename='guocheng2'andtype='p'）
dropprocguocheng2 --判断是否存在
go
createprocguocheng2
@namenvarchar（100）
withencryption ---实现对存储过程加密，以后谁也看不到内容，所以事先要有备份
as
setnocounton
select*fromstudentwherecharindex（@name,urlname）>0 --chaindex的作用相当于Like@name
go
dropprocguocheng2
execguocheng2'功能'
----------------存储过程的几种返回值（output,return,select）-----------------
--
（1）output存储过程[注意在.NET中是怎样接受的]
ifexists（selectnamefromsysobjectswherename='guocheng3'andtype='p'）
dropprocguocheng3 --判断是否存在
go
createprocguocheng3
@nintoutput, ---申明是输出参数
@namenvarchar（50）
withencryption ---加密
as
setnocounton --不显示记录数，提高网络
select*fromstudentwhereurlnamelike'%'+@name+'%'
set@n=@@rowcount --赋值
go
--开始测试
declare@nint --定义输出参数
execguocheng3@noutput,'管理'
print@n --验证是否输出参数已经赋值
----
（2）return存储过程,切记return返回的必须是整型值[注意在.NET中是怎样接受的]
ifexists（selectnamefromsysobjectswherename='guocheng4'andtype='p'）
dropprocguocheng4
go
createprocguocheng4
@namenvarchar（50）,
@nint
withencryption
as
setnocounton
setrowcount@n
select*fromstudentwhereurlnamelike'%'+@name+'%'
if（@@rowcount>0）
return1
else
return0
go
----开始测试
declare@mint
exec@m=guocheng4'管理',4
print@m
------（3）:
带返回游标的存储过程,并且游标只能是output类型
--【1.定义】
ifexists（select*fromsysobjectswherename='guocheng5'andtype='p'）
dropprocguocheng5 ---判断存在否
go
createprocguocheng5
@youbiaocursorvaryingoutput ---定义一个游标输出参数，varying表示可以变化的
as
set@youbiao=cursorforward_only --forward_only表示从第一条开始往下
--[static]（这里可以添加）
forselectcommentfromadminurl --static表示建立一个临时副本，不允许修改基表，如果没有就可以修改基表
open@youbiao --打开游标
go
--【2.使用】
ifexists（selectnamefromsysobjectswherename='guocheng6'andtype='p'）
dropprocguocheng6
go
createprocguocheng6 --用来调用guocheng5
as
declare@nnvarchar（100） --定义一个变量用于接收游标的移动的每条记录
declare@youbiao2cursor --定义一个游标作参数，用于上面那个存储过程
execguocheng5@youbiao=@youbiao2output --赋值给定义个游标
fetchnextfrom@youbiao2into@n --每条记录赋值
while（@@fetch_status=0）
begin
if（@n='2m'）
updateadminurlsetcomment=comment+'M'wherecurrentof@youbiao2 --能进行修改的前提是上面定义的游标没有static
else
updateadminurlsetcomment=comment+'O'wherecurrentof@youbiao2 --能进行修改的前提是上面定义的游标没有static
fetchnextfrom@youbiao2into@n --循环赋值
end
close@youbiao2
deallocate@youbiao2
go
exec guocheng6 --开始执行存储过程6
---------------------------------------------------------
--------创建一个带默认值的带判断的存储过程
ifexists（selectnamefromsysobjectswherename='guocheng7'andxtype='p'）
dropprocguocheng7
go
createprocguocheng7
@namenvarchar（100）=null, ----定义一个默认值是空的输入参数
@nintoutput ----定义一个输出参数
as
if@nameisnull ----判断参数是否为空
begin
print'error!
!
!
'
return
end
select@n=count（*）fromstudentwhereurlnamelike'%'+@name+'%' ---给输出参数赋值
print@n
go
declare@mint ----定义临时变量
execguocheng7'管理',@m ----执行
execguocheng7 @n=@m ---执行带默认值的,但是不能写成execguocheng7@m
-----------执行远程存储过程--------
---创建连接服务器
execsp_addlinkedserver'srv_lnk','','sqloledb','210.38.202.16'
execsp_addlinkedsrvlogin'srv_lnk','false',null,'sa','sgu3197'
---这个允许调用链接服务器上的存储过程
execsp_serveroption'srv_lnk','rpcout','true'
go
---执行远程存储过程，其中srv_lnk是远程数据库的别名
execsrv_shop.dbo.guocheng1
----------------------------------
--------设置或撤销自动执行存储过程---
usemaster --必须设置这个数据库
execsp_procoption'存储过程名字','startup','on' --设置自动执行的存储过程
execsp_procoption'存储过程名字','startup','off' --取消自动执行的存储过程
go
----------------------------------
触发器:
DML触发器，属于某个表
1.CREATETRIGGERtr1
ONsc
FORINSERT,UPDATE,DELETE
AS
PRINT'inserted表：
'
Select*frominserted
PRINT'deleted表：
'
Select*fromdeleted
执行insertintoscvalues（'200215127','1',89）
updatescsetgrade=78wheresno='200215127'
2.CREATETRIGGERtr_s1
ONstudent
FORINSERT,UPDATE
AS
Begin
DECLARE@bhvarchar（6）
SELECT@bh=inserted.snoFROMinserted
Select@bh
/*获取插入或更新操作时的新值（学号）*/
End
执行：
insertintostudent（sno）values（'200215141'）
3.CREATETRIGGERsc_insupd
ONsc
FORINSERT,UPDATE
AS
DECLARE@cjint
SELECT@cj=inserted.gradefrominserted
IF（@cj<0or@cj>100）
BEGIN
RAISERROR（'成绩的取值必须在到之间',16,1）
ROLLBACKTRANSACTION
END
执行：
insertintoscvalues（'200215127','2',109）
在消息框中显示：
你删除一条数据，操作成功
inserted表：
（1行受影响）
deleted表：
（0行受影响）
消息50000，级别16，状态1，过程sc_insupd，第9行
成绩的取值必须在到之间
消息3609，级别16，状态1，第1行
事务在触发器中结束。
批处理已中止。
4.CREATETRIGGERtr_del_sONstudent
FORDELETE
as
BEGIN
DECLARE@bhchar（5）
Select@bh=deleted.snofromdeleted
Deletescwheresno=@bh
END
执行：
deletefromstudentwheresno='200215127'
在消息框中显示
5．usesss
ifobject_id（'loving20','TR'）isnotnull
DROPTRIGGERLOVING20
GO
CREATETRIGGERLOVING20ONCOURSE
AFTERDELETE
AS
BEGIN
--SETNOCOUNTONaddedtopreventextraresultsetsfrom
--interferingwithSELECTstatements.
SETNOCOUNTON;
--Insertstatementsfortriggerhere
PRINT'你删除一条数据，操作成功'
END
GO
执行deletefromcoursewherecno='8'
在消息框中显示：
你删除一条数据，操作成功
（1行受影响）
6．
usesss
ifobject_id（'loving','TR'）isnotnull
DROPTRIGGERLOVING
GO
CREATETRIGGERLOVINGONCOURSE
AFTERINSERT,UPDATE
AS
RAISERROR（'NOTIFYCOURSRRELATIONS',16,10）
GO
执行：
insertintocoursevalues（'9','数据库系统原理','5',3）
在消息框中显示：
消息50000，级别16，状态10，过程LOVING2，第4行
NOTIFYCOURSRRELATIONS
（1行受影响）
执行：
updatecoursesetCcredit=3wherecno='1'
在消息框中显示：
消息50000，级别16，状态10，过程LOVING2，第4行
NOTIFYCOURSRRELATIONS
（1行受影响）
7.usesss
ifobject_id（'loving10','TR'）isnotnull
DROPTRIGGERLOVING10
GO
CREATETRIGGERLOVING10ONCOURSE
AFTERINSERT
AS
PRINT'你插入一条数据，操作成功'
GO
执行：
insertintocoursevalues（'9','数据库系统原理','5',3）
在消息框中显示：
消息50000，级别16，状态10，过程LOVING2，第4行
NOTIFYCOURSRRELATIONS
你插入一条数据，操作成功
．8。
CREATETRIGGERs_reminder
ONStudent
WITHENCRYPTION
AFTERINSERT,UPDATE
AS
RAISERROR（'不能对该表执行添加、更新操作',16,10）
ROLLBACK
GO
执行：
insertintostudent（sno）values（'200215142'）
在消息框中显示：
（1行受影响）
消息50000，级别16，状态10，过程s_reminder，第6行
不能对该表执行添加、更新操作
消息3609，级别16，状态1，第1行
事务在触发器中结束。
批处理已中止。
ALTERTRIGGERs_reminder
ONStudent
AFTERINSERT
AS
RAISERROR（'不能对该表执行添加操作',16,10）
ROLLBACK
在消息框中显示：
（1行受影响）
消息50000，级别16，状态10，过程s_reminder，第5行
不能对该表执行添加操作
消息3609，级别16，状态1，第1行
事务在触发器中结束。
批处理已中止。
（1行受影响）
9.CREATETABLEsss（
[sno][char]（9）referencesstudent（sno）,
[sage][smallint]NULL,
[updatatime][datetime]NULL
）
Createtriggercompensation
onStudent
afterupdate
as
if@@rowcount=0return
ifupdate（sage）
begin
insertsss
selectsno,sage,getdate（）
frominserted
end
执行：
updatestudentsetsage=19wheresno='200215121'
假设表结构是
表（id[主键],Count,c1,c2,c3,c4）
数据类型都是int
10.createtriggerMytrg
on表名
insteadofinsert
as
declare@Myii
begin
select@Myii=Countfrominserted
if（@Myii>0）
insertinto表名select*frominserted
else
begin
--这里执行你说的"满足条件是触发"的事情
--范例中是Count<=0执行的操作,这里我是不执行插入,所以什么都不需要写
end
end
--以上范例是当Count列的值是>0的值就执行插入新数据,否则不执行插入的范例
用UPDATE是一样的
代码如下:
11.createtriggerMytrg
on表名
insteadofupdate
as
declare@Myiiint,@idint,@c1int,@c2int,@c3int,@c4int
begin
select@Myii=Count,@id=id,@c1=c1,@c2=c2,@c3=c3,@c4=c4frominserted
if（@Myii>0）
update表名setCount=@Myii,c1=@c1,c2=@c2,c3=@c3,c4=@c4whereid=@id
--如果你再要更新其他字段,必须在这一个触发器实现,所以这个范例是你更新任意字段（除ID）
else
begin
--这里执行你说的"满足条件是触发"的事情
--范例中是Count<=0执行的操作,这里我是不执行更新,所以什么都不需要写
end
end
DDL触发器
1．CREATETRIGGERsafety
ONDATABASE
FORDROP_TABLE,ALTER_TABLE
AS
PRINT'YoumustdisableTrigger"safety"todroporaltertables!
'
ROLLBACK
执行：
droptablesc1
在消息框中显示：
YoumustdisableTrigger"safety"todroporaltertables!
消息3609，级别16，状态2，第2行
事务在触发器中结束。
批处理已中止。
2．CREATETRIGGERsafety
ONDATABASE
FORCREATE_TABLE
AS
PRINT'CREATETABLEIssued.'
SELECTEVENTDATA（）.value（'（/EVENT_
升级会员 