开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server 利用触发器对多表视图进行更新

简介: 原文 http://www.cnblogs.com/liyifeng/archive/2013/05/05/3056968.html 其步骤就是:利用update操作触发器产生的2个虚拟表【inserted】用来存储修改的数据信息和【deleted】表,然后将对应的数据更新到对应数据表中的字段信息中; 1.
+关注继续查看

原文 http://www.cnblogs.com/liyifeng/archive/2013/05/05/3056968.html

其步骤就是:利用update操作触发器产生的2个虚拟表【inserted】用来存储修改的数据信息和【deleted】表,然后将对应的数据更新到对应数据表中的字段信息中;

1.首先创建3个表:

a.信息表:

复制代码
 1 USE [SQL-LI]
 2 BEGIN TRANSACTION CHUANGJIAN_XINXIN_TAB 
 3 --创建命名为【XINXIN_TAB】的数据表,同时不允许字段为空
 4 CREATE TABLE XINXIN_TAB
 5 (
 6 姓名 NVARCHAR(10) NOT NULL,
 7 性别 NVARCHAR(1) NOT NULL,
 8 学号 INT NOT NULL,
 9 班级 NVARCHAR(20) NOT NULL,
10 出生日期 DATE NOT NULL,
11 CONSTRAINT XUEHAO_YUESU PRIMARY KEY CLUSTERED
12 ([学号]ASC)
13 )
14 COMMIT TRANSACTION CHUANGJIAN_XINXI_TAB
15 GO
复制代码

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】中的数据
复制代码
 1 USE [SQL-LI] 
 2 --声明3个变量分别用来接收【平均分】,【总分】,【姓名】,和一个控制循环的条件变量@I_WHILE_XUEHAO
 3 DECLARE @I_WHILE_XUEHAO INT,@ZONGFEN DECIMAL(4,1),@AVGFEN DECIMAL(3,1),@XINGMING NVARCHAR(10);
 4 SELECT @I_WHILE_XUEHAO =6080;
 5 --使这个变量【@I_WHILE_XUEHAO】的值指定在【学号】字段上
 6 WHILE(@I_WHILE_XUEHAO >=6080 AND @I_WHILE_XUEHAO <6085)
 7 BEGIN
 8 --求取【平均分】,【总分】,【姓名】并存在声明的变量中
 9 SELECT @ZONGFEN =(F.语文 +F.数学 +F.英语 ),@AVGFEN =(F.语文 +F.数学 +F.英语 )/3,@XINGMING =X.姓名 
10 FROM[DBO].XINXIN_TAB AS X INNER JOIN [DBO].FENSHU_TAB AS F ON X.学号 =F.学号 
11 WHERE X.学号 =@I_WHILE_XUEHAO  --与【学号同步】
12 --将其变量的数据插入到【ZHONGHE_TAB】的对应字段上
13 INSERT INTO [DBO].ZHONGHE_TAB ([姓名] ,[学号] ,[平均分] ,[总分] )
14 VALUES(@XINGMING ,@I_WHILE_XUEHAO ,@AVGFEN ,@ZONGFEN )
15 SELECT @I_WHILE_XUEHAO +=1;   --与【学号同步】
16 END
17 GO
复制代码

 【综合分数表】的数据:

3.1.1.创建3个表关联的视图:

复制代码
 1 USE [SQL-LI] 
 2 GO
 3 CREATE VIEW SHITU_FFENSHU_XINXI(姓名,学号,平均分,总分,班级,出生日期)
 4 AS
 5 SELECT TOP 800 X.姓名 ,F.学号 ,Z.平均分 ,Z.总分 ,X.班级 ,X.出生日期 
 6 FROM[DBO].XINXIN_TAB AS X INNER JOIN [DBO].FENSHU_TAB AS F ON X.学号 =F.学号 
 7     INNER JOIN [DBO].ZHONGHE_TAB AS Z ON F.学号 =Z.学号 
 8     ORDER BY F.学号 ASC
 9     GO
10     
复制代码

查看创建的视图:

3.2.1.通过视图修改多个数据表的信息????:

1 UPDATE [SQL-LI].[dbo].[SHITU_FFENSHU_XINXI]
2    SET [姓名] = 'aaaaa', --字段在【信息表】中
3        [平均分] =111    --此次字段在【分数】中
4  WHERE [学号]=6080
5 GO

结果:

下面就写个利用触发器对其多表进行更新的方法:

a.这里就利用instead of 代替触发来代替对各表中的字段内的信息进行修改:

 

复制代码
USE [SQL-LI] 
GO
CREATE 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中判断 
END
GO
复制代码

a1.注意的是视图不是数据表没有存放数据,将从【inserted】表中的数据提取后赋给对应数据表内的字段中;

对象资源管理器中的图示:

 3.万事俱备,开始通过视图修改多表中的数据(验证):

a.

复制代码
USE [SQL-LI] 
--查看未修改前的视图数据信息
SELECT* FROM[DBO].SHITU_FFENSHU_XINXI 
GO

