SQL Server 利用觸發器對多表視圖進行更新的實現方法_MsSql

其步驟就是：利用update操作觸發器產生的2個虛擬表【inserted】用來儲存修改的資料資訊和【deleted】表，然後將對應的資料更新到對應資料表中的欄位資訊中；

1.首先建立3個表：

a.資訊表：

USE [SQL-LI]BEGIN TRANSACTION CHUANGJIAN_XINXIN_TAB --建立命名為【XINXIN_TAB】的資料表，同時不允許欄位為空白CREATE TABLE XINXIN_TAB(姓名 NVARCHAR(10) NOT NULL,性別 NVARCHAR(1) NOT NULL,學號 INT NOT NULL,班級 NVARCHAR(20) NOT NULL,出生日期 DATE NOT NULL,CONSTRAINT XUEHAO_YUESU PRIMARY KEY CLUSTERED([學號]ASC))COMMIT TRANSACTION CHUANGJIAN_XINXI_TABGO

b.明細分數表：

USE [SQL-LI] CREATE TABLE FENSHU_TAB([學號] INT NOT NULL,[語文] DECIMAL(3,1) NOT NULL,[數學] DECIMAL(3,1) NOT NULL,[英語] DECIMAL(3,1) NOT NULL)GO

c.綜合分數表：

USE [SQL-LI] CREATE TABLE ZHONGHE_TAB([姓名] NVARCHAR(10) NOT NULL,[學號] INT NOT NULL,[總分] DECIMAL(4,1) NOT NULL,[平均分] DECIMAL(3,1) NOT NULL)GO

2.1.【資訊表】和【明細分數表】插入對應表中的資料：

USE [SQL-LI] --插入【XINXIN_TAB】表中的5條記錄INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[學號] ,[性別] ,[班級] ,[出生日期] )VALUES('李曉峰',6080,'男','電腦','2013-05-03')INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[學號] ,[性別] ,[班級] ,[出生日期] )VALUES('李曉峰1',6081,'男','電腦1','2013-05-04')INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[學號] ,[性別] ,[班級] ,[出生日期] )VALUES('李曉峰2',6082,'男','電腦2','2013-05-05')INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[學號] ,[性別] ,[班級] ,[出生日期] )VALUES('李曉峰3',6083,'男','電腦3','2013-05-06')INSERT INTO [DBO].XINXIN_TAB ([姓名] ,[學號] ,[性別] ,[班級] ,[出生日期] )VALUES('張曉',6084,'女','美術','2013-05-07')--插入【FENSHU_TAB】表中的5條記錄INSERT INTO [DBO].FENSHU_TAB ([學號] ,[語文] ,[數學] ,[英語] )VALUES(6080,99.5,98.6,59.2)INSERT INTO [DBO].FENSHU_TAB ([學號] ,[語文] ,[數學] ,[英語] )VALUES(6081,93.5,94.3,55.8)INSERT INTO [DBO].FENSHU_TAB ([學號] ,[語文] ,[數學] ,[英語] )VALUES(6082,96.5,78.6,58.5)INSERT INTO [DBO].FENSHU_TAB ([學號] ,[語文] ,[數學] ,[英語] )VALUES(6083,99.5,68.4,89.2)INSERT INTO [DBO].FENSHU_TAB ([學號] ,[語文] ,[數學] ,[英語] )VALUES(6084,99.7,98.7,59.4)GO

【資訊表】的資料：

【明細分數表】的資料：

2.2.運算記錄【綜合分數表】的資料：

插入【ZHONGHE_TAB】中的資料USE [SQL-LI] --聲明3個變數分別用來接收【平均分】，【總分】，【姓名】，和一個控制迴圈的條件變數@I_WHILE_XUEHAODECLARE @I_WHILE_XUEHAO INT,@ZONGFEN DECIMAL(4,1),@AVGFEN DECIMAL(3,1),@XINGMING NVARCHAR(10);SELECT @I_WHILE_XUEHAO =6080;--使這個變數【@I_WHILE_XUEHAO】的值指定在【學號】欄位上WHILE(@I_WHILE_XUEHAO >=6080 AND @I_WHILE_XUEHAO <6085)BEGIN--求取【平均分】，【總分】，【姓名】並存在聲明的變數中SELECT @ZONGFEN =(F.語文 +F.數學 +F.英語 ),@AVGFEN =(F.語文 +F.數學 +F.英語 )/3,@XINGMING =X.姓名 FROM[DBO].XINXIN_TAB AS X INNER JOIN [DBO].FENSHU_TAB AS F ON X.學號 =F.學號 WHERE X.學號 =@I_WHILE_XUEHAO --與【學號同步】--將其變數的資料插入到【ZHONGHE_TAB】的對應欄位上INSERT INTO [DBO].ZHONGHE_TAB ([姓名] ,[學號] ,[平均分] ,[總分] )VALUES(@XINGMING ,@I_WHILE_XUEHAO ,@AVGFEN ,@ZONGFEN )SELECT @I_WHILE_XUEHAO +=1;  --與【學號同步】ENDGO

【綜合分數表】的資料：

3.1.1.建立3個表關聯的視圖：

