百度360必应搜狗淘宝本站头条
mqtt服务器xftp6vscode使用技巧mybatiscollectionkeyerror
当前位置：网站首页 > 热门文章 > 正文

数据库基础05:存储过程、触发器的创建、执行、修改与删除

bigegpt 2024-08-04 11:38 8 浏览

??本文介绍基于Microsoft SQL Server软件，实现数据库存储过程与触发器的创建、执行、修改与删除等操作。

??数据库系列文章请见专栏：数据库基础_疯狂学习GIS的博客-CSDN博客。

??系列文章中示例数据来源于《 SQL Server实验指导（2005版）》一书。尊重版权，因此遗憾不能将相关示例数据一并提供给大家；但是依据本系列文章的思想与对操作步骤、代码的详细解释，大家用自己手头的数据，可以将相关操作与分析过程加以完整重现。

1 交互式创建并执行——存储过程一

（1） 启动Microsoft SQL Server 2008 R2软件；

（2） 在“对象资源管理器”窗格中，在“数据库”处右键，在弹出的菜单中选择“附加”选项；

（3） 选择需要加以附加的jxsk数据库物理文件，选择定位文件夹“G:\sql\chutianjia sql”并选择对应数据库jxsk的物理文件并选择“确定”按钮，再次选择“确定”即可；

（4） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“存储过程”；右击，在弹出的窗口中选择“新建存储过程”选项；如下图；

（5） 将原有模板语句：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

修改为：

CREATE PROCEDURE Pro_Qsinf
@SNO_IN CHAR(8)='S2',@SNAME_OUT CHAR(8) OUTPUT,
@SAGE_OUT INT OUTPUT, @DEPT_OUT CHAR(10) OUTPUT
AS SELECT @SNAME_OUT=SN,@SAGE_OUT=AGE,@DEPT_OUT=DEPT
FROM S WHERE SNO=@SNO_IN

（6） 单击对勾按钮进行语法检查，如下图；单击“工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（7） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“存储过程”；看到已存在通过上述步骤建立的存储过程；

2 交互式创建并执行——存储过程二

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
DECLARE @SNO_IN CHAR(8),
@SNAME_OUT CHAR(8),
@SAGE_OUT INT,
@SDEPT_OUT CHAR(10)
EXEC Pro_Qsinf DEFAULT, @SNAME_OUT OUTPUT,@SAGE_OUT OUTPUT,@SDEPT_OUT OUTPUT
PRINT @SNAME_OUT
PRINT @SAGE_OUT
PRINT @SDEPT_OUT
SELECT @SNO_IN='S4'
EXEC PRO_QSINF @SNO_IN, @SNAME_OUT OUTPUT,@SAGE_OUT OUTPUT,@SDEPT_OUT OUTPUT
PRINT @SNAME_OUT
PRINT @SAGE_OUT
PRINT @SDEPT_OUT
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

3 用T-SQL创建——存储过程一

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

CREATE PROCEDURE Pro_Qscore
@SNAME_IN CHAR(8), @CNAME_IN CHAR(10),@SCORE_OUT TINYINT OUTPUT
AS SELECT @SCORE_OUT=SCORE FROM S,C,SC
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO
AND SN=@SNAME_IN AND CN=@CNAME_IN

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“存储过程”；看到已存在通过上述步骤建立的存储过程；

4 用T-SQL创建——存储过程二

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
DECLARE @SNAME_IN CHAR(8),
@CNAME_IN CHAR(8),
@SCORE_OUT TINYINT
SELECT @SNAME_IN='李思'
SELECT @CNAME_IN='程序设计'
EXEC PRO_QSCORE @SNAME_IN,@CNAME_IN,@SCORE_OUT OUTPUT PRINT RTRIM(@SNAME_IN)+'='+LTRIM (STR(@SCORE_OUT))
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句,如下图；

5 交互式修改存储过程

（1） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“存储过程”；右击存储过程Pro_Qsinf，在弹出的窗口中选择“修改”选项，如下图；

（2） 将原有模板语句修改为

