SQL Server实战五:存储过程与触发器

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

系列文章中示例数据来源于《 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”→“触发器”,已看不到原有触发器,如下图;

至此,大功告成。

标签:游戏攻略