USE [SQL-LI] GOCREATE VIEW SHITU_FFENSHU_XINXI(姓名,學號,平均分,總分,班級,出生日期)ASSELECT TOP 800 X.姓名 ,F.學號 ,Z.平均分 ,Z.總分 ,X.班級 ,X.出生日期 FROM[DBO].XINXIN_TAB AS X INNER JOIN [DBO].FENSHU_TAB AS F ON X.學號 =F.學號   INNER JOIN [DBO].ZHONGHE_TAB AS Z ON F.學號 =Z.學號   ORDER BY F.學號 ASC  GO

查看建立的視圖：

3.2.1.通過視圖修改多個資料表的資訊????：

UPDATE [SQL-LI].[dbo].[SHITU_FFENSHU_XINXI]SET [姓名] = 'aaaaa', --此欄位在【資訊表】中[平均分] =111  --此次欄位在【分數】中WHERE [學號]=6080GO

結果：

下面就寫個利用觸發器對其多表進行更新的方法：

a.這裡就利用instead of 代替觸發來代替對各表中的欄位內的資訊進行修改：

USE [SQL-LI] GOCREATE TRIGGER TRIGG_UPDATE --建立一個upda觸發器DML--關聯到[SHITU_FFENSHU_XINXI]視圖上ON[DBO].[SHITU_FFENSHU_XINXI]INSTEAD OF UPDATE --代替觸發器執行UPDATE功能；【但是只能定義一個增刪改的INSTEAD OF代替觸發】。AS--聲明接受變數用於儲存【inserted】表上的資料DECLARE @XINGMING NVARCHAR(10),@XUEHAO INT,@AVGFEN DECIMAL(3,1),@ZONGFEN DECIMAL(4,1),    @BANJI NVARCHAR(20),@CHUSHENGRIQI DATE;    --篩選【inserted】表中學號最小的一行資料SELECT @XUEHAO =MIN(學號) FROM[inserted] --遍曆【inserted】表WHILE(@XUEHAO IS NOT NULL)BEGIN--將【inserted】表中的資料存放到相應的變數中SELECT @XUEHAO =MIN([學號])FROM[inserted] WHERE [學號]=@XUEHAO SELECT @XINGMING=[姓名] FROM[inserted] WHERE[學號] =@XUEHAO SELECT @AVGFEN=[平均分]FROM[inserted] WHERE[學號] =@XUEHAO SELECT @ZONGFEN=[總分] FROM[inserted] WHERE[學號] =@XUEHAO SELECT @BANJI =[班級]FROM[inserted] WHERE[學號] =@XUEHAO SELECT @CHUSHENGRIQI=[出生日期]FROM[inserted] WHERE[學號] =@XUEHAO --找出視圖中的欄位對應相應表的欄位/*因為視圖中的[姓名]/[班級]/[出生日期]欄位是XINXIN_TAB 中的欄位，故修改【XINXIN_TAB】中對應的欄位資料之*/UPDATE[DBO].XINXIN_TAB SET [姓名]=@XINGMING ,[班級]=@BANJI ,[出生日期]=@CHUSHENGRIQI WHERE[學號]=@XUEHAO --道理同上UPDATE[DBO].FENSHU_TAB SET[學號]=@XUEHAO WHERE[學號]=@XUEHAO --道理同上UPDATE[DBO].ZHONGHE_TAB SET[平均分]=@AVGFEN ,[總分]=@ZONGFEN WHERE[學號]=@XUEHAO --修改完成後就開始篩選【inserted】表中下一條資料記錄SELECT @XUEHAO =MIN([學號])FROM[inserted] WHERE[學號]>@XUEHAO--然後給while中判斷 ENDGO

a1.注意的是視圖不是資料表沒有存放資料，將從【inserted】表中的資料提取後賦給對應資料表內的欄位中；

物件總管中的圖示：

 3.萬事俱備，開始通過視圖修改多表中的資料（驗證）：

a

USE [SQL-LI] --查看未修改前的視圖資料資訊SELECT* FROM[DBO].SHITU_FFENSHU_XINXI GOUPDATE[DBO].SHITU_FFENSHU_XINXI --修改【SHITU_FFENSHU_XINXI】中對應的欄位資料SET[姓名]='liyifeng' ,[平均分]=66.6 ,[總分]=88.8 ,[班級]='電腦SQLServer' ,[出生日期]='2013-05-05' --修改篩選WHERE[學號]=6080 GO--查看修改後的視圖資料資訊SELECT* FROM[DBO].SHITU_FFENSHU_XINXI GO

修改前後對比的結果圖示：

修改後的資料表中的資料：

USE [SQL-LI] SELECT* FROM[XINXIN_TAB] WHERE[學號]=6080SELECT* FROM[FENSHU_TAB] WHERE[學號]=6080SELECT* FROM[ZHONGHE_TAB] WHERE[學號]=6080GO

4.觸發器在資料庫裡面就像顆炸彈一樣，只要滿足氣要求就會被觸發，就會對資料庫裡面的資料進行觸發修改，所以不需要室就盡量將其關閉掉，用的時候就將其開啟：

關閉：

USE [SQL-LI] GODISABLE TRIGGER [DBO].TRIGG_UPDATE --關閉觸發器【TRIGG_UPDATE】ON SHITU_FFENSHU_XINXIGO

開啟：

USE [SQL-LI] GOENABLE TRIGGER [DBO].TRIGG_UPDATE --開啟觸發器【TRIGG_UPDATE】ON[DBO].[SHITU_FFENSHU_XINXI] --觸發器所在的視圖GO

GO

希望我寫的能為你解決一點問題，還望指教！謝謝！