USE [jxsk]
GO
/****** Object:  StoredProcedure [dbo].[Pro_Qsinf]    Script Date: 04/26/2019 15:04:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Pro_Qsinf]
@SNO_IN CHAR(2)='S2',@SNAME_OUT CHAR(8) OUTPUT,
@SAGE_OUT TINYINT OUTPUT, @DEPT_OUT CHAR(10) OUTPUT
AS SELECT @SNAME_OUT=SN,@SAGE_OUT=AGE,@DEPT_OUT=DEPT
FROM S WHERE SNO=@SNO_IN

（3） 单击对勾按钮进行语法检查，如下图；单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

6 用T-SQL修改存储过程

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
ALTER PROCEDURE PRO_QSINF
@SNO_IN CHAR(2)='S1',@SNAME_OUT CHAR(8) OUTPUT,
@SSEX_OUT CHAR(2) OUTPUT,@DEPT_OUT CHAR(10) OUT
AS 
SELECT @SNAME_OUT=SN,@SSEX_OUT=SEX,@DEPT_OUT=DEPT
FROM S WHERE SNO=@SNO_IN
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“存储过程”；点击存储过程Pro_Qsinf，选择“参数”，可发现其定义发生变化；

7 交互式删除存储过程

（1） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“存储过程”；右击存储过程Pro_Qsinf，在弹出的窗口中选择“删除”选项；

（2） 选择确定按钮，存储过程即被删除；如下图；

8 用T-SQL删除存储过程

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
DROP PROCEDURE PRO_QSCORE
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；删除后结果如下下图；

9 交互式为数据库表S创建一级联更新触发器——创建触发器

（1） 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.c”；右击触发器，在弹出的窗口中选择“新建触发器”选项；如下图；

（2） 窗口内原有语句为：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for trigger here
END
GO

需将其更改为：

CREATE TRIGGER TRIGGER_S
ON S FOR UPDATE AS IF UPDATE(SNO)
BEGIN
DECLARE @SNO_NEW CHAR(2),@SNO_OLD CHAR(2)
SELECT @SNO_NEW=SNO FROM inserted
SELECT @SNO_OLD=SNO FROM deleted
UPDATE SC SET SNO=@SNO_NEW WHERE SNO=@SNO_OLD
END

（3） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

10 交互式为数据库表S创建一级联更新触发器——验证触发器

（1） 查看数据库表S与SC，如下图；

（2） 删除原有S与SC之间的外键关系；修改S表中S1为S9，执行操作，如下图；

（3） 查看SC表中数据，发现其S1已改变为S9，且位置也发生相应变化，如下图；

11 交互式为数据库表SC创建一限制更新触发器——创建触发器

（1） 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.SC”；右击触发器，在弹出的窗口中选择“新建触发器”选项；如下图；

（2） 窗口内原有语句为：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for trigger here
END
GO

需将其更改为：

CREATE TRIGGER TRIGGER_SC
ON SC FOR UPDATE AS IF UPDATE(SNO)
BEGIN
DECLARE @SNO_NEW CHAR(2),@SNO_OLD CHAR(2),@SNO_CNT INT
SELECT @SNO_OLD=SNO FROM deleted
SELECT @SNO_CNT=COUNT(*) FROM S WHERE SNO=@SNO_OLD
IF @SNO_CNT<>0
ROLLBACK TRANSACTION
END

（3） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；


（4） 查看数据库表S与SC，可发现在SC中有两条S9学号学生的记录如下两图；此时将SC中的第一条S9记录改为S1，发现修改后其数据再次恢复原有状态；如以下第三幅图；

12 用T-SQL为数据库表SC创建触发器

（1） 点击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
CREATE TRIGGER SCORE_SC_TRI
ON SC FOR INSERT,UPDATE
AS DECLARE @SCORE_READ TINYINT
SELECT @SCORE_READ=SCORE FROM inserted
IF @SCORE_READ >=0 AND @SCORE_READ<=100
BEGIN PRINT'操作完成！'
return
end
PRINT '成绩超出0-100之间，请重新输入。'
ROLLBACK TRANSACTION
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.SC”→“触发器”，可看到通过上述步骤生成的触发器已存在；如下图；

（4） 查看数据库表SC，如下图；

（5） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

INSERT INTO SC VALUES('S1','C5',190)
GO
INSERT INTO SC VALUES('S1','C5',100)
GO
UPDATE SC SET SCORE=130 WHERE SNO='S2'AND CNO='C5'
GO
UPDATE SC SET SCORE =60 WHERE SNO='S2' AND CNO='C5'
GO

（6） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（7） 可以在下方窗口中看到4条系统信息，表示：第1条INSERT语句因成绩为190超出范围，而要求重新输入；第2条INSERT语句因成绩为100在正常范围内，而插入表中;第3条UPDATE语句因成绩为130超出范围，而要求重新输入;第4条UPDATE语句因成绩为60在正常范围内，修改成功；查看数据库表SC的数据。在数据库表SC数据窗口中，单击感叹号按钮，更新表SC中的数据，如下图；可以看到增加了一个记录('S9”，“C5’，100)，修改了一条记录('S2'，C5'，60)，即是步骤中SQL语句执行的结果。

13 用T-SQL为数据库表C创建级联删除触发器

（1） 点击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
CREATE TRIGGER TRIGGER_DC
ON C FOR DELETE
AS DECLARE @CNO_DEL CHAR(2)
SELECT @CNO_DEL=CNO FROM deleted
DELETE FROM SC WHERE CNO=@CNO_DEL
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.C”→“触发器”，可看到通过上述步骤生成的触发器已存在；

（4） 打开数据库表C与SC，发现在SC表中有三条关于C1的记录，如下图；

（5） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
DELETE FROM C WHERE CNO='C1'
GO

（6） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（7） 在数据库表SC中发现课程C1有关的数据已经被删除；

14 交互式修改数据库表S的触发器

（1） 点击“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.S”→“触发器”，右击选择“修改”选项，如下图；

（2） 窗口内原有语句为：

USE [jxsk]
GO
/****** Object:  Trigger [dbo].[TRIGGER_S]    Script Date: 04/26/2019 16:25:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRIGGER_S]
ON [dbo].[S] FOR UPDATE AS IF UPDATE(SNO)
BEGIN
DECLARE @SNO_NEW CHAR(2),@SNO_OLD CHAR(2)
SELECT @SNO_NEW=SNO FROM inserted
SELECT @SNO_OLD=SNO FROM deleted
UPDATE SC SET SNO=@SNO_NEW WHERE SNO=@SNO_OLD
END

需将其更改为：

ALTER TRIGGER TRIGGER_S
ON S FOR DELETE
AS DECLARE @SNO_DEL CHAR(2)
SELECT @SNO_DEL=SNO FROM deleted
WHERE SNO=@SNO_DEL

（3） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（4） 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.S”→“触发器”，双击原有触发器，发现其已被修改；

15 用T-SQL修改数据库表C的触发器

（1） 点击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
ALTER TRIGGER TRIGGER_DC
ON C FOR DELETE
AS DECLARE @CNO_DEL CHAR(2)
SELECT @CNO_DEL=CNO FROM deleted
DELETE FROM SC WHERE CNO=@CNO_DEL
DELETE FROM TC WHERE CNO=@CNO_DEL
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.C”→“触发器”，双击原有触发器，发现其已被修改；

（4） 在数据库表C中删除任意一条记录，发现数据库表SC与TC中记录也随之改变；

16 交互式删除数据库表S的触发器

（1） 点击“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.S”→“触发器”，右击原有触发器，在弹出的菜单中选择“删除”，点击确定；

（2） 在原有位置已看不到原有触发器；

17 用T-SQL删除数据库表C的触发器

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
DROP TRIGGER TRIGGER_DC
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.C”→“触发器”，已看不到原有触发器，如下图；

相关推荐

最全的MySQL总结，助你向阿里“开炮”(面试题+笔记+思维图)

前言作为一名编程人员，对MySQL一定不会陌生，尤其是互联网行业，对MySQL的使用是比较多的。对于求职者来说，MySQL又是面试中一定会问到的重点，很多人拥有大厂梦，却因为MySQL败下阵来。实际上...

Redis数据库从入门到精通（redis数据库设计）

目录一、常见的非关系型数据库NOSQL分类二、了解Redis三、Redis的单节点安装教程四、Redis的常用命令1、Help帮助命令2、SET命令3、过期命令4、查找键命令5、操作键命令6、GET命...

netcore 急速接入第三方登录，不看后悔

新年新气象，趁着新年的喜庆，肝了十来天，终于发了第一版，希望大家喜欢。如果有不喜欢看文字的童鞋，可以直接看下面的地址体验一下：https://oauthlogin.net/前言此次带来得这个小项目是...

精选 30 个 C++ 面试题(含解析)（c++面试题和答案汇总）

大家好，我是柠檬哥，专注编程知识分享。欢迎关注@程序员柠檬橙，编程路上不迷路，私信发送以下关键字获取编程资源：发送1024打包下载10个G编程资源学习资料发送001获取阿里大神LeetCode...

Oracle 12c系列(一)|多租户容器数据库

作者杨禹航出品沃趣技术Oracle12.1发布至今已有多年，但国内Oracle12C的用户并不多，随着12.2在去年的发布，选择安装Oracle12c的客户量明显增加，在接下来的几年中，Or...

flutter系列之:UI layout简介（flutter-ui-nice）

简介对于一个前端框架来说，除了各个组件之外，最重要的就是将这些组件进行连接的布局了。布局的英文名叫做layout，就是用来描述如何将组件进行摆放的一个约束。在flutter中，基本上所有的对象都是wi...

Flutter 分页功能表格控件（flutter 列表）

老孟导读：前2天有读者问到是否有带分页功能的表格控件，今天分页功能的表格控件详细解析来来。PaginatedDataTablePaginatedDataTable是一个带分页功能的DataTable，...

Flutter | 使用BottomNavigationBar快速构建底部导航

平时我们在使用app时经常会看到底部导航栏,而在flutter中它的实现也较为简单.需要用到的组件:BottomNavigationBar导航栏的主体BottomNavigationBarI...

Android中的数据库和本地存储在Flutter中是怎样实现的

如何使用SharedPreferences？在Android中，你可以使用SharedPreferencesAPI来存储少量的键值对。在Flutter中，使用Shared_Pref...

Flet，一个Flutter应用的实用Python库!

▼Flet：用Python轻松构建跨平台应用！在纷繁复杂的Python框架中，Flet宛如一缕清风，为开发者带来极致的跨平台应用开发体验。它用最简单的Python代码，帮你实现移动端、桌面端...

flutter系列之:做一个图像滤镜（flutter photo）

简介很多时候，我们需要一些特效功能，比如给图片做个滤镜什么的，如果是h5页面，那么我们可以很容易的通过css滤镜来实现这个功能。那么如果在flutter中，如果要实现这样的滤镜功能应该怎么处理呢？一起...

flutter软件开发笔记20-flutter web开发

flutterweb开发优势比较多，采用统一的语言，就能开发不同类型的软件，在web开发中，特别是后台式软件中，相比传统的html5开发，更高效，有点像c++编程的方式，把web设计出来了。一...

Flutter实战-请求封装(五)之设置抓包Proxy

用了两年的flutter，有了一些心得，不虚头巴脑，只求实战有用，以供学习或使用flutter的小伙伴参考，学习尚浅，如有不正确的地方还望各路大神指正，以免误人子弟，在此拜谢~（原创不易，转发请标注来...

为什么不在 Flutter 中使用全局变量来管理状态

我相信没有人用全局变量来管理Flutter应用程序的状态。毫无疑问，我们的Flutter应用程序需要状态管理包或Flutter的基本小部件（例如InheritedWidget或St...

Flutter 攻略(Dart基本数据类型，变量 整理 2)

代码运行从main方法开始voidmain(){print("hellodart");}变量与常量var声明变量未初始化变量为nullvarc;//未初始化print(c)...

一周热门
最近发表
标签列表