SQL Server實戰五：儲存過程與觸發器

疯狂学习GIS發表於2024-05-08
SQLServer儲存過程觸發器

  本文介紹基於Microsoft SQL Server軟體，實現資料庫儲存過程與觸發器的建立、執行、修改與刪除等操作。

目錄
  • 1 互動式建立並執行——儲存過程一
  • 2 互動式建立並執行——儲存過程二
  • 3 用T-SQL建立——儲存過程一
  • 4 用T-SQL建立——儲存過程二
  • 5 互動式修改儲存過程
  • 6 用T-SQL修改儲存過程
  • 7 互動式刪除儲存過程
  • 8 用T-SQL刪除儲存過程
  • 9 互動式為資料庫表S建立一級聯更新觸發器——建立觸發器
  • 10 互動式為資料庫表S建立一級聯更新觸發器——驗證觸發器
  • 11 互動式為資料庫表SC建立一限制更新觸發器——建立觸發器
  • 12 用T-SQL為資料庫表SC建立觸發器
  • 13 用T-SQL為資料庫表C建立級聯刪除觸發器
  • 14 互動式修改資料庫表S的觸發器
  • 15 用T-SQL修改資料庫表C的觸發器
  • 16 互動式刪除資料庫表S的觸發器
  • 17 用T-SQL刪除資料庫表C的觸發器

  系列文章中示例資料來源於《 SQL Server實驗指導（2005版）》一書。依據本系列文章的思想與對操作步驟、程式碼的詳細解釋，大家用自己手頭的資料，可以將相關操作與分析過程加以完整重現。

1 互動式建立並執行——儲存過程一

（1） 啟動Microsoft SQL Server 2008 R2軟體；

（2） 在“物件資源管理器”窗格中，在“資料庫”處右鍵，在彈出的選單中選擇“附加”選項；

（3） 選擇需要加以附加的jxsk資料庫物理檔案，選擇定位資料夾“G:\sql\chutianjia sql”並選擇對應資料庫jxsk的物理檔案並選擇“確定”按鈕，再次選擇“確定”即可；

（4） 在“物件資源管理器”中選擇“資料庫”→“jxsk”→“可程式設計性”→“儲存過程”；右擊，在彈出的視窗中選擇“新建儲存過程”選項；如下圖；

image

（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”→“觸發器”，已看不到原有觸發器，如下圖；

  至此，大功告成。

相關文章