UPDATE[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[学号]=6080
SELECT* FROM[FENSHU_TAB]  WHERE[学号]=6080
SELECT* FROM[ZHONGHE_TAB] WHERE[学号]=6080
GO

 4.触发器在数据库里面就像颗炸弹一样,只要满足气要求就会被触发,就会对数据库里面的数据进行触发修改,所以不需要室就尽量将其关闭掉,用的时候就将其开启:

关闭:

USE [SQL-LI] 
GO
DISABLE TRIGGER [DBO].TRIGG_UPDATE --关闭触发器【TRIGG_UPDATE】
ON SHITU_FFENSHU_XINXI
GO

开启:

USE [SQL-LI] 
GO
ENABLE TRIGGER [DBO].TRIGG_UPDATE  --开启触发器【TRIGG_UPDATE】
ON[DBO].[SHITU_FFENSHU_XINXI] --触发器所在的视图
GO

其步骤就是利用update操作的触发器会触发出2个虚拟表,【inserted】用来存储修改的数据信息和【deleted】表,然后将对用数据赋给对应的数据表中的字段内;

GO

 

 

 

 

 

---恢复内容结束---

1.首先创建3个表:

a.信息表:

复制代码
 1 USE [SQL-LI]
 2 BEGIN TRANSACTION CHUANGJIAN_XINXIN_TAB 
 3 --创建命名为【XINXIN_TAB】的数据表,同时不允许字段为空
 4 CREATE TABLE XINXIN_TAB
 5 (
 6 姓名 NVARCHAR(10) NOT NULL,
 7 性别 NVARCHAR(1) NOT NULL,
 8 学号 INT NOT NULL,
 9 班级 NVARCHAR(20) NOT NULL,
10 出生日期 DATE NOT NULL,
11 CONSTRAINT XUEHAO_YUESU PRIMARY KEY CLUSTERED
12 ([学号]ASC)
13 )
14 COMMIT TRANSACTION CHUANGJIAN_XINXI_TAB
15 GO
复制代码

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】中的数据

 【综合分数表】的数据:

3.1.1.创建3个表关联的视图:

复制代码
 1 USE [SQL-LI] 
 2 GO
 3 CREATE VIEW SHITU_FFENSHU_XINXI(姓名,学号,平均分,总分,班级,出生日期)
 4 AS
 5 SELECT TOP 800 X.姓名 ,F.学号 ,Z.平均分 ,Z.总分 ,X.班级 ,X.出生日期 
 6 FROM[DBO].XINXIN_TAB AS X INNER JOIN [DBO].FENSHU_TAB AS F ON X.学号 =F.学号 
 7     INNER JOIN [DBO].ZHONGHE_TAB AS Z ON F.学号 =Z.学号 
 8     ORDER BY F.学号 ASC
 9     GO
10     
复制代码

查看创建的视图:

3.2.1.通过视图修改多个数据表的信息????:

1 UPDATE [SQL-LI].[dbo].[SHITU_FFENSHU_XINXI]
2    SET [姓名] = 'aaaaa', --字段在【信息表】中
3        [平均分] =111    --此次字段在【分数】中
4  WHERE [学号]=6080
5 GO

结果:

下面就写个利用触发器对其多表进行更新的方法:

a.这里就利用instead of 代替触发来代替对各表中的字段内的信息进行修改:

 

复制代码
USE [SQL-LI] 
GO
CREATE 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中判断 
END
GO
复制代码

a1.注意的是视图不是数据表没有存放数据,将从【inserted】表中的数据提取后赋给对应数据表内的字段中;

对象资源管理器中的图示:

 3.万事俱备,开始通过视图修改多表中的数据(验证):

a.

复制代码
USE [SQL-LI] 
--查看未修改前的视图数据信息
SELECT* FROM[DBO].SHITU_FFENSHU_XINXI 
GO

UPDATE[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[学号]=6080
SELECT* FROM[FENSHU_TAB]  WHERE[学号]=6080
SELECT* FROM[ZHONGHE_TAB] WHERE[学号]=6080
GO

 4.触发器在数据库里面就像颗炸弹一样,只要满足气要求就会被触发,就会对数据库里面的数据进行触发修改,所以不需要室就尽量将其关闭掉,用的时候就将其开启:

关闭:

USE [SQL-LI] 
GO
DISABLE TRIGGER [DBO].TRIGG_UPDATE --关闭触发器【TRIGG_UPDATE】
ON SHITU_FFENSHU_XINXI
GO

开启:

USE [SQL-LI] 
GO
ENABLE TRIGGER [DBO].TRIGG_UPDATE  --开启触发器【TRIGG_UPDATE】
ON[DBO].[SHITU_FFENSHU_XINXI] --触发器所在的视图
GO

 

GO

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server——SQL Server视图及索引技术
SQL Server——SQL Server视图及索引技术
19 0
SQL Server创建复合索引时,复合索引列顺序对查询的性能影响
原文:SQL Server创建复合索引时,复合索引列顺序对查询的性能影响    说说复合索引 写索引的博客太多了,一直不想动手写,有一下两个原因: 一是觉得有炒剩饭的嫌疑,有兄弟曾说:索引吗,只要在查询条件上建索引就行了,真的可以这么暴力吗? 二来觉得,索引是个非常大的话题,很难概括出所有的情况...
1177 0
在SQL Server中对视图进行增删改
原文:在SQL Server中对视图进行增删改 Lesktop开源IM发布以后,有一些网友问及如何在嵌入IM后与自己网站的用户系统整合(即如何让嵌入的IM直接使用网站原有的用户数据库,而不需要将已有的用户数据导入到IM的数据库中)。
984 0
SQLServer中使用索引视图(物化视图)
物化视图:以前用的普通的视图,普通视图就是一段逻辑语句,对性能没有任何的提升,也不能创建索引,而物化视图会把视图里查询出来的数据在数据库上建立快照,它和物理表一样,可以创建 索引,主键约束等等,性能会有质的提升,但是其有缺点,会占用,可以设置它定时自动更新一次,也可以手动更新,当然也是可以设置及时更新的,但是会拉慢基表的增删改查操作,在这里我只讲思路,具体的话大家可以自己去研究。
1147 0
<转>VisualSVN server导入已有仓库zz
超简单搭建功能全面易用的svn服务端 VisualSVN Server+ApachePasswd程序包安装前准备:VisualSVN-Server-1.6.3.msi:http://www.visualsvn.
1196 0
+关注
杰克.陈
一个安静的程序猿~
10425
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载